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:

  1. function main() {
  2. var start_timer = new Date().getTime();
  3. check_daily_runtime_limit()
  5. //
  6. // Do something
  7. //
  9. var end_timer = new Date().getTime();
  10. var date = new Date(null);
  11. var seconds = date.setSeconds((end_timer-start_timer)/1000);
  13. set_runtime(seconds);
  14. }
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.

 

  1. // Add latest execution's run time to the total run time of the day.
  2. function set_runtime(current_runtime) {
  3. const runtime_cell = "G7";
  5. // Make total runtime zero if it's a new day.
  6. reset_runtime();
  8. var ss_db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB");
  9. var old_runtime = ss_db.getRange(runtime_cell).getValue();
  11. //var hms = '02:04:33'; // your input string
  12. var a = old_runtime.split(':'); // split it at the colons
  14. // minutes are worth 60 seconds. Hours are worth 60 minutes.
  15. var old_runtime_secs = (+a[0]) * 60 * 60 + (+a[1]) * 60 + (+a[2]);
  17. var total_runtime = old_runtime_secs + (current_runtime / 1000);
  18. var date = new Date(null);
  19. date.setSeconds(total_runtime);
  20. total_runtime = date.toISOString().substr(11, 8);
  22. ss_db.getRange(runtime_cell).setValue(total_runtime);
  23. }
// 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.