GAS delay function, for 60 to 180 minutes? Utilities.sleep?

Question

I need to run a function exactly x minutes after another one was ran in Google Scripts.

I need something reliable, and my reading of How to use Utilities.sleep function suggests that it might hold up other calls of the script, especially after more than a few thousand milliseconds.

// Delay for x minutes 
Utilities.sleep(sMinutes * 60 * 1000 )

Is this going to cause issues with the DoPost being called again? Is this the best way? I can't see how to make a Trigger work effectively really.

Answer 1

I appreciate the comments here that lead me to the answers below, but since no one has posted an answer, I thought I would show the next searcher of this question how I made it happen.

There are 2 ways to do this, both rely on a Google Sheet record as a session variable - time ran.

Step 1 Create a sheet with information and 2 columns, for me in Col E "Off?" and Col F "Time" optionally third and fourth columns: "Time turned off" and "Trigger ID" respectively

The first way is to create a trigger that runs at a specific time. Pitfalls

• imprecise as @TheAddonDepot pointed out in comments above
• requires creating and deleting triggers, which meant adding yet another column to the sheet

function createOneTimeTrigger(sMinutes) {
  // Get the current date and time
  var now = new Date();
  
  // Set the trigger time to 60 minutes after now
  //var triggerTime = new Date(now.getTime() + (sMinutes * 60 * 1000));
  
  // Create the time-based trigger
  var trigger = ScriptApp.newTrigger("IFTTTOff")
    .timeBased()
    .after(sMinutes * 60 * 1000)
    //.after(triggerTime)
    .create();

    return trigger.getUniqueId() 
    
}


/**
 * Deletes a trigger - call after the above trigger runs
 * @param {string} triggerId The Trigger ID.
 * @see https://developers.google.com/apps-script/guides/triggers/installable
 */
function deleteTrigger(triggerId) {
  // Loop over all triggers.
  const allTriggers = ScriptApp.getProjectTriggers();
  for (let index = 0; index < allTriggers.length; index++) {
    // If the current trigger is the correct one, delete it.
    if (allTriggers[index].getUniqueId() === triggerId) {
      ScriptApp.deleteTrigger(allTriggers[index]);
      break;
    }
  }
}

The second way, which I settled on as the best method for this is

1. Create a function that checks if the Column "Off?" has an "On" value, and if so, use that data to do what you want to do and then change the value to "Off" and update "Time ran" Column.
2. Then create a time based trigger that runs every x minutes - and checks for the "On" and then if the requisite amount of time has passed.

This method to me is more reliable. I set the trigger to run every 5 minutes, which means it is going to be a maximum of 5 minutes imprecision.

function searchSheet() {
  // Get the active spreadsheet and sheet
  var ssID = "<sheetid>";
  var ss = SpreadsheetApp.openById(ssID);
  var shName = "<sheetName>";
  var sh3 = ss.getSheetByName(shName);
  
  // Get the range of cells to search
  var range = sh3.getDataRange();
  
  // Get the values of the cells in the range
  var values = range.getValues();
  
  // Loop through the rows to find the row with column 5 set as "On"
  for (var i = 0; i < values.length; i++) {
    if (values[i][4] == "On") {

      //Get Time and Time since ran
      var sName = values[i][0]
      var sReference = values[i][1]
      var sAmount = values[i][2]
      var sTimeToUse = WhatTimeToUse(sAmount)
      var sCurrency = values[i][3]
      var sTimeSince = values[i][5]
      var sTimeOff = new Date(sTimeSince.getTime() + (sTimeToUse * 60 * 1000));
      var date = new Date()
      //var sTriggerID = values[i][7];

      if (date > sTimeOff) {
        Logger.log ("Called OFF")

        //Email ?
        MailApp.sendEmail({
        to: "your@email.com",
        subject: "your subject",
        htmlBody:  "Your body"

        });


        CallIFTTT(sReference, "Off")
        sh3.getRange(i+1, 5).setValue("Off");
        sh3.getRange(i+1, 7).setValue(date);
       // deleteTrigger(sTriggerID)

      } else {
        Logger.log("Time not past")
      }
    }
  }

Note, I left the extra lines in here if you use method one, and want to delete the trigger etc.