Google Script on a Spreadsheet - create a link to modify the answer via form

Question

I have a form, linked to a spreadsheet. On this spreadsheet I have a script working on the answers of the form. The script have to generate a mail to the user who answered the form, and sometimes ask him to modify his answer. I want to generate a pre-filled-form link (pre-filled with the answer of the user), to send it by mail and so let him change only what i want him to change (not have to fill all the form again)

I saw there is a way to do that using the function

  getEditResponseUrl()
    --> var 'responses' = 'myform'.getResponses();
    --> var 'modificationUrl' = 'responses'[?].getEditResponseUrl()

I don't know which 'responses' of the form to take. (because I have less line in my spreadsheet than number of form response...

Please, is there any way to know which response to use to do the getEditResponse (using the time for exemple..; but I don't know how)

Thank you in advance.

Answer 1

instead of using the link from the form to the spreadsheet, you could maybe write your own the data from the form into the spreadsheet. In the object given by the form you will retrieve his id and with his id you'll then have the ability to link efficiently the entry of your spreadsheet to your form answer.

You can look at this ticket to learn a bit more how to handle form answers. And if you need specific informations (you will do) on how manipulate the response of a form when triggered on form submit you can check this issue.

exemple:

// for a form with 2 question named "question 1" and "question 2"
function submitFormFunc(e) {
  var items = e.response.getItemResponses();
  var responses={};
  for(var i = 0; i< items.length; i++) {
   responses[items[i].getItem().getTitle()]=items[i].getResponse();
  }

  var responseTable = [];
  var responseIndex = ["Timestamp","ID","question 1","question 2"];
  responseTable.push(e.response.getTimestamp().toString());
  responseTable.push(e.response.getId());
  responseTable.push(responses["question 1"]);
  responseTable.push(responses["question 2"]);
  responseTable.push(FormApp.getActiveForm().getResponse(e.response.getId()).getEditResponseUrl());
  SpreadsheetApp.openById("your spreadsheetId").appendRow(responseTable);
}