Make & send student reports

Docs-7

//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('DOCUMENT ID'),
    reportFolder = DriveApp.getFolderById('FOLDER ID');

  //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;

  //Check if all the cells have been completed
  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("") < 16) {
      sheet.getRange(indexNo + 3, 23).setValue("Cell missing info");
    }
    else {
      var reportData = setUpData(rRow);
      var openReport = createReport(company, reportTemplate, reportFolder, rRow, reportData);
      reportMade = addToSheet(openReport, 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";

  //Convert feedback to column number & if an X add 1 & add no evaluado
  if (isNaN(rRow[SPEAKING])) {
    var speaking = 1;
    var textS = notEvaluated;
  }
  else {
    var speaking = rRow[SPEAKING] / 2;
    var textS = "X";
  }

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

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

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

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

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

  var levelProgress = rRow[LP];
  var studentLevel = rRow[STUDENTLEVEL];
  var exam = rRow[EXAM];

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

  var attendance = rRow[ATTENDANCE] * 100;
  var attendance = attendance.toFixed(0);
  var reportData = [group, studentName, period, speaking, textS,
    listening, textL, writing, textW, reading,
    textR, grammar, textG, vocab, textV,
    levelProgress, studentLevel, exam, attendance,
    STUDENT, TEACHER, LANG];
  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(),
    openReport = DocumentApp.openById(newReportId),
    body = openReport.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
  openReport.saveAndClose();
  return openReport;
}

//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];

  var notEval = "Not evaluated";

  //Adds either "No evaluado" or X to appropriate column
  if (textS === notEval) {
    table2.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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 === notEval) {
    table3.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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 === notEval) {
    table4.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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 === notEval) {
    table5.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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 === notEval) {
    table6.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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 === notEval) {
    table7.getRow(0).getCell(1)
      .setBackgroundColor(bgColor3).setText(notEval);
  }
  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(">");
    table8.getRow(1).getCell(i).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 sendReportsLINK() {
  var emailType = 'LINK';
  sendReports(emailType);
}

function sendReportsPDF() {
  var emailType = 'PDF';
  sendReports(emailType);
}

function sendReports(emailType) {
  //Get data
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = ss.getActiveSheet(),
    data = sheet.getDataRange().getValues(),
    company = data[0][0];

  //Get column numbers
  const STUDENT = 4, EMAIL = 5, LINK = 22, SEND = 24;
  var reportSent = false;

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

  //Filter link formulas by reports with "Y" in Send column
  var dataFormulas = sheet.getDataRange().getFormulas();
  var linkFormulas = dataFormulas.map(function (lf, r) {
    return lf[LINK];
  })
    .filter(function (lf, r) {
      return data[r][SEND] === "Y";
    });

  //Get file IDs from HYPERLINK formulas
  var linkIds = linkFormulas.map(function (li) {
    var linkId = li.replace('=HYPERLINK("https://docs.google.com/a/bazroberts.com/open?id=', "");
    var linkId = linkId.replace('","REPORT")', "");
    return linkId;
  });

  //Loop through reports links
  linkIds.forEach(function (linkId, l) {
    var sEmail = reportRows[l][EMAIL];
    //Check if there is an email
    if (sEmail !== "") {
      var doc = DriveApp.getFileById(linkId);
      var studentName = reportRows[l][STUDENT];

      //Send report either as a PDF or via a link
      if (emailType === "PDF") {
        sendEmailPDF(sEmail, studentName, doc);
      }
      else if (emailType === "LINK") {
        sendEmailLINK(sEmail, studentName, doc, linkId);
      }

      //Get student names and find position of student
      reportRows.forEach(function (rRow, num) {
        var studentName = rRow[4];
        var indexNo = data.map(function (row) {
          return row[4];
        })
          .indexOf(studentName);

        //Update sheet with "Sent"
        sheet.getRange(indexNo + 1, SEND + 1).setValue("Sent");
      });
      reportSent = true;
    }

    //If email is missing change cell to red
    else if (sEmail === "") {
      var indexNo = data.map(function (row) {
        return row[4];
      })
        .indexOf(studentName);
      sheet.getRange(indexNo + 1, 6).setBackground("red");
    }
  });

  //End of script msg to user
  if (reportSent === true) {
    ss.toast("Reports sent.");
  } else {
    ss.toast("No reports sent.")
  }
}

//SCRIPT 6a
function sendEmailLINK(sEmail, studentName, doc, linkId) {
  var emailBody = HtmlService.createHtmlOutputFromFile('7-emailLINK')
    .getContent();

  //Change doc permission to anyone with link
  doc.setSharing(DriveApp.Access.ANYONE_WITH_LINK,
    DriveApp.Permission.EDIT)

  //Create URL for the link & add to email
  var linkUrl = "https://docs.google.com/document/d/"
    + linkId + "/export?format=pdf";
  emailBody = emailBody.replace('#LINK', linkUrl);

  MailApp.sendEmail(sEmail, "REPORT: " + studentName, '',
    {
      htmlBody: emailBody,
      replyTo: 'baz@bazroberts.com'
    });
}

//SCRIPT 6b
function sendEmailPDF(sEmail, studentName, doc) {
  var emailBody = HtmlService.createHtmlOutputFromFile('7-emailPDF')
    .getContent();

  //Get G Doc and make a PDF then attach     
  var pdf = doc.getAs('application/pdf').getBytes();
  var attach = {
    fileName: 'Report.pdf', content: pdf,
    mimeType: 'application/pdf'
  };

  MailApp.sendEmail(sEmail, "REPORT: " + studentName, '',
    {
      htmlBody: emailBody,
      replyTo: 'baz@bazroberts.com',
      attachments: [attach]
    });
}

//HTML 7-Link
<!DOCTYPE html>
<html>

<head>
	<base target="_top">
	<style>
		p {
			font-family: verdana;
			font-size: 1.2em;
		}

		#image {
			float: left;
			width: 50;
			padding-left: 10px;
		}

		#title {
			height: 40px;
			padding-top: 10px;
			border-left-style: outset;
			border-left-color: red;
			border-left-width: 10px;
		}

		.pad {
			padding-left: 10px;
		}
	</style>
</head>

<body>
	<div class="pad">
		<img id="image" src="https://docs.google.com/uc?id=1Z2LuCSK-B-WIkIgOJQW4UIaUCea6UG3D"
                     height='50' width=50>
		<h2 id="title">REPORT</h2>
		<p>Please find the <a href=#LINK>link</a> to your report from your teacher.
			Thank you for taking classes with us.</p>
		<p>Best regards,</p>
		<p>Barrie Roberts - Director of Studies</p>
		<hr>
	</div>
</body>

</html>

//HTML 7-PDF
<!DOCTYPE html>
<html>

<head>
	<base target="_top">
	<style>
		p {
			font-family: verdana;
			font-size: 1.2em;
		}

		#image {
			float: left;
			width: 50;
			padding-left: 10px;
		}

		#title {
			height: 40px;
			padding-top: 10px;
			border-left-style: outset;
			border-left-color: red;
			border-left-width: 10px;
		}

		.pad {
			padding-left: 10px;
		}
	</style>
</head>

<body>
	<div class="pad">
		<img id="image" src="https://docs.google.com/uc?id=1Z2LuCSK-B-WIkIgOJQW4UIaUCea6UG3D"
                     height='50' width=50>
		<h2 id="title">REPORT</h2>
		<p>Attached is your report from your teacher.
			Thank you for taking classes with us.</p>
		<p>Best regards,</p>
		<p>Barrie Roberts - Director of Studies</p>
		<hr>
	</div>
</body>

</html>

//SCRIPT 8
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('REPORTS')
    .addItem('Make reports', 'makeReports')
    .addSeparator()
    .addItem('Send reports as PDF attachment', 'sendReportsPDF')
    .addSeparator()
    .addItem('Send reports as link', 'sendReportsLINK')
    .addToUi();
}