[SOLVED] Google Forms with Google Sheets using GAS

Google Forms with Google Sheets using GAS

We are using Google forms for an event registration. The form is linked to a Google sheet which uses the following script to achieve certain functions, one of which, is to do an HTTP post when something changes:

function uuid_v2(range) {
  return Array.isArray(range) ? range.map(row => row.map(cell => cell === '' ? '' : Utilities.getUuid())) : (range === '' ? '' : Utilities.getUuid());
}

function sentinelAgent(e){
  Logger.log("Invoking sentinelAgent...");
  FormApp.getActiveForm();
  Logger.log("Event Type: " + e.changeType);
  Logger.log("Event details: " + JSON.stringify(e));

  if(e.changeType=="EDIT" || e.changeType=="INSERT_ROW"){ //The type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER)
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //get activated Spreadsheet
    var sheet = spreadsheet.getSheetByName("Overview"); //get sheet by sheet name
    var headings = sheet.getDataRange().offset(0, 0, 1).getValues()[0]; //get heading
    var column_to_watch = 6; //A=1, B=2, C=3 etc...
    
    var row = sheet.getActiveRange().getRow();
    var column = sheet.getActiveRange().getColumn();
    
    if (e.changeType=="EDIT" && column != column_to_watch)
      return;
    
    var values = sheet.getSheetValues(
        row, // starting row
        1, // starting column
        1, // number of rows
        7 // number of columns
    );
    
    var payload ={}
    
    for (i = 0; i < headings.length; i++) {
      var name = headings[i];
      var value = values[0][i];
      payload[name] = value;
    }
    payload["row_number"] = row;
    payload["change_type"] = e.changeType;
    
    var payload = {
        'method': 'post',
        'contentType': 'application/json',
        'payload': JSON.stringify(payload)
    };
    var _response = UrlFetchApp.fetch(
                  _url,
                  {
                    method: "POST",
                    contentType: "application/json",
                    payload: JSON.stringify(payload),
                    muteHttpExceptions: true,
                  }
                );
      var responseCode = _response.getResponseCode()
      var responseBody = _response.getContentText()
      Logger.log("Outbound request: " + JSON.stringify(payload));
      if (responseCode > 200 && responseCode < 250) {
        var responseJson = JSON.parse(responseBody)
        Logger.log("Inbound response: " + JSON.stringify(responseJson));
      } else {
        Logger.log(Utilities.formatString("Request failed. Expected 200, got %d: %s", responseCode, responseBody))
      }
  }
}

Here's a screen of header from Google Sheets:

When an existing row is modified, the OnChange trigger (of a custom function) works as expected and the entire record is transferred to an API service. However, the OnChange trigger does not work for a new row insertion. An event with type INSERT_ROW is never evident on logs when the form submission occurs. Any thoughts as to whats wrong with this script?

Setting up a trigger for FormSubmit does transfer the the form data. BUT, they do not contain additional fields calculated within the Google sheet after a new row is inserted, such as, static_id and compute_id.

Here's a screen of the triggers:

references:

Solution

Instead of relying on formulas, do the calculations using the function called by the form submit trigger.

For Google Form submissions that are sent to a spreadsheet, use a spreadsheet on-form submit trigger instead of an on-change trigger.

The event object includes a range property of the target range as well as properties holding the form submission values.

References