How to add "Edit Response" link to Google Forms emails?

Question

I have a simple Google Form that collects data, and, using AppScript, sends confirmation emails to users who fill it out. After user submits the form, on confirmation, s/he will see a link to edit his/her response.

I'd like to include that link as a part of the confirmation email (Right now, it only shows up on the page.) How can I obtain the URL to edit a submitted response?

I am able to get the link to the Form through SpreadsheetApp.getActiveSpreadsheet().getFormUrl(). It gives me the following format: https://docs.google.com/a/domain.com/spreadsheet/viewform?formkey=<formKey>

The link however doesn't include the edit key, which is required for users to edit his/her response. The expected URL should look like this: https://docs.google.com/a/domain.com/spreadsheet/viewform?formkey=<formKey>&edit=<editKey>

Thanks for the help in advance!

-K

Edited:

Added a feature request on this: http://code.google.com/p/google-apps-script-issues/issues/detail?id=1345&thanks=1345&ts=1337773007

score 22 · Accepted Answer · answered Feb 16 '14 at 23:36

The answer that this wasn't possible by @Henrique Abreu was true until very recently. Google seems to have added getEditResponseUrl() to the FormResponse class and with that it becomes possible to use code like this to get the edit URL for a bunch of existing forms:

function responseURL() {
 // Open a form by ID and log the responses to each question.
 var form = FormApp.openById('1gJw1MbMKmOYE40Og1ek0cRgtdofguIrAB8KhmB0BYXY'); //this is the ID in the url of your live form
 var formResponses = form.getResponses();
 for (var i = 0; i < formResponses.length; i++) {
   var formResponse = formResponses[i];
   Logger.log(formResponse.getEditResponseUrl());
 }
}

To make it automatically email the user as they respond one could add a trigger on form submit. As The situation I'm working with doesn't require people to log in with an apps account I don't have access to an email address automatically so I have a text question that captures the user's email address.

It does ask the question about whether or not editing the forms is what you want. I've been grappling with the relative advantages of editing an existing response or sending a prefilled form using toPrefilledUrl() so that I can see how things have changed over time. I guess this comes down to the value that tracking this will provide you.

This totally works, and I [detailed it in a slightly different way here](http://stackoverflow.com/a/42211883/26510) — Brad Parks, Feb 13 '17 at 19:21

score 2 · Answer 2 · answered May 23 '12 at 01:36

2

If you are using Google Apps your responders can edit there form responses.

See: How to Edit Form Responses

answered May 23 '12 at 01:36

ScampMichael

3,688
2
16
23

Henrique G. Abreu · Answer 3 · 2014-08-03T21:52:11.447

--edit this is now possible. See other answers.

After user submits the form, on confirmation, s/he will see a link to edit his/her response. I'd like to include that link as a part of the confirmation email

That is not possible, period.

That link is not accessible anywhere and one can't guess/construct it. But, there's some workarounds that might suit you (some suggested here that I'll re-phrase), e.g.

Send a per-populated form link and have the user re-send it. You'd need to have some kind of control field (e.g. the username), so you can know and delete/ignore his older submits. Possibly automatically via a script.

You could also develop and publish an apps-script GUI and send a link to this apps script plus a parameter that you generate where you can determine which entry you should edit. The down-side of this approach is that it's somewhat cumbersome and overkill to re-design the whole form on Apps Script. But again, it works.

At last, you could open an "Enhancement Request" on Apps Script issue tracker and wait until they and Google Spreadsheet/Forms team get together to develop a solution.

Thanks. I've created a feature request. Hope this goes through... It gets kinda annoying lol. http://code.google.com/p/google-apps-script-issues/issues/detail?id=1345&thanks=1345&ts=1337773007 — DashK, May 23 '12 at 11:39
Yes, I hope too. But if this is somewhat critical for you, I advise that you do not wait and just go with any workaround. From my experience, requests like this can take over an year to "go through". — Henrique G. Abreu, May 23 '12 at 11:46

score 2 · Answer 4 · answered Jan 24 '15 at 23:01

2

Here is a clear blog post that shows you how to do it step by step and explains what's going on under the hood for AppsScripts newbies:

http://securitasdato.blogspot.com/2014/11/sending-confirmation-emails-from-google.html

While collectively you can get there from the all the excellent answers provided here, the script from that post worked best for me.

answered Jan 24 '15 at 23:01

Von

156
1
11

The referred blog post is a great finding. Thanks for sharing the link here. – Rubén Aug 20 '15 at 02:49

score 1 · Answer 5 · edited Jun 19 '13 at 18:21

1

Does this help - I haven't tried it but I was looking for the same thing a while ago and noticed this.

From this page https://developers.google.com/apps-script/reference/forms/

code from there contains this:

Logger.log('Published URL: ' + form.getPublishedUrl());
Logger.log('Editor URL: ' + form.getEditUrl());

Jon

edited Jun 19 '13 at 18:21

Viper

2,216
1
21
41

answered Jun 19 '13 at 17:52

eskdale

11
1
2

score 1 · Answer 6 · edited Mar 18 '14 at 18:45

Try This: (Credits is not for me, because i merge two solutions of the third part)

Source: Send Confirmation Email with Google Forms

/* Send Confirmation Email with Google Forms */

function Initialize() {

    var triggers = ScriptApp.getScriptTriggers();

    for (var i in triggers) {
        ScriptApp.deleteTrigger(triggers[i]);
    }

    ScriptApp.newTrigger("SendConfirmationMail")
        .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
        .onFormSubmit()
        .create();

}

function SendConfirmationMail(e) {
  var form = FormApp.openById('***YOUR FORM CODE***');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('***SHEET NAME***');

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = ***************COLUMN SEQUENCE EX 14******; // column number where URL's should be populated; A = 1, B = 2 etc
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [], url;

  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {

    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
    url = resultUrls[i-1]
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  

    try {

        var ss, cc, sendername, subject, headers;
        var message, value, textbody, sender;

        // This is your email address and you will be in the CC
        cc = Session.getActiveUser().getEmail();

        // This will show up as the sender's name
        sendername = "****YOUR NAME******";

        // Optional but change the following variable
        // to have a custom subject for Google Docs emails
        subject = "Registro de Oportunidade submetido com sucesso";

        // This is the body of the auto-reply
        message = "Nós recebemos seu registro de oportunidade.<br>Muito Obrigado!<br><br>";

        ss = SpreadsheetApp.getActiveSheet();
        headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

        // This is the submitter's email address
        sender = e.namedValues["********COLUMN NAME OF DESTINATION E-MAIL************"].toString();

        for (var i in headers) {

            value = e.namedValues[headers[i]].toString();

            // Do not send the timestamp and blank fields            
            if ((i !== "0") && (value !== "")) {
                message += headers[i] + ' :: ' + value + "<br>";
            }
        }

        message += "<br>Link to edit" + ' :: ' + url + "<br>";
        textbody = message.replace("<br>", "\n");

        GmailApp.sendEmail(sender, subject, textbody, 
                            {cc: cc, name: sendername, htmlBody: message});

    } catch (e) {
        Logger.log(e.toString());
    }

}

There is a problem with the code in this answer. See [this other answer](http://stackoverflow.com/a/22434475/1595451). — Rubén, Aug 20 '15 at 02:46

user3397716 · Answer 7 · 2014-03-18T13:49:15.090

Great, script works! Thanks.

For newbies, like me: Just paste the andre's code for function SendConfirmationMail(e) into your spreadsheet's code editor and set 'on form submit' trigger to run it. That's in spreadsheet script editor, not form script editor.

You need to hack in some values. Read the code. For me the confusing one was the need to replace the ********COLUMN SEQUENCE EX 14****** with the sheet column number where you want the edit urls to end up. I used 39 which is one column more than my form was using up.

However, I got runtime probs in this part:

for (var i in headers) {

            value = e.namedValues[headers[i]].toString();

            // Do not send the timestamp and blank fields            
            if ((i !== "0") && (value !== "")) {
                message += headers[i] + ' :: ' + value + "<br>";
            }
        }

Dunno why, but I replaced it with this:

 for (var keys in columns) {
        var key = columns[keys];
        if ( e.namedValues[key]) {
        message += key + ' :: '+ e.namedValues[key] + "<br>"; 
        } 
    }

Works for me.

Should be used `headers` instead of `columns`. – Rubén Aug 20 '15 at 02:48 — Rubén, Aug 20 '15 at 02:48

score 0 · Answer 8 · answered May 23 '12 at 01:15

0

you can try to populate a form with the values given from that email address than delete previous answers ...

it's not a beautiful way but it can works ...

answered May 23 '12 at 01:15

Edo

1,539
18
26

score -1 · Answer 9 · answered May 23 '12 at 01:07

I don't think we have access to what that value is through the Spreadsheet API (which means Apps Script doesn't have it either). The closest I can think of would be the "key" value in this feed. You'd have to test to find out though. There's no other alternative that I know of other than accessing the Spreadsheet API directly. So first, you'd have to get the last row through the api use ?reverse=true&max-results=1

How to add "Edit Response" link to Google Forms emails?

9 Answers9

Linked