CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-formseventtrigger
Uncle Slug
Uncle Slug

Reputation: 913

Having trouble triggering a Google spreadsheet function on Form Submission

I have created a Google form that is linked to a Google spreadsheet containing two sheets. I have also created a function in Google Scripts, called "handleFormSubmission", that when triggered (on submission of the linked form):

  1. creates a variable containing the values of this submission from sheet one: var s0Row = s0.getRange("A"+(s0Last)+":J"+(s0Last)).getValues();

  2. then, if a matching ID condition is met on both sheets, sets those values from sheet one into the appropriate range on sheet two: s1Row.setValues(s0Row);

There's probably a better way to do this, but for now the function works fine when run from Google Scripts and the form is returning submissions to sheet one.

The problem I'm having is getting this function to trigger when the linked form has been submitted. I've attempted to set this trigger up as you'll see in the screen shot below.

Code 

function handleFormSubmission() {
  var ss = SpreadsheetApp.openById("1FmArzo50IV2Wmykgsa89l_EARjzkiyeFDoPaCjGyBZM");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActive();
  var s0 = sheet.getSheets()[0];
  var s0Last = s0.getLastRow();
  var s1 = sheet.getSheets()[1];
  var s1Last = s1.getLastRow();
  var s0Bid = s0.getRange(s0Last, 10).getValue();
  var s0Row = s0.getRange("A"+(s0Last)+":J"+(s0Last)).getValues();
  for (var i = 2; i < s1Last + 1; i++) {
    var s1Bid = s1.getRange(i, 10).getValue();
    var s1First = s1.getRange(i, 2).getValue();
    var s1LastName = s1.getRange(i, 3).getValue();
    var s1Row = s1.getRange("A"+(i)+":J"+(i));
     if (s0Bid === s1Bid) {
      Logger.log(i + " " + s1First + s1LastName);
      Logger.log("s0: " + s0Bid);
      Logger.log("s1: " + s1Bid);
      Logger.log("Match!");
      s1Row.setValues(s0Row);
      Logger.log("----------------------");
      break;
    } else {
      Logger.log(i + " " + s1First + s1LastName);
      Logger.log("s0: " + s0Bid);
      Logger.log("s1: " + s1Bid);
      Logger.log("Nope...");
      Logger.log("----------------------");
    }
  };
};`

Current Project's Triggers

Current Project's Triggers

Upvotes: 0

Views: 220

Answers (2)

Dean Ransevycz
Dean Ransevycz

Reputation: 953

It's definitely an event on the spreadsheet that you're intercepting, although that may seem counter-intuitive.

I had the same problem & solved it by deleting the trigger, creating a new version of the script, then recreating the trigger. Don't know why it works -- perhaps a GAppsScript expert could explain it to us.

Also, why not get your form submission values out of the event object? So, instead of hunting down the last row of sheet[0] (which could, in an extreme case, not be the submission you're looking for, but the next one), you can access the field you want from the event object using either of:

  • e.values (an array of the form values in the order they appear in the spreadsheet)
  • e.namedValues (a dictionary/hash of the form values)

Upvotes: 2

KRR
KRR

Reputation: 4917

I think the event should be "From form" not "From spreadsheet". That is because you are working on script editor opened from the spreadsheet. It populates when you open the script editor from the form and write the function in that editor. Hope that helps!

Upvotes: 1

Related Questions