One of the concerns I have about add-ons is that the there is no context maintained between invocations. It's the same thing with custom functions. What that means is that the document or spreadsheet structure needs to be rebuilt each time, using something like.
In a normal Apps Script function, the document object persists throughout the function so there no need to keep opening it. With an add-on, you have to open it each time. With a several hundred page document this can take minutes - performance like this is simply not viable.
I decided to look into it to see if I could figure out what was going on.
Performing repeated actions on a growing document and timing how long each of them take to see if there is an inflection point at which the performance suddenly degrades and if so then what is it. Running the tests from an HtmlService and comparing it against a baseline running the same thing with a persisted document should provide some insight.
At first I was a little disappointed, because I noticed that the time to open a document was increasing proportionately to the growing size of the document (and in any case was under a second). It wasn't increasing exponentially like the overall run time - so it wasn't going to be that easy to track down what was causing this.
I noticed that there was an exponential delay between request and the open action.
But the delay was between the end of the previous cycle and before the document open start. In other words, the request to open the document was taking longer to 'be received', but it wasn't clear what was happening during that delay - which could be close to a minute for a document with 1mb content.
My first thought was that saving and closing the document at the end of each cycle might help to reduce the delay in responding to the next cycle (although since the request didn't know it was going to process a document, it's not clear how). However that process of saving is in itself exponential. As the document content reaches 1mb, it takes over a minute to close.
However the good news is that the delay between request and action has gone (implying that there is some kind of implicit document save happening at each google.script.run request).
I'm now starting to suspect this is not completely related to the size of the file, but more about how many times its called. This time I'll clear out the document between each cycle, so each cycle's document should be roughly the same size - and in theory should suffer the same delay - but that is more or less steady, so it is related to the size of the document.
A timestamp in the request can be compared against the time the request gets received to confirm it is indeed processing something in between the time the call is sent and received. .
this is received and logged in the stats package.
The lag (the time between the execution request and it starting) is not exponential, whereas the delay (the time between the previous cycle finishing and the next one starting) is. This pretty much points to there being a non sequential implicit save and close happening outside the flow of the script during which time nothing else can happen.
Assuming this is what's supposed to happen, it makes add-ons involving large files pretty much impractical. One approach could be to always do a save and close at the end of some add-on action in the hope that another one will not be required before it's finished.
The process is controlled from a webapp, which repeatedly provokes a performance test, dumping the results at the end
This adds some random data to a table and appends it to the document, in each case measuring how long operations like opening, reading text and so on take. The data is random to minimize any skew that might be to do with some kind of compression or caching. This example
has the save and close enabled.
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.
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 Docs >