Using array formulas to improve performance


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.

function nativeCapitalizeCallSign(callSign, name ) {

 

 return name.split(" ").map(function(d) {

   var com = d.slice(0,1).toLowerCase();

   return com[callSign.toLowerCase().indexOf(com) === -1 ?

'toLowerCase' :

'toUpperCase']() + d.slice(1);

 },[])

 .join(" ");

}


Using my timing function on a sheet I can test how long it takes to process called as a custom function

function timeCallSign (callSign, name) {

  var result = timeFunction (nativeCapitalizeCallSign , callSign, name);

  return [[result.start, result.finish, result.elapsed]];

}

and each cell had the formula, dragged down through a few rows. 

=timeCallSign(A2,B2)


The results are below

143773880168114377388016821
143773880134714377388013470
143773880108614377388010871
143773880079414377388007951
143773880107714377388010770
143773880183614377388018360
143773880160314377388016041
143773880206914377388020690
143773880095614377388009571
143773880104614377388010460
143773880137814377388013780
143773880128914377388012901
143773880173014377388017311
143773880159014377388015900
143773880169114377388016910
143773880173314377388017341
143773880094614377388009471
143773880066014377388006611
143773880139514377388013950
143773880177414377388017740
143773880182514377388018250
143773880190114377388019021
143773880207714377388020781
143773880070114377388007010
143773880137714377388013770

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.

Using Array formulas to eliminate processing gaps

Rewriting the custom function now to handle ArrayFormulas

function arrayCapitalizeCallSign(callSigns, names ) {


  // can handle either arrays or single arguments

  if ( !Array.isArray(callSigns) ) {

    return process (callSigns , names);

  }

  else {

    return callSigns.map (function (d,i) {

      return process (d[0], names[i][0]);

    })

  }

  

  // move out the processing to a function common arrays or single

  function process(callSign, name) {

    return name.split(" ").map(function(d) {

      var com = d.slice(0,1).toLowerCase();

      return com[callSign.toLowerCase().indexOf(com) === -1 ? 'toLowerCase' : 'toUpperCase']() + d.slice(1);

    },[])

    .join(" ");

 }

   

}


And testing..

function timeArrayCapitalizeCallSign (callSign, name) {

  var result = timeFunction (arrayCapitalizeCallSign , callSign, name);

  return [[result.start, result.finish, result.elapsed]];

}


and calling it like this

=timeArrayCapitalizeCallSign (A3:A26,B3:B26)

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

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.




For more like this, see  Google Apps Scripts snippets. Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. 




Comments