By @mhawksey

Migrating Excel VBA Macros to Google Sheets/Apps Script: Data reshaping tip, think batches not rows

A question recently came up on Stackoverflow:

I’ve just written my first google apps scripts, ported from VBA, which formats a column of customer order information … The issue is processing time. It cannot handle longer columns of data. I am warned that Method Range.getValue is heavily used by the script.

The question is well presented with the code they are using, example data and things they have tried.

Existing Data

Reformatted data

One of the things to remember when moving from the Excel to the Google Sheets is that every interaction you make with a spreadsheet comes with a performance hit. Also Apps Script has a 6 minute execution limit and other quotas to consider. This requires a slightly different mindset and instead you need to think about things in batches rather than row by row operations.

The Apps Script Developer site has some guidance on batch operations. Basically best practice is to grab all the data you need using .getValues(), which returns  a two-dimensional array of values, iterate across and reshape as required and then if needed output using .setValues(). The performance gain when you do this is staggering. Executing the original code on 1,000 rows of data takes 56.822 seconds total runtime, doing as a batch and you get 0.301 seconds total runtime. You can see the code I suggest using here and if you want to have a play yourself here is some test data with both functions loaded in the script editor

Exit mobile version