Get url of the google form every time after submission is done

Question

I am using a google script to get the url of the Google form successfully to Google spreadsheets. However, I must manually click run in google script in order to get the url.

How to get the url automatically when someone click the submit button in Google form?

Below is the code of my script

function assignEditUrls() {
  var form = FormApp.openById('1F4YdKMGlJXn9w8aiq0UXzegWEfzjxUMd2CV2vewmoLQ');

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

  var data = sheet.getDataRange().getValues();
  var urlCol = 37; 
  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(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);  
}

I also have set the project triggers to on form submit

However, I still can't automatically get the url of the google form even submitted. I have to manually click run the script in order to get the url in spreadsheet

@Riyafa Abdul Hameed is not helpful as it is an 2014 solution. The Step 8 is gone in current version. You can check on that — lotteryman

Diego Diego · Accepted Answer · 2018-06-21T10:36:57

To use the "on form submit" spreadsheet trigger, you need to first make sure that the form is saving responses to that spreadsheet. You can then use the event object to replace some of the literal values you've declared.

Unfortunately, the trigger doesn't provide the response ID, so you'll need to manually search for it using the provided timestamp.

function assignEditUrls(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  var ts = new Date(e.namedValues.Timestamp[0]);
  var form = FormApp.openById("1F4YdKMGlJXn9w8aiq0UXzegWEfzjxUMd2CV2vewmoLQ");
  var formResponses = form.getResponses(ts);
  var editURLColumn = 37;
  var row = e.range.getRow();
  for (var i = 0; i < formResponses.length; i++) {
    var formResponseTs = (new Date(formResponses[i].getTimestamp())).getTime();
    if (formResponseTs === ts.getTime()) {
      var responseId = formResponses[i].getId();
      var editURL = formResponses[i].getEditResponseUrl();
      sheet.getRange(row, editURLColumn).setValue(editURL);
      break;
    }
  }
}

Get url of the google form every time after submission is done

1 Answers