I used a Google Forms tutorial to tweak Form data to merge into a PDF and then send to an email. I am getting the following error message when I try to run the script:
TypeError: Cannot read property "values" from undefined. (line 11, file "Code")
I do not know how to fix the problem. I have searched the web for an answer. Here is a copy of the script. I marked the 2 lines where the script is giving an error:
var docTemplate = "1ZSqmId2BBjtz6PmgQEmusjnkHGsFKD1CBSq0rrQk6Kc";
var docName = "TestCertificate";
// When Form Gets submitted
function onFormSubmit(e) {
//Get information from form and set our variables
var email_address = "[email protected]";
//**(THIS IS WHERE THE ERROR IS OCCURRING ON THESE 2 LINES BELOW!)**
var full_name = e.values[2];
var Activity = e.values[3];
// Get document template, copy it as a new temp doc, and save the Doc’s id
var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName+' for '+full_name)
.getId();
// Open the temporary document
var copyDoc = DocumentApp.openById(copyId);
// Get the document’s body section
var copyBody = copyDoc.getActiveSection();
// Replace place holder keys,in our google doc template
copyBody.replaceText('keyFullName', full_name);
copyBody.replaceText('keyActivity', Activity);
// Save and close the temporary document
copyDoc.saveAndClose();
// Convert document to PDF
var pdf = DocsList.getFileById(copyId).getAs("application/pdf");
// Attach PDF and send the email
var subject = "Report";
var body = "Here is the form for " + full_name + "";
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});
// Delete temp file
DocsList.getFileById(copyId).setTrashed(true);
}
Here are links to the form and certificate I was testing.
Form/Spreadsheet
Document Template
The error you're seeing is because you're running a trigger function in the Script Editor. When you do this, the Event Parameter e
is not defined - that's what the error message is saying.
For more background, see How can I test a trigger function in GAS?
Here's a test function that will run your onFormSubmit()
function multiple times, with the data that's already in your spreadsheet. It reads each row of the sheet, generates an object to simulate the Event you would get when a form was submitted, then calls the trigger function. If you place breakpoints inside onFormSubmit()
, or rely on Logger.log()
, this technique will allow you to test your trigger function.
function test_onFormSubmit() {
var dataRange = SpreadsheetApp.getActiveSheet().getDataRange()
var data = dataRange.getValues();
var headers = data[0];
// Start at row 1, skipping headers in row 0
for (var row=1; row < data.length; row++) {
var e = {};
e.values = data[row];
e.range = dataRange.offset(row,0,1,data[0].length);
e.namedValues = {};
// Loop through headers to create namedValues object
for (var col=0; col<headers.length; col++) {
e.namedValues[headers[col]] = e.values[col];
}
// Pass the simulated event to onFormSubmit
onFormSubmit(e);
}
}
I've done no other debugging of your original function... but this gets rid of that error message, so you can continue testing.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With