How to delay time trigger in google sheets using Google Apps Script?

Question

I have data coming in Google sheet from external software after every 12 minutes. However it does not clear the old data rather it appends new data in the sheet. I want to clear old data first. For that I created a time trigger which runs after every 10 minutes and clear the sheet. Now here is the tricky part:

1. Sheet is cleared after 10 minutes
2. Data comes in after 12 minutes

There is a gap of 2 minutes in which sheet is completely empty and there is no data. I want to close out this gap to 30 seconds. For that I did the following:

function ClearSheet(){
   
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Today_Import");
  Delay();
  sheet.getDataRange().clear();
  
   SpreadsheetApp.flush();
   TriggerDelete();
   SpreadsheetApp.flush();
   TriggerCreate()
}

function Delay(){

   SpreadsheetApp.flush();
   Utilities.sleep(85000);
}

function TriggerDelete() {
  var Triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < Triggers.length; i++) {
    if (Triggers[i].getHandlerFunction() == "ClearSheet") {
      ScriptApp.deleteTrigger(Triggers[i])
    }
  }
}

function TriggerCreate(){
    ScriptApp.newTrigger("ClearSheet")
   .timeBased().everyMinutes(10).create();
}

I created a ClearSheet() function with a delay of approximately 1.2 minutes. after that sheet is cleared, previous trigger is deleted and new trigger is installed and ideally this new trigger should run approximately 1.5 minutes later than the previous but it does not happen.

How to delay my trigger function so it can run after 11-11.5 minutes instead of every 10 minutes?

Answer 1

What about create a trigger to run at specific time? In your case 11.5minutes later than now

  var date_now = new Date();
  date_now.setTime(_date.getTime() + (11*60000 + 30000) ); // = 11,5min

  ScriptApp.newTrigger(callfunction)
            .timeBased()
            .at(date_now)
            .create();

Reference : https://developers.google.com/apps-script/reference/script/trigger-builder#timebased

Answer 2

You can use .after() to set an exact time in milliseconds, after which the trigger runs, and then you can create it again.

However, the problem with Apps Script time-based triggers is that they're inherently innacurate. Even if you set an exact timeframe, it may drift over time depending on the script runtime or just randomness introduced by the server. Even the after() method may run a while after it was specified and you can end up inadvertently clearing the whole sheet during one of the cycles.

You could consider an alternate method such as keeping track of the last row with the CacheService and check for new rows as frequently as possible. Then when new rows are detected you can clear the old data. For example:

function TriggerCreate() {
  ScriptApp.newTrigger('ClearSheet').timeBased().after(1000).create()
}

function TriggerDelete() {
  var Triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < Triggers.length; i++) {
    if (Triggers[i].getHandlerFunction() == "ClearSheet") {
      ScriptApp.deleteTrigger(Triggers[i])
    }
  }
}

function ClearSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Today_Import")
  var current = sheet.getLastRow()
  var lastrow = CacheService.getDocumentCache().get("lastrow")
  if (current > lastrow) {
    if (lastrow > 0) {
      var range = sheet.getRange(1, 1, parseInt(lastrow))
      range.deleteCells(SpreadsheetApp.Dimension.ROWS)
    }
  }
  CacheService.getDocumentCache().put("lastrow", current, 600)
  
  TriggerDelete()
  TriggerCreate()
}

The trigger is meant to run roughly every second with after(), but in practice it ranges from 30 seconds to 2 minutes. Depending on your needs it may still keep the sheet nearly constantly updated and it won't be fully blank at any point, though the newer data may be found at the bottom during short intervals. Note that this may fail if you have users manually writing rows to it, but I guess that's not the case given that you are currently clearing it completely.

You could also apply the same idea of tracking the row to your 10 minute trigger instead of recreating the triggers.

As for your current script, it could be that the Utilities.sleep() is not working because it's within a function or something similar, because adding it directly to the ClearSheet() in my example does delay the script as it should. Do keep in mind that this keeps the trigger running, and there's a trigger daily runtime limit. You probably don't need to use flush() that often either.

To sum it up, it would be best if you could somehow modify the external software to send a clear signal before the data, but aside from that there's no way to reliably run a trigger exactly every 11.5 minutes. You can try to set after() to 11.5 minutes and I think it's more accurate over longer timeframes, but your best bet is to take into account that the time may vary and use a different approach.

References:

• Issue tracker about time discrepancies
• CacheService
• Apps Script quotas
• Time-based trigger methods