Question about optimization

[Johnny Kovacs]

HI there,

To my understanding this kind of question may vary by environment and language, so I thought I would come here to ask it specifically about a google apps script living in a library which is accessed by a spreadsheet:

Suppose that there are about 15 different spreadsheets, across which have about 5,000,000 datapoints in total. 

Suppose that a script living in a library is working with a specific subset of that data at all times (different parts of the script utilize different datasets). 

Suppose the library is plugged into 12 different "Spreadsheet output files", which, at 3am, all of them are set to fire off, and run the script in the library, once for each refresh of the data on each of their tabs (between 6 and 12 tabs per spreadsheet output file)

 

Currently, the original source data is only needing to be retrieved once per run of the script in the library per tab. This means each of those source files are accessed between 6-12 times per file, for a total of round 72-144 times across all the files being updated at 3am. 

Optimization thought: I wondered to myself, if this could be optimized by doing the data retrieval of all 15 source spreadsheets only once, making essentially a variable that looks like json of the 5,000,000 datapoints, and then passing that variable to each run of the script, which would mean that each source data file would only need to be retrieved one time, instead of 72-144 times.

My question is: Would the optimization thought above serve to make the script run faster? Or, since so much data (like 5,000,000 datapoints) would be being passed between functions in the script, it would either not make a difference or actually make it slower?

Any advice on this would be much appreciated!

[cwl...@gmail.com]

In my experience, anything running in memory (especially on the Google mainframes) will run much faster than making multiple calls to Sheets. Of course, it sounds like you really need a database (Firebase?) to handle this kind of thing but have done what many of us have done and use sheets as an ad hoc database replacement.

[CBMServices Web]

Agree on the minimizing accesses to spreadsheets for optimizing, but also look at what other Google services are being used. Something as simple as the Utilities.formatDate method if called enough times will slow down the script by a lot.

When it comes to optimization, I would start with what loops you have defined and what actions are occurring in those loops. Saving a 1 second efficiency in a loop when it is being looped over 5m times will save hours.

A database may be best when you have a large dataset, but it will not provide you with execution optimization savings.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1c0a4ceb-3832-4bf8-9b42-634beb880428n%40googlegroups.com.