CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Bob
Bob

Reputation: 44

Does a Google Form Trigger on Submit happen before or after the push into a spreadsheet

If I wished to change an answer in a Google Form, say a response to a MultipleChoice Item, how would I do so? I can trigger the execution of a script on Submit, but does it happen before or after the push into the spreadsheet?

The following code is triggered on Submit, which Submit also results into the form data being pushed into a linked spreadsheet. The spreadsheet updates, but the result is not doing what I want.

function setPrice() {
  var ws = SpreadsheetApp.openByUrl("spreadsheet_link");
  var ss = ws.getSheetByName("BPS")
  var inventory = ss.getRange("A2:A280").getValues();
  var prices = ss.getRange("B2:B280").getValues();
  var prices_no_bp = ss.getRange("C2:C280").getValues();

  var form = FormApp.getActiveForm();
  var formItems = form.getItems();

  var item_selected = form.getItemById("Question 1").asListItem();
  var bp_selected = form.getItemById("Question 2").asListItem();
  var price_selected = form.getItemByID("Price").asMultipleChoiceItem;
  var formResponse = form.createResponse();

// scan the inventory list, find the item selected, and return the price
  var this_price = 999999999;
  var sl = 8;
  for (var i = 0;i < sl; i++) {
    if (inventory[i] == item_selected) {
      if (bp_selected == "Yes") {
        this_price = prices_no_bp[i];
      } else {
        this_price = prices[i];
      }
    }
  }

// Create the multiple choice entry
// Change the response to the same value
// Attach the response to the form
  price_selected.createChoice(this_price);
  var response = price_selected.createResponse(this_price);
  formResponse.withItemResponse(response)

// Responses are ready for submission

}

Is this code not working because I'm changing the response AFTER I've already pushed into the spreadsheet, or because I'm doing it in the right order but not really changing the response?

Upvotes: 0

Views: 472

Answers (1)

Bob
Bob

Reputation: 44

Responses are pushed into the response spreadsheet before the triggered code runs. Changing a response programmatically via the onSubmit trigger is therefore pointless. Best option is to work on the response spreadsheet instead, since that is where the data is available.

Upvotes: 1

Related Questions