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?

CodePudding user response：

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();