Using e.values with onFormSubmit

Question

I'm brand new at this so please excuse me if this is a silly question.

I'm using google script to carry out a change based on the submitted answer from a google form. I've got a test function I've been using to get it right in the script editor and it works perfectly. When using the onFormSubmit trigger instead though, getting the variable result from e.values, it isn't doing anything. I've defined the trigger for the project and can see that confirmed in the 'Current Project's Triggers' window so it can't be that.

This is the test function code:

function Test_form_submit() {
   var ss = SpreadsheetApp.getActiveSpreadsheet()
   var sheet = ss.getSheetByName("Group Totals")
   SpreadsheetApp.flush()
   var their_choice1 = "Group 5: Thursday 6-8pm"
   var r = find_limits("C3:E7", their_choice1)
   var count = r[0]
   var limit = r[1] 
   check_availability("545507554", count, limit, their_choice1)

}

This is my trigger function code:

function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Group Totals")
  SpreadsheetApp.flush()
  var their_choice1 = e.values[3]
  var r = find_limits("C3:E7", their_choice1)
  var count = r[0]
  var limit = r[1] 
  check_availability("545507554", count, limit, their_choice1)

}

The choice of theirs I'm wanting is in column D on the spreadsheet so I believe e.values[3] is the right thing to use. I can't understand why nothing is happening.

I tried adding a line of logger.log(e.values[3]) as well but the log is blank when I look.

Any help would be much appreciated!

Answer 1

You can use e.namedValues to get the submitted value for any particular column.

e.namedValues["FULL COLUMN NAME"].toString();

Answer 2

For anyone who has tried using the same methods above with no luck, I found this link https://code.google.com/p/google-apps-script-issues/issues/detail?id=5325. To summarize:

function runonformsubmit(e){
    var itemResponses = e.response.getItemResponses();
    var item1 = itemResponses[0].getResponse();
    //OR to get the title of the question
    var title1 = itemResponses[0].getItem().getTitle();
}

I've successfully used this model to extract the responses. Here is a sample of the code that I currently use:

function createClientDefaultURLs(e){
    var clientResponses = e.response.getItemResponses();
    var clientFName = clientResponses[0].getResponse();
    var clientLName = clientResponses[1].getResponse();
    var companyName = clientResponses[2].getResponse();
    var companyAddress = clientResponses[3].getResponse();
    var clientEmail = clientResponses[4].getResponse();
}

Hope that helps someone else figure out how to extract the responses from the form submissions.

Answer 3

As a complement to Amit's answer, you can check all the values and parameters returned in the eventInfo coming with the onFormSubmit trigger by using a statement like this :

  Logger.log(e);

This will show something like this :

(the values shown here come from an unused form that I had with "garbage" values ;-) but it is sufficient to actually see the structure.)