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