Accessing user entered data upon submit in google

问题:

I have a google-form that has the following two fields:

• Email address: - A text box
• Tool: - A radio button
  • Tool 1
  • Tool 2
  • Tool 3

The user would enter his email address and select a tool and click submit. I would like the following message to appear:

Thanks for responding. An email has been sent to you to at entered email address to download selected tool.

I have the following piece of code in the script editor

    function emailFormSubmission() {
        var form = FormApp.getActiveForm();//the current form
        var dest_id = form.getDestinationId(); //the destination spreadsheet where form responses are stored
        var ss = SpreadsheetApp.openById(dest_id);//open that spreadsheet
        var theFormSheet = ss.getSheets()[0]; //read the first sheet in that spreadsheet
        var row = theFormSheet.getLastRow(); //get the last row
        var emailid = theFormSheet.getRange(row,2,1,1).getValue();//get column 2 corresponding to the email id. column 1 is timestamp. so, skip that.
        var tool = theFormSheet.getRange(row,3,1,1).getValue();//get column 3 corresponding to the selected tool.
        form.setConfirmationMessage('Thanks for responding. An email has been sent to you '+ emailid + ' to download' + tool);
    }

I have also set the triggers to be Run -> emailFormSubmission, Events -> from Form , onFormSubmit.

What happens is: Suppose the first user ('A') enters his information and clicks submit. His entered information gets displayed correctly. When second user ('B') enters his information and clicks submit, A's information is displayed. When third user ('C') enters his information and clicks submit, then B's information is displayed. I found that the issue is with "getlastrow()" since the spreadsheet is updated after emailFormSubmission is processed.

Whats wrong with the above code? How do I fix this?

UPDATE

Based on @wchiquito's comments, I changed the code to following to make it work.

function emailFormSubmission(e) {
    var form = FormApp.getActiveForm();
    //Check this link on how to access form response:
    //https://developers.google.com/apps-script/understanding_events?hl=en

    var responses = e.response;//e is of type formresponse.
    var emailid = responses.getItemResponses()[0].getResponse();
    var tool = responses.getItemResponses()[1].getResponse();
    Logger.log(emailid);
    Logger.log(tool);
    form.setConfirmationMessage('Thanks for responding. An email has been sent to  '+ emailid + '   with instructions to download ' + tool +'. If you do not find our email in your inbox, please check your spam folder');  
    Logger.log(form.getConfirmationMessage());
}

回答1:

Remember that the event On form submit (Understanding Events) receives a parameter that has the following structure:

• values​​
• range
• namedValues​​

and you can do something like:

function emailFormSubmission(e) {
    ...
    var row = e.range.getRow();
    ...
}

Try the following code to observe the structure of the parameter e:

function emailFormSubmission(e) {
    ...
    Logger.log(e);
    ...
}

UPDATE

First, excuse my confusion, I showed you the structure of a Spreadsheet form submit event when you really are using a Form submit event.

Sure enough, a Form submit event has the following structure:

• response

Returning an object of type FormResponse.

Therefore, defining the event: On submit form (Form submit event), you can do something like the following:

function emailFormSubmission(e) {
  var itemResponses = e.response.getItemResponses();
  for (var i = 0, len = itemResponses.length; i < len; ++i) {
    Logger.log('Response #%s to the question "%s" was "%s"',
               (i + 1).toString(),
               itemResponses[i].getItem().getTitle(),
               itemResponses[i].getResponse());
  }
}

However, the confirmation message set according to the data sent as responses of the form, does not seem very clear, you can set the message, but will not display for the active response, if not for the next.

回答2:

My first guess is these two lines right here:

var emailid = theFormSheet.getRange(row,2,1,1).getValue();//get column 2 corresponding to the email id. column 1 is timestamp. so, skip that.
var tool = theFormSheet.getRange(row,3,1,1).getValue();//get column 3 corresponding to the selected tool.

When you call getLastRow() on a sheet - you're getting the last row. Sure, but considering the order of events and how these values are processed, you need a +1, to get the most recent submission. Currently you're one row behind when your code runs to update the Form confirmation message.

So just change your code to the following:

var emailid = theFormSheet.getRange(row+1,2,1,1).getValue();
var tool = theFormSheet.getRange(row+1,3,1,1).getValue();

Spreadsheets are the most confusing of Google services, in my opinion. When you get values in the Spreadsheet, they're returned as an [] or [][] depending on what your Range is when you call getValues(). But getRange() on a sheet starts at index 1 (to make it easier to read in code I suppose). Often times I find that I have an off-by-one error because of the way data is passed around. Just keep that in mind as you work with Spreadsheets :)

回答3:

Short answer: want you want can't be done with Google forms.

Explanation: form.setConfirmationMessage() sets the confirmation message for the form as stored on the server, not for the current active form. Same applies for example for form.setTitle(). The active form will not be modified. One would expect different behaviour for the confirmation message, but alas, this is not the case.