CODEWITHSUNDEEP
javascriptgoogle-apps-script
unacorn
unacorn

Reputation: 1032

Form's Apps Script returning Cannot read property 'value' of undefined error

I have a simple script to generate a doc and PDF upon form submission. I checked all of my code and have submitted a test form to test it out but I kept getting the error of 'values' being undefined when the script is trying to read from the spreadsheet result from the form.

I also tried adding the script in spreadsheet that stores the form submissioon result instead of the form, but I got the same error.

Can anybody points out what have I done wrong?

I set a trigger to execute the script upon form submission: Trigger set

Here's my code:

function myFunction(e) {
  //e.values is an array of form values
  var timestamp = e.values[0];
  var firstName = e.values[1];
  var lastName = e.values[2];
  var title = e.values[3];
  Logger.log(e.values[0]);

  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('google-doc-template-file-id'); 

  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('my-drive-folder-id')
  var copy = file.makeCopy(lastName + ',' + firstName, folder); 

  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId());

  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody(); 

  //Then we call all of our replaceText methods
  body.replaceText('{{First name}}', firstName); 
  body.replaceText('{{Last name}}', lastName);  
  body.replaceText('{{Company}}', company); 

  doc.getAs(pdf);
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 
}

The error occured at line 3 - var timestamp = e.values[0];.

Upvotes: 1

Views: 1882

Answers (2)

J_Everhart383
J_Everhart383

Reputation: 354

I'm pretty sure I know the issue with your original script, as it came from an article on my site. There are two things that need to be changed if you want the original script to work:

  • The script needs to be bound to the spreadsheet accepting form responses, not the form itself.
  • You need to change the 'Event Source' on the trigger to 'from Spreadsheet' instead of 'from Form'

Upvotes: 1

Tanaike
Tanaike

Reputation: 201388

  • In your situation, the container-bound script of Google Form is used.
  • The OnSubmit event trigger is installed to the function of myFunction.
  • You want to retrieve 3 response values of firstName, lastName, title from the 4 questions.
  • You want to also retrieve timestamp.

If my understanding is correct, how about this modification? Please think of this as just one of several possible answers.

Modification points:

  • From the image you provided, it is found that the script is the container-bound script of Google Form.
    • Unfortunately, the event object of the OnSubmit event trigger has no property of values for the container-bound script of Google Form. Ref
    • In this case, it is required to retrieve the response values with e.response.getItemResponses().
  • In the case that values is included in the event object, it is required to be the container-bound script of Spreadsheet. When the container-bound script of Spreadsheet is used, you can retrieve the response values using e.values from myFunction(e). Ref
  • And also, in order to retrieve the time stamp, getTimestamp() is used.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
//e.values is an array of form values
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
Logger.log(e.values[0]);
To: 
var response = e.response;
var timestamp = response.getTimestamp();
var [firstName, lastName, title] = response.getItemResponses().map(function(f) {return f.getResponse()});

References:

Upvotes: 4

Related Questions