1

I tested a simple script on 2 different spreadsheets and the execution took less than 1 sec for the first spreadsheet and about 7 sec for the second spreadsheet (even though it's the same code). Here is the code, very basic (just retreiving a value in a sheet), used on the 2 spreadsheets:

function testScript() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MY SHEET");
  var range = sheet.getRange("A1");
  var value = range.getValues();

}

I called the fonction from the menu using this code:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('MY MENU')
      .addItem('TEST', 'testScript')
      .addToUi();

}

I have nothing else in the script file.

Concerning the spreadsheets:

  • The first one (less than 1 sec execution) is composed of an empty sheet called "MY SHEET".
  • The second one (moredthan 7 sec execution) is composed of 9 sheets with a lot of data and formulas inside + one empty sheet called "MY SHEET" as well (to test the simple code).

Why the execution time is so different between the 2 spreadsheets? The code is exactly the same between them and I'm only dealing with the empty sheet "MY SHEET".

Thanks a lot! :)

Rubén
  • 34,714
  • 9
  • 70
  • 166
Julien
  • 11
  • 1
  • Your script has a "small" error. Since range is a single cell, getValue() should be used instead of getValues(). It should not affect the execution time, I think. By the other hand, `testScript()` does't makes any change. How do you know the script execution time? – Rubén Jan 04 '18 at 19:32
  • I minimized the original code to those 3 lines to highlight the performance issue. That's why it doesn't make any update. Regarding time execution, I added some logs for the 3 lines : Open sheet :46 ms Get range :3519 ms Get values :3451 ms – Julien Jan 04 '18 at 22:57
  • Do your formulas include volatile functions like NOW, RAND or import functions like IMPORTRANGE, etc? Do you know that the Execution Transcript records that automatically? Posible related [Measurement of execution time of built-in functions for Spreadsheet](https://stackoverflow.com/q/46923770/1595451) – Rubén Jan 04 '18 at 23:02
  • I don't know about volatile functions but I'm mostly using IF statement and INDIRECT, FILTER functions. But none of them are present in the sheet "MY SHEET" that I request in my code. I guess even though I only opening this sheet, the other sheets have to refresh ones... – Julien Jan 04 '18 at 23:08
  • When a recalculation is made it encomases the whole spreadsheet, so we should be sure that a recalculation isn't be made at the time that the script is running. – Rubén Jan 04 '18 at 23:17
  • I removed all INDIRECT and FILTER functions from all sheets and the time execution droped to ~200ms. Thank you for your help. Now I need to know how to disable to that or find a workaround. – Julien Jan 04 '18 at 23:27
  • Unfortunately it's not possible to disable the automatic recalculation for non-volatile functions. The workaround will depend on what you need to do, but it could be that you should have to rethink the "architecture" of your "system". – Rubén Jan 04 '18 at 23:36
  • 1
    Although I don't know whether this is useful for your situation, when Sheets API v4 is used, there is a case that the process speed becomes fast. In order to retrieve the value of cell ``a1``, you can use ``Sheets.Spreadsheets.get(spreadsheetId, {ranges: "'MY SHEET'!a1:a1"});``. If you use this, please enable Sheets API v4 at [Advanced Google Services](https://developers.google.com/apps-script/guides/services/advanced) and [API console](https://console.cloud.google.com/apis/library/sheets.googleapis.com/?q=sheets). – Tanaike Jan 05 '18 at 00:25

1 Answers1

0

I'm not sure a precise answer can be given for this, but the key factors here are that Apps Script is making remote API calls behind the scenes when you call the built in Google services, and that Google's infrastructure is distributed and complex.

Your calls to SpreadsheetApp functions - - getActiveSpreadsheet(),getSheetByName(),getRange(),getValues() are each making network requests to a Google service, which take time and are affected by a variety of conditions, just like any other network request.

The time it takes each of those to complete can vary widely, in my experience they take from under a second up to about 2 seconds each, and this varies from execution to execution.

As for why it's so different between your two documents, it's hard to say. There may be something about the amount of content in the sheets themselves that has an impact, but I would expect that to be very minor.

More likely it's a quirk of the infrastructure itself, and it's also pretty likely to be a temporary condition causing the one document to perform more slowly than the other on a consistent basis.

For example, those two files may be stored in entirely different data centres in entirely different places, and the amount of latency at one site may be more than the other. With complex distributed architecture like Google Drive there are many other scenarios that could account for the difference. The scripts themselves are separate files, and those could be stored in different locations from the sheets, etc.

Cameron Roberts
  • 7,127
  • 1
  • 20
  • 32
  • Thank you for your reply, I guess indeed it's coming from the Google Drive architecture behind. I tested to duplicate the second spreadsheet document, and run the code again, but I took the same amount of time. I added some log and it looks like getting range and values are the more time consumtion : Open sheet :46 ms Get range :3519 ms Get values :3451 ms – Julien Jan 04 '18 at 22:54
  • I removed all formulas in my 9 extra sheets of the second spreadsheet (by copy-pasting the values of each sheet on themself) and the execution goes way faster: Open sheet :58 ms Get range :143 ms Get values :132 ms. I guess formulas have a huge impact on performance while getting range and values. – Julien Jan 04 '18 at 23:04
  • that's really surprising, in the slower sheet, are you using getValues() on a range which actually contains the formulas? I suppose it may recalculate the formulas on each call if that is the case. If you are accessing a rang that doesn't actually contain formulas, then it's a really odd result. As a general rule, if you need to read in a lot of different values, it's best to just grab the entire sheet (getDataRange().getValues()) and then work on the resulting Array. – Cameron Roberts Jan 05 '18 at 01:01
  • The sheet "MY SHEET" is empty in the 2 spreadsheets, no value, no formulas. I wrote that code to point the performance issue. I'm not even updating any data in the code so it doesn't really make sense to recalculate everything. – Julien Jan 05 '18 at 09:55