Mindtickle Completion

mindtickle.js

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
}