Wednesday, 1 April 2015

Getting the Edit Link From A Google Form Response

This code snippet may be of use to someone. I couldn't find an easier way of doing this. You need to familiar with Apps Script and Forms to get this, sorry.

When you have a Google Form saving responses into a Google Spreadsheet, at times it would be nice to be able to give people the link to edit their form ( later ).

So.. in your onFormSubmit(e) function you need...

var timestamp = e.range.getValues()[0][0]
var row_num = e.range.getRow()

If you get the value from e.values or e.namedValues it doesn't work. No idea why... it may be something to do with millisecond precision or the space/time continuum... dunno.

Then you need a function like this...


/**
* Gets edit link for a response's timestamp
*
* @method render_text
* @param {string} Form ID"
* @param {timestamp} A date
* @return {string} A URL.
*/
function get_edit_link_for(form_id, timestamp){
  
  var form = FormApp.openById(form_id)
  var formResponses = form.getResponses();
  
  // Let's work backwards through the list, should get there quicker...
  formResponses.reverse() 
  
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];  
    var response_timestamp = formResponse.getTimestamp()
    
    if ( Number(timestamp) == Number(response_timestamp)){
      var url = formResponse.getEditResponseUrl()
      return url
      break
    }  
  }
  //If it gets to here, it ain't found it. Boo!
  Logger.log( "Error: get_edit_link_for") 


...You then just... 

var edit_url = get_edit_link_for("YOUR_FORM_ID", timestamp)
sheet.getRange(row_num, YOUR_COLUMN_NUMBER).setValue( edit_url )

...and save it in your onFormSubmit function and it's there ready to send out in an email if need be.

Hope this helps.