LinkedIn
Twitter
I'm trying to automate sending a custom PDF via email to respondents when an administrator (like myself) tick a checkbox in the response sheet of the Google Form (as shown below).
Unfortunately, I'm encountering an error:
TypeError: Cannot read properties of undefined (reading 'Email Address')createPDF@ Code.gs:52
Can anyone help me review my code to fix this error? Specifically, could you guide me on defining the Email Address in the createPDF function?
function onEdit(e) {
const sheet = e.source.getActiveSheet();
const checkboxColumn = 18; // Adjust if needed
// Check if the edited sheet is "Form Responses 1" and the edited column is the checkbox column
if (sheet.getName() === "Form Responses 1" && e.range.getColumn() === checkboxColumn) {
// If the checkbox is checked, proceed with creating and sending the PDF
if (e.range.isChecked()) {
const entryRow = e.range.getRow();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
// Extract data from the edited row
// ... (rest of your code to build the info object and create the PDF)
const info = {
'Timestamp': [rowData[1]],
'Email Address': [rowData[2]],
'What Happened ?': [rowData[3]],
'Why is it a Problem ?': [rowData[4]],
'Who Detected / Who is Affected ?': [rowData[5]],
'Where is the Problem ?': [rowData[6]],
'When Detected ?': [rowData[7]],
'How was it Detected': [rowData[8]],
'How Many ?': [rowData[9]],
'Part Number ': [rowData[10]],
'Type of the component ': [rowData[11]],
'Camera Stage': [rowData[12]],
'Quantity to be blocked by PN': [rowData[13]],
'Proposed disposition plan': [rowData[14]],
'Due Date ': [rowData[15]],
'Comment': [rowData[16]]
};
const pdfFile = createPDF(info);
sheet.getRange(entryRow, 20).setValue(pdfFile.getUrl());
sendEmail(info["Email Address"][0], pdfFile);
}
}
}
function createPDF(info) {
const pdfFolder = DriveApp.getFolderById("1ejP3Dsd4y4TiIjtsJ3fHf5g_cmjvHDbo");
const tempFolder = DriveApp.getFolderById("1cvIus2hbg25W3SgqeLP30Fc4661k_HR6");
const templateDoc = DriveApp.getFileById("1sfZGb1jo0amoOFB1p_P-1UNyu_gKUDb8ReUnFNeQkBM");
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
// Extract first and last name from email address
const email = info['Email Address'][0] || "";
const names = email.split(".");
const firstName = names[0];
const lastName = names.length > 1 ? names[1] : "";
body.replaceText("{Date of Request}", info['Timestamp'][0] || "");
body.replaceText("{Requestor}", info['Email Address'][0] || "");
body.replaceText("{What}", info['What Happened ?'][0] || "");
body.replaceText("{Why}", info['Why is it a Problem ?'][0] || "");
body.replaceText("{Who}", info['Who Detected / Who is Affected ?'][0] || "");
body.replaceText("{Where}", info['Where is the Problem ?'][0] || "");
body.replaceText("{When}", info['When Detected ?'][0] || "");
body.replaceText("{How}", info['How was it Detected'][0] || "");
body.replaceText("{Qty}", info['How Many ?'][0] || "");
body.replaceText("{PN}", info['Part Number '][0] || "");
body.replaceText("{Type}", info['Type of the component '][0] || "");
body.replaceText("{Stage}", info['Camera Stage'][0] || "");
body.replaceText("{Qty B}", info['Quantity to be blocked by PN'][0] || "");
body.replaceText("{Plan}", info['Proposed disposition plan'][0] || "");
body.replaceText("{Due}", info['Due Date'][0] || "");
body.replaceText("{Comment}", info['Comment'][0] || "");
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName(firstName + " " + lastName + " " + info['Part Number or Serial Number'][0]);
//tempFolder.removeFile(newTempFile);
newTempFile.setTrashed(true);
return pdfFile;
}
function sendEmail(email, pdfFile) {
const subjectUser = "Here's your QC930 Quarantine Entry Form";
const bodyUser = "Hello,\n\nThank you for submitting a quarantine entry form request. \n\nPlease print and attach this form to all quarantined parts. \n\nMany thanks for your cooperation. \n\nKind regards, \nQuality Team";
GmailApp.sendEmail(email, subjectUser, bodyUser, {
attachments: [pdfFile],
name: 'Quality Team'
});
}
