This script is intended to create a customize PDF file based on the Google Form responses. This worked a few days back and suddenly it stopped executing.
The variable/property in question (“info“) seems to be present but it is still returning an error of being undefined
For reference, this is the script:
function afterFormSubmit(e) {
const info = e.namedValues;
const pdfFile = createPDF(info);
const entryRow = e.range.getRow();
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PDFs").getRange(entryRow,19).setValue(pdfFile.getUrl());
sendEmail(e.namedValues['Email Address'][0],pdfFile);
}
function sendEmail(email,pdfFile) {
GmailApp.sendEmail(email, "Here's your Material Requisition Form", "Hello,nnThank you for submitting a material requisition. nnAs this is a self declaration form there is no further approval required. nnPlease print and attach this form to all requested materials. nnMany thanks for your cooperation. nnKind regards,nLogistics Team", {
attachments: [pdfFile],
name: 'Logistics Team'
});
GmailApp.sendEmail("[email protected]", "Material Requisition Form for SAP Transaction", "Hello,nnKindly transact the following material requisiton form in SAP. nnMany thanks for your cooperation. nnKind regards,nLogistics Team", {
attachments: [pdfFile],
name: 'Logistics Team'
});
}
This is the variable/property is based from the Google Form responses (Google Sheets)
This is the error:
TypeError: Cannot read properties of undefined (reading ‘Date of Request’)
createPDF @ Code.gs:35
“info” object was checked to have “Date of Request” (as shown in the heading of the form responses in Google Sheet)
Editor’s note:
Below there is the createPDF function taken from the revision 1 of this question
function createPDF(info) {
const pdfFolder = DriveApp.getFolderById("1GiY42M3rTq36M9pnUq7Y6RE1qQ8TFN8X");
const tempFolder = DriveApp.getFolderById("1bkzpVTURICEvcJWemFGaOYTZSdeF_XNj");
const templateDoc = DriveApp.getFileById("1UfFzcIdG1DCAYpk-NWSiA7bEoz1ZSJXeuV_9-Okw_vg");
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
body.replaceText("{Date of Request}",info['Date of Request'][0]);
body.replaceText("{Requestor}",info['Requestor'][0]);
body.replaceTest("{Line}",info['Line'][0]);
body.replaceText("{Cost Centre}", info['Cost Centre (if known)'][0]);
body.replaceText("{Project Name}", info['Project Name'][0]);
body.replaceText("{Project Code}", info['Project Code'][0]);
body.replaceText("{PN}", info['Part Number or Serial Number'][0]);
body.replaceText("{Reason for Requirement}", info['Reason for Requirement'][0]);
body.replaceText("{Quantity}", info['Quantity'][0]);
body.replaceText("{Description}", info['Description'][0]);
body.replaceText("{Have}", info['Have these products been deemed scrap by the Linelead/Area owner?'][0]);
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName(info['Requestor'][0] + " " + info['Date of Request'][0]);
tempFolder.removeFile(newTempFile);
return pdfFile;
}
Michelle P is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
8
Directly Accessing Data using getValues()
A possible reason why your code fails is because it expects the info parameter to be structured as an object with values wrapped in arrays. If the input to info does not match this expected structure, trying to access properties like may return undefined, leading to a TypeError when attempting to read [0] from it.
To avoid issues with undefined values, I recommend creating a replacement object directly from the data row without wrapping values in arrays. This approach simplifies your code by allowing you to reference values directly, such as using row[2] for “Date of Request”. Using getValues() provides a clear 2D array, making your code more reliable and reducing the chance of mistakes.
More information is needed on your end more importantly on how you are implementing the current solution to your project. The issue might be coming from the implementation and how it was being called. Take note that this answer is not reflecting that the changed code is the source of error but rather, another course of action that you can use to avoid any error any further resulting with the same result.
NOTE: the removeFile() has already been deprecated, I removed it from your code by rendering it as a comment.
UPDATED CODE:
function createPDF() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
const data = sheet.getDataRange().getValues();
const pdfFolder = DriveApp.getFolderById("1GiY42M3rTq36M9pnUq7Y6RE1qQ8TFN8X");
const tempFolder = DriveApp.getFolderById("1bkzpVTURICEvcJWemFGaOYTZSdeF_XNj");
const templateDoc = DriveApp.getFileById("1UfFzcIdG1DCAYpk-NWSiA7bEoz1ZSJXeuV_9-Okw_vg");
for (let i = 1; i < data.length; i++) {
const row = data[i];
const replacements = {
"{Date of Request}": row[2] || "N/A",
"{Requestor}": row[3] || "N/A",
"{Line}": row[4] || "N/A",
"{Cost Centre}": row[5] || "N/A",
"{Project Name}": row[6] || "N/A",
"{Project Code}": row[7] || "N/A",
"{PN}": row[8] || "N/A",
"{Reason for Requirement}": row[9] || "N/A",
"{Quantity}": row[10] || "N/A"
};
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
for (const [key, value] of Object.entries(replacements)) {
body.replaceText(key, value);
}
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
pdfFolder.createFile(blobPDF).setName(replacements['{Requestor}'] + " " + replacements['{Date of Request}']);
//tempFolder.removeFile(newTempFile);
}
}
OUTPUT:
REFERENCES:
- getValues()
- removeFile()
6