1

I have a Google Form with a Google spreadsheet to store responses with columns: name, email, revenue, and a fourth column Id which is used to identify the particular respondent.

I am trying to generate a unique URL for each recipient so that they can respond to the form and use the same URL to edit the form at a later time. I look at the getEditUrl() (Google Apps Script) method which created a unique URL for the response after submission:

function myFunction() {
  assignEditUrls();  
}

function assignEditUrls() {
  var form = FormApp.openById('1vsqvwomoqSXwF6TlNkmmktAAk2av2r-2LRrBYJdv3VQ');
    //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++) { 
    var resp = responses[i];

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

    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 = "AIzaSyBVG4Q5i1mNI0YAO0XVGZ3suZU8etTvK34";

  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;
}

However I want to do it the other way, which is to first generate the unique URL to be then sent to recipients so they can submit and edit their responses without the need to send them another URL (the EditUrl response).

Is this possible?

HERE is a link to the spreadsheet

1

1 Answer 1

2

Yes, it's possible, but with slight different approach:

Submit one answer for each respondent in order to get one edit URL by each of them, then send the corresponding URL to each respondent.

Below is a code snippet that programmatically submits a response and log some response attributes including the edit response url by two methods, the first one has an issue, the second one is a workaround.

It will work as a stand alone or as a bounded script.

/*
This code shows how to get the edit response url of a 
programmatically submitted response to a Google Form
*/ 

// Replace the form ID by your own form
var formID = '1234567890abcdefghijklmnopqrstuvwxyz';

function myFunction() {
  var form = FormApp.openById(formID);
  var response = form.createResponse();
  var items = form.getItems();
  var item = items[0];
  if (item.getType() == 'TEXT') {
    var textItem = item.asTextItem();
    var itemResponse = textItem.createResponse('my text');
    response.withItemResponse(itemResponse);
  } 
  // Submit response
  var submittedResponse = response.submit();
  // Get submitted response attributes
  var values = {
    ID : submittedResponse.getId(),
    TS : submittedResponse.getTimestamp(),
    /* 
    Issue 4476: FormApp: getEditResponseUrl() produces invalid URL 
    https://code.google.com/p/google-apps-script-issues/issues/detail?id=4476
    */
    ER1 : submittedResponse.getEditResponseUrl(),
    /* Workaround from 
    https://code.google.com/p/google-apps-script-issues/issues/detail?id=4476#c2
    */
    ER2 : submittedResponse.getEditResponseUrl().replace(
      /\?edit2=.*/,"?edit2=" + submittedResponse.getId()
    ) 
  };
  Logger.log(values);
}

References

8
  • How can i do this? It would require to programmatically pre fill the form, submit it and save the editresponseurl to be then sent to each recipient
    – Cyzanfar
    Nov 7, 2015 at 23:45
  • I mean could I do what you just stated programmatically and if so, how?
    – Cyzanfar
    Nov 7, 2015 at 23:51
  • Yes, you could. You will need to use the FormResponse Class
    – Rubén
    Nov 7, 2015 at 23:55
  • I've looked over the documentation and tried to implement it in my code but without success. Any help there?
    – Cyzanfar
    Nov 7, 2015 at 23:56
  • programmatically submit google form responses :)
    – Rubén
    Nov 7, 2015 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.