CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-forms
Ian
Ian

Reputation: 1

Google Trigger for Edited Form Entry

I have a trigger that places the Edit Response URL in the 100th column.

Not sure who i plagiarized this from, please feel free to cite yourself, lol

function assignEditUrls() {
  var form = FormApp.openById('XXXXXXXXXXXXXX');
    //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Current');

    //Change the sheet name as appropriate
  var data = sheet.getDataRange().getValues();
  var urlCol = 100; // 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++) {
    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);  
}

However, i need to expand it to run one of two different scripts, depending on if this is the initial response to a form or an edit of an existing response

//first run
if (check_if_edited(e)==false)
{
  do_something();
}
//all other runs
else 
{
  do_something_else();
}

How do i do this?

[Edit]

I apologize: I want to do this per response, not for the first submission of the form. So the initial response runs one script, but if a user edits their response, i need to run a separate script

Upvotes: 0

Views: 94

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

Use the Properties Service to save a key-value pair after ther first run to check against in the future.

More Information:

As per the documentation:

This service allows scripts to store strings as key-value pairs scoped to one script, one user of a script, or one document in which an editor add-on is used.

So you can use it to create a variable which is bound to the script, on first run, and then change which code you want to execute based on if it exists or not. You can see it like a global-scope boolean which continues existing after the script finishes executing.

Code:

Editing your example:

function onFormSubmit() {
  var sp = PropertiesService.getScriptProperties();
  var user = e.response.getRespondentEmail());

  //first run
  if (!sp.getProperty(user)) {
    do_something();
    sp.setProperty(user, true);
  }
  //all other runs
  else {
    do_something_else();
  }
}

Make sure to turn on the setting for collecting respondent email addresses from the ⚙️ > General > Collect email addresses checkbox in the Form UI.

You will also need to set this up as an installable trigger on form submit from the triggers page accessible from the View > Current project's triggers in the Apps Script UI.

References:

Upvotes: 0

Related Questions