e.response.getId().getEditResponseUrl(); in Google Apps Script does not work

Question

I have made a tool that stores ticket number and some details handovered by and to other team members. In that process i used a Form to submit the handover and Google Spreadsheet as database. Also i want to notify the team members as well as implement editing data by them, so i wrote a script as below.

function formSubmitReply(e) 
{
var timeStamp = e.values[0];
var sendTo = e.values[1];
var ticket = e.values[2];
var description = e.values[3];
var editResponseURL= e.response.getId().getEditResponseUrl();
logger.log(editResponseURL);

MailApp.sendEmail(sendTo,
                  "New Handover",
                  ticket +" handovered to you at "+ 
                  timeStamp +"\n\nDescription:"+
                  description +
                  "\n\nKindly check it using the below link\n\n"+
                  editResponseURL +"",
                   {name:"Handover Mail"});}

In the above code everything works fine when i remove the below code

var editResponseURL= e.response.getId().getEditResponseUrl();

There are no errors which is why i am unable to troubleshoot. Also I am not able to log the details. Kindly advice me ways to save the URL. Now if i succeed in saving the URL and sending over the URL to the other person is there any way to allow editing only a single question for eg, of questions ticket number, description, status i want the user to change only to status and rest should be disabled. I am not aware of such a feature in Google Forms.

Answer 1

Just because there are no errors does not mean you can't troubleshoot. A good start would be to look at the execution transcript.

Anyway, the issue is that you're mixing usable event objects. Based on your description that it's the line with:

var editResponseURL= e.response.getId().getEditResponseUrl();

I assume that this script is attached to a spreadsheet. For spreadsheets, e.response is not a usable field, however, if you were to attach the script instead to a form, you would find that e.values are not a usable field.

One possible solution here is to rewrite the script for a form, as the form actually stores the responses, you don't need the spreadsheet to store the data for you, unless you want to be able to read individual responses.

You can find a similar example on the FormResponse page that loops through all the responses to a form and logs their values. From there you can add on the section of your script that mails out the values.

When using a form, in order to get the timestamp, you can then use the method .getTimestamp(), which is more reliable I think then the spreadsheet timestamp (I believe the timestamp on the sheet is the time it was written to the sheet, not the time it was submitted to the form. Usually they're the same time within a few min/seconds, but not always).

With regards to getting an edit URL that only allows you to edit certain responses, no that's not possible. No work around for that springs to mind, and I doubt one exists.