Google form url point to last updated answer

Question

I have created a Google form with a Google spreadsheet associated to it that can be accessed (using the form URL) by each recipient I am sending it to. Right now the URL isn't unique to each recipient. The recipient can access the form then submit it. Upon submit, I am generating an edit URL via the GetEditResponse() function available in the Google FormResponse Class. The recipient can then edit their response using the edit url I provide them once they submit the form.

Here is my Google App Script code:

function myFunction() {
  assignEditUrls();

}

function assignEditUrls() {
  var form = FormApp.openById('formId');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');

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

  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));

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

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


function shortenUrl(longUrl) {
  // google url shortener api key
  var key = "apiKey";

  var serviceUrl="https://www.googleapis.com/urlshortener/v1/url?key="+key;

  var options={
    muteHttpExceptions:true,
    method:"post",
    contentType: "application/json",
    payload : JSON.stringify({'longUrl': longUrl })
  };

  var response=UrlFetchApp.fetch(serviceUrl, options);

  if(response.getResponseCode() == 200) {
    var content = JSON.parse(response.getContentText());
    if ( (content != null) && (content["id"] != null) )
      return content["id"];
  }

  return longUrl;
}

You can also view the Spreadsheet associated with the form.

The problem with this model is that the recipients need to get back an edited URL in order to edit their response at a later time. Instead I want them to keep the same URL I originally provided them with and each time they come back to that URL I should identify which recipient it is and redirect them with the last updated URL.

For that scenario to happen I would need to:

1. Create a unique URL (identifier) for each recipient.

2. The same URL should point to the last updated URL (Form) so that they can always use the same URL originally provided (backend process).

Is this possible to achieve using Google's available tools? If it is, how can I create a unique url to identify which recipient responded to the form?

score 1 · Answer 1 · answered Oct 28 '15 at 20:11

1

Staying within the confines of Google Apps Script, you cannot provide a redirection to a Google Form.

Here are two ideas off the top of my head...

If you're willing to use another service to handle the redirection, you could have it do the redirection for you. (I'm not recommending any, just saying it's an option.)
You could write a Google Apps Script web service that would present a mock-up of your real form. Users would have a unique URL to pass their unique identifier as a HTTP query parameter; heck, you could use goo.gl to produce a short URL for each of them as you do now. Based on the identifier, pre-fill the fake form with their last results. Upon commit, your web service can submit the form programmatically.

answered Oct 28 '15 at 20:11

Mogsdad

44,709
21
151
275

I like the second Idea. Basically I would generate a unique url for each recipient. When they submit the form I'll populate their answer in the spreadsheet. Now when they access the same url again, I'll check against my spreadsheet, identify the url, prefill it with what they responded before so that they can see their answers from the previous time using the same url I originally provided them with. So basically every time they access the url I just prefill the form with what they previously submitted. Is this logic correct? – Cyzanfar Oct 28 '15 at 20:16
That's it - it sounds doable. – Mogsdad Oct 28 '15 at 21:17
It does indeed :). Would you happen to know how to generate a unique form url for each recipient that will fill in the survey? right now [THIS](https://docs.google.com/forms/d/1vsqvwomoqSXwF6TlNkmmktAAk2av2r-2LRrBYJdv3VQ/viewform) url is the exact same for all recipients, I'd need something unique to identify who responded. Any ways you would know how to do that? – Cyzanfar Oct 28 '15 at 21:21
The unique URL will be for the _web app + query parameters_, not for the Google Form. [Here](http://stackoverflow.com/a/18669532/1677912) is a way to do it all without a google form, [this answer](http://stackoverflow.com/a/20510224/1677912) is obsolete with the new Forms, but has some useful ideas in it. [Here](http://stackoverflow.com/a/20110656/1677912) shows how to get existing responses to build a pre-filled URL (you could adapt it for presenting current responses + submitting updates). – Mogsdad Oct 29 '15 at 01:21
Great! I looked over the code you have [here](https://gist.github.com/mogsdad/6472790). I have trouble rapping my head around this... I need a way to create a unique id the very first time a recipient opens the form, so even before submitting it there is already a unique id that differentiates the recipients form. Like this, the next time they click on the link and edit their response I can check against my spreadsheet and Identify which recipient it is and give him his last updated answers. – Cyzanfar Oct 29 '15 at 13:54
You won't be able to do that using stock Google Forms. (There are multiple questions about it around here.) Using a web app, though, you could react to "no parameter" by creating a unique ID for that user. – Mogsdad Oct 29 '15 at 13:57
Ok I see. Last question. Since I already know who I am sending the survey to ( organization name ) can't I just prefill the url then automatically get the edit response url and send that instead of just the original url? That will ensure that 1. it is unique (using shortened url) and 2. the url will always be the same – Cyzanfar Oct 29 '15 at 14:35

score 1 · Answer 2 · answered Oct 29 '15 at 05:34

1

Create a unique URL (identifier) for each recipient.

Creating a separate Google Form for each person will give you this.

The same URL should point to the last updated URL

Assuming your go with creating a separate Form for each user, try placing the edit URL back into each Form's description or 1st page some where after submission of the 1st response. You'd have to make it clear that they have to click on that link going forward. There isn't a setting to automatically re-direct original Google Form URLs to their corresponding Edit Response ones AFAIK, if that's what you are asking for.

answered Oct 29 '15 at 05:34

Bryan P

5,031
3
30
44

This is a solution. The downside is that I would need to create ~ 5000 form. – Cyzanfar Oct 29 '15 at 13:02
if this is for users under just 1 domain / org, then the option to automatically collect emails will uniquely identify the respondents. you already have that turned on? – Bryan P Oct 29 '15 at 15:24

Google form url point to last updated answer

2 Answers2