Last active
December 29, 2021 17:18
-
-
Save barrieroberts/61f85ec401878d61681496e710336f4a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 }); | |
} | |
} |
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
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