Skip to content

Instantly share code, notes, and snippets.

@erickoledadevrel
Last active April 9, 2022 18:56
Show Gist options
  • Save erickoledadevrel/91d3795949e158ab9830 to your computer and use it in GitHub Desktop.
Save erickoledadevrel/91d3795949e158ab9830 to your computer and use it in GitHub Desktop.
Demonstrate how to create a form submit processing script that can handle missing or duplicate trigger firings.
// Change this values based on your spreadsheet.
var SHEET_NAME = 'Form Responses 1';
var STATUS_COLUMN_NUMBER = 4;
var PROCESSED_STATUS = 'Processed';
var LAST_ROW_KEY = 'lastRow';
var LOCK_TIMEOUT_MS = 60000; // 1 minute
var MAX_RUNTIME_MS = 240000; // 4 minutes
/**
* Handles form submits as they come in.
* Configure this function to run on form submit.
*/
function onFormSubmit(event) {
var range = event.range;
var sheet = range.getSheet();
var rowNumber = range.getRow();
processRow(sheet, rowNumber);
}
/**
* Checks to make sure all recent form submits were processed.
* Configure this function to run on a time-based trigger.
*/
function fallback() {
var start = new Date();
var properties = PropertiesService.getDocumentProperties();
var lastRowChecked = Number(properties.getProperty(LAST_ROW_KEY) || 1);
var sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
var numRows = sheet.getLastRow();
var rowNumber = lastRowChecked + 1;
while (rowNumber <= numRows && !isTimeUp(start)) {
Logger.log(rowNumber);
processRow(sheet, rowNumber);
rowNumber++;
}
properties.setProperty(LAST_ROW_KEY, rowNumber - 1);
}
/**
* Processes a single row / form submission in the spreadsheet,
* ensuring it hasn't already been processed.
*/
function processRow(sheet, rowNumber) {
var lock = LockService.getDocumentLock();
lock.waitLock(LOCK_TIMEOUT_MS);
var statusRange = sheet.getRange(rowNumber, STATUS_COLUMN_NUMBER);
var status = statusRange.getValue();
if (!status || status.length == 0) {
// Add your code here to actually process the row.
statusRange.setValue(PROCESSED_STATUS);
}
lock.releaseLock();
}
/**
* Helper function for determining if the script is close
* to timing out.
*/
function isTimeUp(start) {
var now = new Date();
return now.getTime() - start.getTime() > MAX_RUNTIME_MS;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment