Make Student Reports

Docs-6

//SCRIPT 1
function makeReports() {
  //Get data
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = ss.getActiveSheet(),
    data = sheet.getDataRange().getValues(),
    company = data[0][0];
  data.splice(0, 2);

  //Get report template and report folder IDs
  const reportTemplate = DriveApp.getFileById('1JkDxw8r_aMBKDgwnUI2OdWDwg87ljGCIgjrKMHxQErc'),
    reportFolder = DriveApp.getFolderById('1wjCCwa3Rq_YN-fLEBG8xJMmupZhf-08u');

  //Filter rows by reports with "Y" in Make column
  var reportRows = data.map(function (rr) {
    return rr;
  })
    .filter(function (rr) {
      return rr[23] === "Y";
    });

  var numOfReports = reportRows.length;
  var reportMade = false;

  //Get the row locations of each of the students
  reportRows.forEach(function (rRow, num) {
    var studentName = rRow[4];
    var indexNo = data.map(function (row) {
      return row[4];
    })
      .indexOf(studentName);

    //Check to see if any cells up to column Q are empty,
    //if so, add msg on sheet, if not, make report    
    if (rRow.indexOf("") > -1 && rRow.indexOf("") < 17) {
      sheet.getRange(indexNo + 3, 23).setValue("Cell missing info");
    }
    else {
      var reportData = setUpData(rRow);
      var report = createReport(company, reportTemplate,
        reportFolder, rRow, reportData);
      reportMade = addToSheet(report, num, ss, sheet,
        numOfReports, indexNo);
    }
  });
  //End of script message to user
  if (reportMade === true) {
    ss.toast("Reports made.");
  } else {
    ss.toast("No reports made.")
  }
}

//SCRIPT 2
function setUpData(rRow) {
  const GROUP = 0, LANG = 1, CLASSLEVEL = 2, TEACHER = 3,
    STUDENT = 4, PERIOD = 6, SPEAKING = 7, LISTENING = 8,
    WRITING = 9, READING = 10, GRAMMAR = 11, VOCAB = 12,
    EXAM = 13, ATTENDANCE = 14, STUDENTLEVEL = 15, LP = 16;

  //Add group number - deal with 0
  var group = rRow[GROUP];
  if (!isNaN(group)) {
    var group = (group > 9) ? group : "0" + group;
  }

  var studentName = rRow[STUDENT];
  var period = rRow[PERIOD];
  var notEvaluated = "Not evaluated";

  //Get the column numbers & cell values for the marks
  if (isNaN(rRow[SPEAKING])) {
    var speaking = 1;
    var textS = notEvaluated;
  }
  else {
    var speaking = Math.ceil(rRow[SPEAKING] / 2);
    var textS = "X";
  }

  if (isNaN(rRow[LISTENING])) {
    var listening = 1;
    var textL = notEvaluated;
  } else {
    var listening = Math.ceil(rRow[LISTENING] / 2);
    var textL = "X";
  }

  if (isNaN(rRow[WRITING])) {
    var writing = 1;
    var textW = notEvaluated;
  } else {
    var writing = Math.ceil(rRow[WRITING] / 2);
    var textW = "X";
  }

  if (isNaN(rRow[READING])) {
    var reading = 1;
    var textR = notEvaluated;
  } else {
    var reading = Math.ceil(rRow[READING] / 2);
    var textR = "X";
  }

  if (isNaN(rRow[GRAMMAR])) {
    var grammar = 1;
    var textG = notEvaluated;
  } else {
    var grammar = Math.ceil(rRow[GRAMMAR] / 2);
    var textG = "X";
  }

  if (isNaN(rRow[VOCAB])) {
    var vocab = 1;
    var textV = notEvaluated;
  } else {
    var vocab = Math.ceil(rRow[VOCAB] / 2);
    var textV = "X";
  }

  //Get the level progress, student level & exam mark
  var levelProgress = rRow[LP];
  var studentLevel = rRow[STUDENTLEVEL];
  var exam = rRow[EXAM];

  if (!isNaN(exam)) {
    exam = exam + " / 10";
  }

  //Get the attendance %
  var attendance = rRow[ATTENDANCE] * 100;
  var attendance = attendance.toFixed(0);

  //Store the above variables in an array
  var reportData = [group, studentName, period,
    speaking, textS, listening, textL, writing,
    textW, reading, textR, grammar, textG,
    vocab, textV, levelProgress, studentLevel,
    exam, attendance, STUDENT, TEACHER, LANG,
    notEvaluated];
  return reportData;
}

//SCRIPT 3
function createReport(company, reportTemplate, reportFolder, rRow, reportData) {
  //Copy template document & name document, get the doc body
  var group = reportData[0], studentName = reportData[1];
  var newReport = reportTemplate.makeCopy(company +
    "-Report-" + group + "-" + studentName, reportFolder),
    newReportId = newReport.getId(),
    report = DocumentApp.openById(newReportId),
    body = report.getBody(),
    bgColor1 = "#FFB74D",
    bgColor2 = '#eba845',
    bgColor3 = '#fce5cd';

  //Fill in tables  
  var tables = body.getTables();
  table0(tables, rRow, company, reportData);
  tables2to7(tables, reportData, bgColor1, bgColor2, bgColor3);
  table8(tables, reportData, bgColor1, bgColor2);
  table9(tables, reportData);

  //save edits and update report made status
  report.saveAndClose();
  return report;
}

//Fill in student and group details
function table0(tables, rRow, company, reportData) {
  var table0 = tables[0];
  var STUDENT = reportData[19],
    period = reportData[2],
    TEACHER = reportData[20],
    LANG = reportData[21],
    group = reportData[0];

  table0.getRow(0).getCell(1).setText(rRow[STUDENT]);
  table0.getRow(1).getCell(1).setText(company);
  table0.getRow(2).getCell(1).setText(period);
  table0.getRow(0).getCell(3).setText(rRow[TEACHER]);
  table0.getRow(1).getCell(3).setText(rRow[LANG]);
  table0.getRow(2).getCell(3).setText(group);
}

//Fill in feedback and colour cells
function tables2to7(tables, reportData, bgColor1, bgColor2, bgColor3) {
  var table2 = tables[2];
  var table3 = tables[3];
  var table4 = tables[4];
  var table5 = tables[5];
  var table6 = tables[6];
  var table7 = tables[7];

  var studentName = reportData[1], period = reportData[2],
    speaking = reportData[3], textS = reportData[4],
    listening = reportData[5], textL = reportData[6],
    writing = reportData[7], textW = reportData[8],
    reading = reportData[9], textR = reportData[10],
    grammar = reportData[11], textG = reportData[12],
    vocab = reportData[13], textV = reportData[14],
    noEval = reportData[22];

  //Adds either "Not evaluated" or X to appropriate column
  if (textS === noEval) {
    table2.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (sp = 1; sp < speaking; sp++) {
      table2.getRow(0).getCell(sp)
        .setBackgroundColor(bgColor1);
    }
    table2.getRow(0).getCell(speaking)
      .setBackgroundColor(bgColor2).setText(textS);
  }

  if (textL === noEval) {
    table3.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (li = 1; li < listening; li++) {
      table3.getRow(0).getCell(li)
        .setBackgroundColor(bgColor1);
    }
    table3.getRow(0).getCell(listening)
      .setBackgroundColor(bgColor2).setText(textL);
  }

  if (textW === noEval) {
    table4.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (wr = 1; wr < writing; wr++) {
      table4.getRow(0).getCell(wr)
        .setBackgroundColor(bgColor1);
    }
    table4.getRow(0).getCell(writing)
      .setBackgroundColor(bgColor2).setText(textW);
  }

  if (textR === noEval) {
    table5.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (re = 1; re < reading; re++) {
      table5.getRow(0).getCell(re)
        .setBackgroundColor(bgColor1);
    }
    table5.getRow(0).getCell(reading)
      .setBackgroundColor(bgColor2).setText(textR);
  }

  if (textG === noEval) {
    table6.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (gr = 1; gr < grammar; gr++) {
      table6.getRow(0).getCell(gr)
        .setBackgroundColor(bgColor1);
    }
    table6.getRow(0).getCell(grammar)
      .setBackgroundColor(bgColor2).setText(textG);
  }

  if (textV === noEval) {
    table7.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(noEval);
  }
  else {
    for (vo = 1; vo < vocab; vo++) {
      table7.getRow(0).getCell(vo)
        .setBackgroundColor(bgColor1);
    }
    table7.getRow(0).getCell(vocab)
      .setBackgroundColor(bgColor2).setText(textV);
  }
}

//Fill in level and level progress
function table8(tables, reportData, bgColor1) {
  var table8 = tables[8];
  var studentLevel = reportData[16],
    levelProgress = reportData[15];

  table8.getRow(0).getCell(1).setText(studentLevel);
  for (i = 1; i <= levelProgress; i++) {
    table8.getRow(1).getCell(i)
      .setText(">")
      .setBackgroundColor(bgColor1);
  }
}

//Fill in exam & attendance info
function table9(tables, reportData) {
  var table9 = tables[9];
  var exam = reportData[17],
    attendance = reportData[18];

  table9.getRow(0).getCell(1).setText(exam);
  table9.getRow(0).getCell(3).setText(attendance + "%");
}

//SCRIPT 4
function addToSheet(openReport, num, ss, sheet,
  numOfReports, indexNo) {
  //Add Google Doc URL to Sheet
  var reportUrl = openReport.getUrl();
  var linkCell = sheet.getRange(indexNo + 3, 23);
  linkCell.setFormula('=HYPERLINK("'
    + reportUrl
    + '";"REPORT")');
  sheet.getRange(indexNo + 3, 24).setValue("Made");

  //Progress message to user & update reportMade status
  var reportNumber = Number(num) + 1;
  ss.toast("Report " + reportNumber + " out of "
    + numOfReports + " made.");
  var reportMade = true;
  return reportMade;
}

//SCRIPT 5
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('REPORTS')
    .addItem('Make reports', 'makeReports')
    .addToUi();
}