Google Apps Scripts have limitations regarding the daily runtime of the scripts in a single account.

As of today, the quotas are:

• 90 min/day (Gmail account)
• 6 hours/day (G Suit account)

Unfortunately, there isn’t an easy way to track the resources currently used by a project.

 

So, let’s build script to track the daily runtime and notify us through email once it’s about to reach the limit.

 

First of all, we need a new sheet to store all the data needed.

Let’s call this ‘DB’.

Our main function’s structure will look like this:

function main() {
  var start_timer = new Date().getTime();
  check_daily_runtime_limit()
  
  //
  // Do something
  //
  
  var end_timer = new Date().getTime();
  var date = new Date(null);
  var seconds = date.setSeconds((end_timer-start_timer)/1000);  

  set_runtime(seconds);
}

 

 

We measure the execution time of the main function and add it up to the total daily runtime, which is stored in a cell in the ‘DB’ sheet, by calling the set_runtime function.

 

// Add latest execution's run time to the total run time of the day.
function set_runtime(current_runtime) {
  const runtime_cell = "G7";
  
  // Make total runtime zero if it's a new day.
  reset_runtime();
  
  var ss_db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB");
  var old_runtime = ss_db.getRange(runtime_cell).getValue();
  
  //var hms = '02:04:33';   // your input string
  var a = old_runtime.split(':'); // split it at the colons

  // minutes are worth 60 seconds. Hours are worth 60 minutes.
  var old_runtime_secs = (+a[0]) * 60 * 60 + (+a[1]) * 60 + (+a[2]); 

  var total_runtime = old_runtime_secs + (current_runtime / 1000);
  var date = new Date(null);
  date.setSeconds(total_runtime);  
  total_runtime = date.toISOString().substr(11, 8);
  
  ss_db.getRange(runtime_cell).setValue(total_runtime);
}

 

 

Set_runtime also calls the reset_runtime function whose liability is to reset the total daily runtime at the beginning of each day, as well as to store it into a table in ‘DB’ sheet keeping a history.

 

 

So, at the beginning of our main script, we call the check_daily_runtime_limit function which sets 4 time-points. The first one is at 3 hours, second at 4 hours, third at 5 hours and forth at 5:30 hours. When each one of them is reached, a notification email will be sent to our Gmail account.

Those notifications are best for a G-Suite account but we could easily adjust them to our needs.

 

The full script together with the ‘DB’ sheet is included in this spreadsheet.

 

Feel free to grab a copy by clicking on “File” —> “Make a copy”.

 

 

By using such implementation an unexpected pause of our time-triggered script could be prevented.