Google Apps Script “execution time exceeded” woes

Just how slow is Google Apps Script ?

I’m a big fan of Google Apps Script, but I’m really fed up of seeing “Execution time exceeded”. Obviously data access times can be unpredictable, and although it can be improved through caching it’s never going to be as good as a local Excel Workbook. But once you have the data loaded, how about processing speeds – shouldn’t that be comparable ?

Math Comparison

Lately I’ve been playing around with color space conversions in both VBA and GAS. This involves some heavy Math calculations. Migrating to GAS has been driving me crazy with “execution time exceeded” problems. At first I thought it was related to data access, so I tried some tests with repeated calls to a complex math problem for comparing colors.

Here’s a timing test in VBA

Private Sub testCompareSpeed()
     Dim i As Long, c As cProgressTimer, d As Double
     Set c = New cProgressTimer
       With c
         d = .cMicroTimer
           For i = 1 To 10000
         compareColors i, vbWhite - i
           Next i
       Debug.Print .cMicroTimer - d
      End With
End Sub

 

And the same thing in GAS

function testCompareSpeed() {
      mcpher.useTimer('x').start();
      for (var i = 1 ; i <= 10000 ;i++) {
        mcpher.compareColors (i, mcpher.VBCOLORS.vbWhite - i);
        }
      mcpher.useTimer('x').stop();
      Logger.log(mcpher.useTimer().report());
}

 

The results

Over a few iterations the Excel version averaged out at 2.8 seconds, whereas the GAS version took 24.7 seconds. The implementation of compareColors in GAS is a straight translation to javaScript from VBA and is essentially just a load of Math. There is no data fetching component.

Library slowdown?

Since the functions I’m running are in a shared library, I thought maybe that was affecting it. I ran it in the same context as the library and that brought it down to a consistent 19.2 seconds. – so apparently there is some penalty for using functions from a shared library rather than from the same module.

Is it javaScript?

So let’s put Google Apps Script aside for a moment – maybe my translation from VBA sucked.  What if we run this in pure local javaScript. An amazing and steady average of 0.19 seconds

The Browser?

So far I’ve been running this in chrome on a pretty decent windows 7 PC. How about a different browser?
Here’s the full results

So what does this mean ?

Most spreadsheet applications don’t have this kind of scripting, but certainly it means that you have to tread carefully if you do. A number of times I found I’ve had to split tasks up for GAS, which were trivial on Excel,  because I just can’t get them done without hitting the “execution time exceeded”.

What’s going on in GAS to make it so bad?

Maybe it’s something I’m doing. I have no idea. Anybody have similar experiences ?

For more stuff like this, see from VBA to Google Apps Script