Context: I have a "sortResponse" Google Sheets App Script function that is triggered 3 minutes after a Google Form Submission.
The new row is added to the bottom of the Sheet when someone submits to the form. I then have a 3rd party app (Zapier) pulling the Data from the new row within those 3 min.
Problem: Zapier takes 3-5 minutes to pull the data, so if User 1 submits to form at 12:00. And User 2 submits to form at 12:03. My "sortResponse" function will trigger after User 1's first 3 min, and sort the two new rows at the bottom into the corresponding order on the sheet, thus leaving Zapier with only a blank row at bottom of sheet and no data to pull.
My Question: How can I reset the "sortResponse" function or "utilities.sleep timer" to restart the 3 min timer when a new user submits to form?
My Current Google Sheets App Script:
/** @OnlyCurrentDoc */
function sortResponses() {
Utilities.sleep(180000);
var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses Master Data");
sheet.sort(4,false); // Column 4: "Start Date" Sort by Descending Order
sheet.sort(2, true); // Column 2: "Company" Sort by Ascending Order
}
What I'm Trying but Can't Get to Quite Work:
/** @OnlyCurrentDoc */
function sortResponses() {
Utilities.sleep(180000);
var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses Master Data");
sheet.sort(4,false); // Column 4: "Start Date" Sort by Descending Order
sheet.sort(2, true); // Column 2:"Company" Sort by Ascending Order
}
function initializeTrigger(){ // run this only once to create a trigger if necessary
var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses Master Data");
ScriptApp.newTrigger("resetFunctionTimer")
.forSpreadsheet(sheet)
.onChange()
.create();
}
function resetFunctionTimer(e){
Logger.log(e.changeType);
if(e.changeType=='INSERT_ROW'){
Browser.msgBox('New Row Added, wait 3 more min');
Utilities.sleep(18000); // Sleeps 3 more min
}
}
Thanks in advance. Long time lurker, first time poster.