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

Possible duplicate of [Google Forms onsubmit](https://stackoverflow.com/questions/15685975/google-forms-onsubmit) — Riyafa Abdul Hameed, Jun 21 '18 at 10:56
@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, Jun 22 '18 at 02:18

Diego · Answer 1 · 2018-06-24T06:57:09.073

2

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

edited Jun 24 '18 at 06:57

answered Jun 21 '18 at 10:36

Diego

9,261
2
19
33

Can you elaborate more? What is wrong in my code? Or the way I set teh trigger is wrong? As your information, I can gernerate the response, just the last column for the edit url need to need manually run the script to get it. If it has 100 person submit, I need to run that 100 times. The problem is I need to send the link to them to edit after they submit instantly. So I need a way to be automatic – lotteryman Jun 22 '18 at 02:16
1) Is this a container-bound script? 2) Are the form responses being saved to the spreadsheet that, I assume, this script is bound too? – Diego Jun 22 '18 at 04:00
Hi diego,1) I am not sure is it a container bound script. 2) Yes, all the data in form save into spreadsheet response, except I add another extra column at column 37, to get the google form url. The problem is it will not get the url after someone submit the form. I have to manually run the script to get the link.. – lotteryman Jun 22 '18 at 04:09
1

@lotteryman Take a look at the code example I just posted. I've tested this and it should work for you, but please let me know if it doesn't. – Diego Jun 24 '18 at 06:57
I run the assignEditUrls script as the first action after the form is submitted to refresh the URLs, then I do whatever I need to do with the data and send notifications with the edit URL included. – Janine White Oct 22 '20 at 15:35

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

1 Answers1