In Google Sheets, Is there a way to `catch` the "script timeout" exception and log some quick information, before the script is actually terminated?

Question

I have a very large application, written in TypeScript, using Visual Studio Community, that I publish using CLASP. At times, part of this application can run very slow and cause the script to be terminated. I'm trying to figure out what is causing these timeouts.

1. I tried writing to the log and viewing the results in Executions.

   This let me see where the script was, when it timed out, but it really didn't highlight which portions of code were slowing things down.

    

2. I put a timing log together, in hopes of tracking down performance issues.

   function doSomething() {
       let timer = new Timer();
       // Do Something
       timer.stop();
   }
   

   The Timer object automatically identifies the function by parsing the stack and records the starting timestamp. The stop method records the ending timestamp and appends a row to a TIMING_LOG Sheet in the current Spreadsheet.

   This worked well in small tests, but I soon realized that the overhead of adding just a single row of data to a single sheet, was way too high! I wouldn't be able to log each timing log in real-time.
    

3. I altered the stop method to simply push() the timing log data into an array. The I add all those log lines to the TIMING_LOG Sheet, before the operation completes.

   this.LogSheet
       .getRange(
           this.LogSheet.getLastRow() + 1,
           1,
           this.mLogLines.length,
           this.mLogLines[0].length
       )
       .setValues(this.mLogLines);
   
   this.mLogLines = [];
   

   This works surprisingly well, but unfortunately, the lines of the log don't get appended when the script times out.

What I'm looking for is some sort of signal, telling me that the script is being terminated. Like an event I can add a handler for. Something like...

GoogleAppScript.addEventListener("terminating", handleTermination);

Answer 1

The exceeded maximum execution time limit can't be caught when running a script except when calling the script from another platform using the Google Apps Script API. To ensure logging the messages, add flow control points that check for the elapsed time so when it is near to the limit, the script terminates elegantly, instead of throwing this error.

Example:

/**
 * This should be less than 1 to ensure to exit before throwing the maximum execution time limit error.
 */
const SECURITY_FACTOR = 0.8;

/**
 * The official limit is 36000 milliseconds (6 minutes). 
 * Google Workspace accounts might have 1.8E6 milliseconds (30 minutes).
 */
const MY_EXECUTION_TIME_LIMIT = 36000 * SECURITY_FACTOR;


/**
 * Helper to check if the elapsed time have exceeded my execution time limit
 */
const isMyLimitExceeded = () => Date.now() - startTime >= MY_EXECUTION_TIME_LIMIT;


const startTime = Date.now();

function runner(){

  // do something

  if(isMyLimitExceeded){
    // do something before execution be terminated

    // finish
    Logger.log('Warning! The script was terminated before it finished to prevent throwing the maximum execution time limit error.')
    return;
  } else {
    // do something else
  }
  Logger.log('Congratulations! Script finished before exceeding the maximum execution time limit');
}

Similar patterns can be found in questions about processing many identical items, e.g., moving files from one folder to another. For this kind of script, it's common to do the elapsed time check before starting to process a new item or when finishing processing one.

---

From the question

At times, part of this application can run very slow and cause the script to be terminated. I'm trying to figure out what is causing these timeouts.

Execution time times might be affected due to several factors, including some that are on the Internet service, like high traffic and slow Google server response.

When working with spreadsheets, formula recalculation might affect script performance, too.

From the question

This works surprisingly well, but unfortunately, the lines of the log don't get appended when the script times out

Instead of using SpreadshetApp.Range.setValues to log multiple log entries at once, consider using the Advanced Sheets Service and logging the entries as soon as they occur. The Advanced Sheet Service might be faster than the Spreadsheet Service.

Also, could you consider writing detailed logs when the execution time is close to the execution limit? This will help to reduce the impact of the writing logs on the script performance.

Answer 2

Although I'm not sure whether I could correctly understand your question and I cannot know your actual script, how about the following approaches?

Approach 1

About What I'm looking for is some sort of signal, telling me that the script is being terminated., I think that "Method: processes.list" of Google Apps Script API can retrieve script processing information. Ref When a function like "sample" runs, the "processStatus" becomes RUNNING. Upon successful completion, it changes to COMPLETED. If an error occurs, it changes to FAILED. This indicates whether the script has finished. However, currently, there seems to be no built-in function for real-time notification. While a custom script can be created for this purpose, checking the process in real time might be challenging.

Approach 2

About but I soon realized that the overhead of adding just a single row of data to a single sheet, was way too high!, in this case, how about using console.time("label"), console.timeEnd("label") instead of putting a value to Spreadsheet every time? Ref Also, you can see the logs of console in Logs Explorer when you have linked the Google Apps Script project with the Google Cloud Platform Project. Ref By this, I guess that the process cost for checking the time can be reduced.

Approach 3

The data of the logs in the "Approach 2" section can be obtained using API. Ref and Ref Of course, the data of the processing data in the "Approach 1" section can be obtained by API. I guessed that by using both APIs or one of the APIs, the part of the high cost in your script might be able to be obtained.

References:

• Method: processes.list
• Logs Explorer
• Cloud Logging API
• Error Reporting API

Answer 3

Scripts that run through a button, a custom menu item, a sidebar or an installable trigger are limited to six minutes of runtime. That should be plenty if the code observes the best practices.

If your code cannot run within the runtime limit, see An easy way to deal with Google Apps Script’s 6-minute limit.

Very often, bad performance is caused by the spreadsheet rather than the script. To improve spreadsheet performance, see these optimization tips.