Skip to content

Instantly share code, notes, and snippets.

@CraigRodrigues
Last active February 27, 2022 17:53
Show Gist options
  • Save CraigRodrigues/bac34e2b2a5330832011779a7c130f91 to your computer and use it in GitHub Desktop.
Save CraigRodrigues/bac34e2b2a5330832011779a7c130f91 to your computer and use it in GitHub Desktop.
Mindtickle Completion
const SHEET_NAME = 'COMPLETION REPORT';
function getAverage(colName) {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const col = data[0].indexOf(colName);
if (col != -1) {
const completions = sheet.getRange(2,col+1,sheet.getMaxRows()).getValues().map(x => x[0]).filter(x => x !== '');
const completionTotal = completions.reduce((a, b) => a + b);
return completionTotal/completions.length * 100;
}
}
function mindtickleComletion() {
const ui = SpreadsheetApp.getUi();
const input = ui.prompt("Please enter the cutoff percentage");
const thresholdPercentage = input.getResponseText() / 100;
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const headerRow = data[0];
const lastRow = sheet.getLastRow();
const nameIndex = headerRow.findIndex(x => x === 'Name');
const completionPercentIndex = headerRow.findIndex(x => x === '% Completion');
const completionAvg = getAverage('% Completion');
const ss = SpreadsheetApp.getActiveSpreadsheet();
let completionSheet = ss.getSheetByName(SHEET_NAME);
if (completionSheet) {
ss.deleteSheet(completionSheet);
}
ss.insertSheet(SHEET_NAME);
completionSheet = ss.getSheetByName(SHEET_NAME);
completionSheet.appendRow(['Advocate Name', 'Completion Percentage', 'Missing Modules']);
const outliers = [];
const moduleData = [];
for (let i = 1; i <= lastRow; i++) {
let missingModules;
if (data[i] && data[i][completionPercentIndex] < thresholdPercentage) {
let indexes = [];
data[i].forEach((item, idx) => {
if (idx > completionPercentIndex && item !== 1) indexes.push(idx);
});
missingModules = indexes.map(i => `${headerRow[i]}`).join('\n');
outliers.push(data[i][nameIndex]);
}
if (data[i]) {
moduleData.push([data[i][nameIndex], (data[i][completionPercentIndex] * 100), missingModules]);
}
}
completionSheet.getRange(2,1,moduleData.length, moduleData[0].length).setValues(moduleData);
completionSheet.setFrozenRows(1);
completionSheet
.sort(1, true)
.appendRow(['COMPLETION AVERAGE', completionAvg.toFixed(2)])
.appendRow(['OUTLIERS', outliers.join('\n')])
.autoResizeColumns(1, 2)
.setColumnWidth(3, 300); // auto resize counts the width including new lines which we don't want
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment