Skip to content

Instantly share code, notes, and snippets.

@barrieroberts
Last active December 29, 2021 17:18
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save barrieroberts/61f85ec401878d61681496e710336f4a to your computer and use it in GitHub Desktop.
Save barrieroberts/61f85ec401878d61681496e710336f4a to your computer and use it in GitHub Desktop.
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Reports')
.addItem('Make pdfs', 'createReports')
.addItem('Email reports', 'sendEmails')
.addToUi();
}
function createReports() {
//Get data from sheet and remove header
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sh = ss.getActiveSheet();
const ssData = sh.getDataRange().getValues();
ssData.shift();
let rowNo = 2;
//Get the report folder and report template URLs
const shOptions = ss.getSheetByName('Options');
const reportFolderUrl = shOptions.getRange(10, 1).getValue();
const reportFolderKey = reportFolderUrl.replace("https://drive.google.com/drive/folders/", "");
const reportFolder = DriveApp.getFolderById(reportFolderKey);
const reportTemplateUrl = shOptions.getRange(12, 1).getValue();
const reportTemplateKey = reportTemplateUrl.match(/[-\w]{25,}/);
//Loop down rows
for (const row of ssData) {
//Get data from sheet
let [studentName, , group, teacher, speaking, listening, pronunciation, writing, grammar, vocabulary, attention, behaves, participates, arrives, missed, classesInTerm, written, oral, recommendation] = row;
let reportTemplate = DriveApp.getFileById(reportTemplateKey);
let newReport = reportTemplate.makeCopy(`Report-${group}-${studentName}`);
let newReportId = newReport.getId();
//Open new Doc and get its body
let openReport = DocumentApp.openById(newReportId);
let body = openReport.getBody();
//Fill report details
let table1 = body.getTables()[0];
table1.getRow(0).getCell(1).setText(studentName);
table1.getRow(1).getCell(1).setText(group);
table1.getRow(2).getCell(1).setText(teacher);
let table2 = body.getTables()[1];
table2.getRow(0).getCell(1).setText(speaking);
table2.getRow(1).getCell(1).setText(listening);
table2.getRow(2).getCell(1).setText(pronunciation);
table2.getRow(0).getCell(3).setText(writing);
table2.getRow(1).getCell(3).setText(grammar);
table2.getRow(2).getCell(3).setText(vocabulary);
let table3 = body.getTables()[2];
table3.getRow(0).getCell(1).setText(attention);
table3.getRow(1).getCell(1).setText(behaves);
table3.getRow(0).getCell(3).setText(participates);
table3.getRow(1).getCell(3).setText(arrives);
let table4 = body.getTables()[3];
table4.getRow(0).getCell(1).setText(missed);
table4.getRow(0).getCell(3).setText(classesInTerm);
let table5 = body.getTables()[4];
table5.getRow(0).getCell(1).setText(written.toFixed(1) + " /10");
table5.getRow(0).getCell(3).setText(oral.toFixed(1) + " /10");
table5.getRow(1).getCell(1).setText(recommendation);
//save edits
openReport.saveAndClose();
//Save current Doc as pdf
let pdf = DriveApp.getFileById(newReportId).getAs('application/pdf');
pdf.setName(openReport.getName() + ".pdf");
let reportPdf = DriveApp.createFile(pdf);
let newPDFId = reportPdf.getId();
//Move pdf in report folder, delete original Doc
reportPdf.moveTo(reportFolder);
DriveApp.getFileById(newReportId).setTrashed(true);
//Add PDF URL to Sheet using PDF ID
let cell = sh.getRange(rowNo, 20);
cell.setFormula('=HYPERLINK("https://drive.google.com/file/d/' + newPDFId + '")');
rowNo++;
}
ss.toast("Reports made.", "Finished");
}
function sendEmails() {
const sh = SpreadsheetApp.getActiveSheet();
const ssData = sh.getDataRange().getValues();
ssData.shift();
//Get school logo from Drive
const image = DriveApp.getFileById("0B3zSwpJCAxswMEpablpOZGt5ZXc").getBlob();
//Loop down the rows of students
for (const row of ssData) {
//Get data on row
let name = row[0], emailTo = row[1], pdfLink = row[19];
//Set up message
let message = '<img src="cid:logo" height="40" width="60"/>';
message += "<p>Dear parents,</p>";
message += "<p>Please find the end of year report for " + name + ".</p>";
message += "<p>Please click on the link below to open the PDF.</p>";
message += '<p><a href='+pdfLink+'>REPORT</a></p>';
message += "<p>Best regards,</p>";
message += "<p>Barrie</p>";
//Send email
MailApp.sendEmail(
emailTo, "Report-"+name, "",
{ inlineImages:{ logo:image },
htmlBody:message });
}
}
@JonHollandWales
Copy link

JonHollandWales commented Aug 10, 2017

Hi Barrie
on the 46th line of the script
var reportTemplate = DriveApp.getFileById(reportTemplateKey);
i'm getting this error message when i try to save

Syntax error. (line 65, file "send pdf")Dismiss

@barrieroberts
Copy link
Author

Line 46 relies on a reportTemplateUrl on the options sheet (line 20).
I can't see why there would be an error on line 65 though.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment