Here is a two-part query:
- I am trying to set up a script that will copy responses from a Google Form to another Google Sheet, where I can modify and reformat them before sending them via email. However, when using the current script with an onFormSubmit trigger, I keep encountering the error message:
TypeError: Cannot read property "Values" from undefined. (line 7, file "Code")
Below is the code snippet:
function formSubmitReply(e)
{
var t = "1g-wIs6nGxu3mJYA1vKtPCxBLCsvh1upeVGbCokOOTIw";
var tName = "AggregationOutput";
//Get information from the form and set as variables
var email = e.Values[2];
var name = e.Values[3];
// Get template, copy it as a new temp, and save the Doc’s id
var tcopyId = SpreadsheetApp.openById(t).copy(tName+' for '+name).getId();
// Open the temporary document & copy form responses into template copy response sheet
var copyt = SpreadsheetApp.openById (tcopyId);
var copyts = copyt.getSheetByName('Resp');
// Transfer Data from Form Responses to Temporary file
copyts.getRange('A3').setValue(name);
// Send a copy of the template in an email as an Excel file
var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + copyt.getId();
var subject = 'Aggregaton Output for' + name;
var body = url;
MailApp.sendEmail(email, subject, body);
// Delete the temporary file
DriveApp.getFileById(tcopyId).setTrashed(true);
}
- In relation to the above code, my second question pertains to dealing with skipped questions in the form. How should I handle this so that it doesn't affect the array from e.values? Considering that respondents may go back and edit their responses on the form before resubmitting, relying on the last row as an indicator may not always work. Your suggestions are welcomed.
Any assistance or advice provided would be greatly appreciated.