when attempting to use e.value in google sheet script I get TypeError: Cannot read property "Values" from undefined. (line 7, file "Code")

2
votes

This is a two part question:

  1. I have google sheet with a linked Form. When the form is submitted I want the responses from the form to be copied to another google sheet where I intend to change and reformat and then send via email. The script below is what i have currently written and it has a trigger set up onFormSubmit. However, I keep getting the follow error:

TypeError: Cannot read property "Values" from undefined. (line 7, file "Code")

Code below:

function formSubmitReply(e)
{    
var t = "1g-wIs6nGxu3mJYA1vKtPCxBLCsvh1upeVGbCokOOTIw"; 
var tName = "AggregationOutput";

//Get information from 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');  

// Transfers Data from Form Responses to Temporary file
copyts.getRange('A3').setValue(name);

//Sends copy of 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);

// Deletes temp file
DriveApp.getFileById(tcopyId).setTrashed(true);
}
  1. Part two of my question, even if I can get the code to work what would you recommend when a question is skipped in the form - won't this change the array from e.values. The issue with using the last row as a problem is that I want people to go back and edit responses on the form and then resubmit which means the last row isn't always the row used.

Any and all help is appreciated.

2
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-formsgs-installable-triggers
For part 2: If any of the question is skipped during filling the response, the array of responses does not change. For the answers not filled, no value ("") is stored in the corresponding index in the array. I did not understand the part about going back and editing the responses. Could you please elaborate or explain it in some other way? – Suyash Gandhi
Does this answer your question? How can I test a trigger function in GAS? – Rubén

2 Answers

1
votes

For Part 1, try this:

function formSubmitReply(e)
{    
var t = "1g-wIs6nGxu3mJYA1vKtPCxBLCsvh1upeVGbCokOOTIw"; 
var tName = "AggregationOutput";

//Get information from form and set as variables
var itemResponses = e.response.getItemResponses();

  var email = itemResponses[2].getResponse();
  var name = itemResponses[3].getResponse();

// 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');  

// Transfers Data from Form Responses to Temporary file
copyts.getRange('A3').setValue(name);

//Sends copy of 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);

// Deletes temp file
DriveApp.getFileById(tcopyId).setTrashed(true);
}
0
votes

Question 1: The error you get is due to a wrong syntax, values (All small, not Values)

  var email = e.values[2];
  var name = e.values[3];

Question 2: When the question is skipped the value of the response is blank. So if an email is left blank e.values[2] would still refer to the email field in your form, but will have no value in it.

If you have edit later option activated on the form, the edited responses will only be present in the e.values array. So if they update their email ID only, e.values[2] = "updated Email ID" and e.value[0-1,3-end] = Empty/blank.

To figure out if the submission is new entry or edited entry you can use e.range to figure out where the responses are going to be added in the "form Response" sheet. And you can mirror that range in your "resp" sheet to keep it updated the same way as form response sheet.