In Getting insights into Sheets performance I mentioned that I was taking a look under the covers of Sheets to see how performance is doing and introduced a useful function for timing stuff. The first thing to recognize though, is that today's Sheets performance is like night and day compared to the early days.
However custom functions (which I tend to steer clear of) are still pretty poor. So what's going on ?
This small custom formula, which doesn't call any services, and just does a little string manipulation shouldn't take any time to run.
and each cell had the formula, dragged down through a few rows.
The results are below
As expected each function took 0-1 milliseconds to run, but overall the elapsed time on 25 rows was 1500 milliseconds, rather than 25 milliseconds
Plotting the start times of each function execution, I can see that mostly nothing was happening in terms of running functions. In fact I've had to exaggerate the dots which represent processing time, just to be able to see them
Clearly the execution time is not much to do with the custom function itself, but rather the server conversations needed to run each one.
Rewriting the custom function now to handle ArrayFormulas
and calling it like this
The function only runs once, eliminating all those inter function delays, and does all the work in 6 milliseconds.
The lesson here is that you should always write your custom functions to handle array formulas (which will process a range all in one go), rather than calling the function repeatedly by dragging down a single cell formula.
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Scripts snippets >