How can I reset "sortResponse" function or "utilities.sleep timer" when a new user submits to form?

54 Views Asked by At

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.

0

There are 0 best solutions below