Google-sheets – way to generate edit response URL for Google Form

google sheetsgoogle-apps-scriptgoogle-forms

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

Best Answer

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

Short answer

The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point.

Below is a script that is easier to adopt.

Instructions

Create a test form and note the form ID (between ../d/ and /edit in the url).
Set the form to send responses to a new spreadsheet.
You'll be redirected to the new spreadsheet. Note the active sheet name (rename it if you want) and add a header to the column to be used to hold the response edit URLs, i.e. Edit Url. NOTE: Capitalization is very important, so be very careful on how do you write it.
Go to Tools > Script editor to add a Google Apps Script project bounded to the spreadsheet.
Replace the default code with the code below (remember to edit the global variables according to your case).
Save the project, and then add an installable form summit trigger (if you get an "Authorization required" dialog, click "Review Permissions" and then "Allow").
Submit some sample responses to test the solution.

Code

/*
 * Global Variables
 */

// Form URL
var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var columnName = 'Edit Url' ;
// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var columnIndex = headers[0].indexOf(columnName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = startRow-1; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
    }
  }
}

Additional resources

I created a gist with the content from the question and answer. this b/c I'm exploring ways to better collaborate between end-users who write code.

Google-forms – How to we NOT record, but on the fly disregard responses not matching an already existing roster

Short answer

Once the response is submitted it's stored and there isn't a way to avoid this. By the other side, it's not possible to delete individual responses on the responses container bounded to the Google Form.

Explanation

At this time Google Forms responses are stored in the Google form file and optionally could be set to automatically send them to a Google spreadsheet by using a built-in feature.

It's worth to say that Google Forms also could be used as a script container and that it have the event on form submit that could be used to do some things with the submitted responses like to discriminate which responses should be sent to a Google spreadsheet, but not for delete individual responses from the responses container bounded to the Google form file.

Remarks

As was already mentioned in a comment to the question by Canadian Luke, relying on students typing their ID in a Google Form and automatically discriminating their responses based on it could cause "problems". One way to minimize this is to send to each student the URL to a pre-filled Google Form with their ID already filled up.

Best Answer

Related Solutions

Google-sheets – Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

Short answer

Instructions

Code

Additional resources

Google-forms – How to we NOT record, but on the fly disregard responses not matching an already existing roster

Short answer

Explanation

Remarks

References

Related Topic