Google Sheets Script Performance With Slow Custom Function

0
votes

Setup: I have a Google sheet where I would like to run a custom function implemented using a script. This script is used to perform a relatively lengthy URL lookup and decode process (10 ms per call, dependent on bandwidth and ping). The custom function uses one column as input, and returns the result.

Problem: When my Google sheet is opened, the column that uses this calculation is refreshed. This grinds the Google sheet to a halt for about 10 seconds, until each cell in the column is recalculated. This will only become worse as I add to my spreadsheet.

Question: Can I change my function script, or change a setting in Google sheets so that the slow custom function is only calculated when the input cell is changed?

For anyone curious, here is a demo sheet with my problem

2
performancegoogle-apps-scriptgoogle-sheets
You could use onEdit(), then check if what was edited is the cell/range you want to wait for and only then run your script. – Chris
In addition to what ChrisW said, call functions on-demand like call from custom menu/buttons. – TheMaster
I tried your spreadsheet and a copy. A couple of thoughts: 1) it's almost a circular reference because the function parameter "x" is also a multiplier for the Sleep command. The excessive delay disappears when one declares a separate variable for the duration of the sleep period OR edits sleep to be a static value -such as, Utilities.sleep(100);. If I may ask, what benefit do you see in a sleep command in a custom function. It's one thing to stop/interrupt a script for a period of time, but why interrupt a custom function? – Tedinoz
The sleep command represents my actual function: a google API call to un shorten a goo.gl link, followed by a regex parsing of the returned url for GPS coordinates, followed by formatting. Also, I’m traveling and haven’t had time to try the responses left above, but I will soon. – Andrew W.
Okay, I've finally gotten home and had the chance to review your responses. OnEdit seemed promising, since it can be configured so that it only runs when the cells in the column of interest are edited, however I realized that this is the default behavior of google sheets. The delay I am experiencing is that all cells are recalculated on browser refresh, not when one cell is changed. One cell changing takes several micro seconds (not a problem), but recalculating the whole column takes much longer. I suppose I will have to live with the slow open time. Thanks.. – Andrew W.

2 Answers

1
votes

On my answer to In google sheets can I wrap a standard function in a custom function to control when it is run? I shared the idea of having a couple of buttons called "freeze" / "unfreeze" to control when the recalculation of "expensive formulas" is done.

On this case, you could "freeze" the range with your custom function before closing the spreadsheet so the next time that you open it will open faster, then when you need to update the frozen range you "unfreeze" it.

After posting the original version the OP asked for a simple version but as my original implementation was something quick and dirty with stuff in Spanish I shared there just the most important code lines. The core methods services are

  • A global variable to store the formula if it will always be the same
  • copyTo to overwrite the formula results range with the values a la copy-paste-values-only
  • clear to delete the formula results pasted previously
  • setFormula to add the formula back to the spreadsheet.

Then I realized that I could improved that published an unlisted Google Sheets add-on that use the PropertiesService to implement a "formula store" with functions to add/remove/list the formulas and a dynamic menu instead of buttons.

Something else to try is to use a time-driven trigger that runs, let say nightly just in case that you forgot to freeze the range for the custom function results.

0
votes

Slightly better performance if there are less custom functions. You can rewrite your function to take in an array, so you'd only have one function instead of 100 running at once. 

function mySlowFunction(x) {
  //Utilities.sleep(x*100); //100 ms
  if (x.map) {
    return x.map(function(y) {return mySlowFunction(y) });
  } else {
    return x * 100;
  }
}