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:
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