Soon

I plan to use a onEdit(e)Google Sheets and Google Apps Script (1) event to record the timestamp of the update of cells 1,1 and 180,8 of a 180x8 array of formulas on the understanding that the Google spreadsheet updates from top to bottom and from left to right.

Is this approach adequate?

Considerations

recalculation

Google Spreadsheets has a parameter to determine when to recalculate functions like NOW(), TODAY(), RANDOM, and RANDOM.BETWEEN().

The options are:

• in changes
• In changes and every minute
• In changes and every hour

of (4)

Note: External data functions are recalculated at the following intervals:

• ImportRange: 30 minutes
• ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
• Google Finance: 2 minutes

To Do: Determine if this can be leveraged in any way

Google Apps Script

onEdit(e)it is a simple event. It, its installable version as well as other events in Google spreadsheets respond to user actions, so it can be used to call functions for example when editing a cell.

"Change" is a settable event that can be used for changes of the following types: EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER.

To do: identify what types of changes OTHER encompasses.

It is clear to me from the above that it could be used to identify the start of the recalculation.

To Do: Identify how to determine via Google Apps Script that the recalculation is complete.

Additional Information:

Context

It is required to improve the performance of a Google spreadsheet for which it is being considered to replace the formulas with code. To determine the degree of success, it is desired to have metrics of the execution time of both solutions. In the case of code, for example, you can register the timestamp at the beginning and end and then subtract the first from the last one.

The spreadsheet has a 180 x 8 array of formulas. The formulas take data from the row and header and return a 1x2 array. In other words, formulas are used to nest 1x2 arrays in each element of a 180x8 array.

There is enough space to accommodate the result matrix. In a minimal, complete, and verifiable example of the matrix, headers would be placed every other column. In the actual use case an additional column is included for recording notes.

Spreadsheet performance

Regarding the "understanding" of the order in which the calculations are performed, this is based on the recommendation given in various sources to use the same convention in spreadsheets that is used to read in Western languages ​​(English, Spanish, Portuguese, among others), on the one hand because that is how the search functions work and on the other hand because spreadsheets are recalculated faster this way. Included in references is no. 2 as an example of the various sources. Curiously, in the official documentation, Google Sheets help (see 3), there is no mention of this.

References

1. Events - Guides- Google Apps Script
2. 2. Make your spreadsheet read from left to right and top to bottom
3. File sizes - Google Editors Help
4. Change the locale, time zone, calculation frequency, and language for a spreadsheet - Google Editors Help

Questions on Stack Exchange about reducing recalculation time

• Which factor will give most speedup to every update to a large Google Spreadsheet?
• How can I speed up Google Sheets?