'Google Forms to PDF to Email
I am creating an online process for our employees to submit a leave of absence form. I have a google sheet collecting responses to a leave of absence google form. I want to take responses from the google sheet, autofill them into a temp doc based on a google doc template, make the autofilled form into a pdf and then automatically email that pdf to an email (the submitters supervisor- which is a question on the google form). I would like to then delete the temp autofilled doc and only keep the pdf version in my drive. The script I am using is not working though. For anything. My code is below.
The errors I have been getting is that e is undefined, 0 is undefined, 'Name' is undefined. I have not been able to create any pdfs but some of the form response are being filled into the temp google doc template. Can you please help me figure out how to make this script work?
function AfterFormSubmit(e) {
Logger.log("e: " + JSON.stringify(e));
const info = e.namedValues;
const pdfFile = createPDF(info);
const entryRow = e.range.getRow();
const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LOA Form Responses");
ws.getRange(entryRow, 25).setValue(pdfFile.getUrl());
sendEmail(e.namedValues['Supervisor Email'][0].pdfFile);
}
function sendEmail(email){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
const pdfFile = createPDF(info);
GmailApp.sendEmail(email, "New LOA Request", "Please review the LOA Request form attached and either approve or deny.",{
attachments: [pdfFile], name: "Harmonium LOA"})
}
function createPDF(info)
{
const pdfFolder = DriveApp.getFolderById("1sfGo81QTb012oCI0PUPUmfQaLRh3PZcG")
const tempFolder = DriveApp.getFolderById("1b5hjRebTofFZc907O8HZX8l4hrAlLC-G")
const templateDoc = DriveApp.getFileById("1iS6JKlPgHfQd18_NTBJEsW7A0OwSxpZW7SM9HQhpZkg")
const newTempFile = templateDoc.makeCopy(tempFolder);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
body.replaceText("{Name}", info['Name'][0]);
body.replaceText("{Timestamp}", info['Timestamp'][0]);
body.replaceText("{Site/Dept.}", info['Location'][0]);
body.replaceText("{Adress}", info['Home Address'][0]);
body.replaceText("{ID}", info['Employee ID'][0]);
body.replaceText("{Title}", info['Title'][0]);
body.replaceText("{Leave}", info['Leave Type'][0]);
body.replaceText("{PersonalReason}", info['Reason'][0]);
body.replaceText("{FMLA/CFRAReason}", info['Reason'][0]);
body.replaceText("{COVIDReason}", info['Reason'][0]);
body.replaceText("{Birth/Adoption/Placement}", info['Due date, adoption date or placement date of child.'][0]);
body.replaceText("{Start Date}", info['Start Date'][0]);
body.replaceText("{Return Date}", info['Anticipated Return Date'][0]);
body.replaceText("{Request Type}", info['New or Existing Request'][0]);
body.replaceText("{Hours}", info['Hours'][0]);
body.replaceText('{Cont / Inter}', info['Continuous or intermittent'][0]);
body.replaceText('{Inter Schedule}', info['Intermittent work schedule'][0]);
body.replaceText('{Birth/Adoption/Placement}', info['Due date, adoption date or placement date of child.'][0]);
body.replaceText('{Documentation}', info['Documentation'][0]);
body.replaceText('{Payment Plan}', info['Payback Plan'][0]);
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Name'][0] + " LOA-" + info['Start Date'][0]);
tempFolder.removeFile(newTempFile);
return pdfFile;
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|