Range.getValue() of a 1-cell range takes 3 to 7 seconds to execute

2
votes

I have a script that used to run in well under a minute. Recently I started getting "Exceeded maximum execution time" errors indicating it now breaks the 6 minute limit.

Using 'Execution transcript' I found that the few getValue() and getValues() calls I make take very long time, so I experimented.

The following test function takes anywhere from 3 to 7 seconds to execute:

function test_singleCellRange_getValue() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Marcin');
  var range = sheet.getRange(1, 1);
  var value = range.getValue();
}

The 'Execution transcript' shows that the getValue() call takes all the time:

[17-10-29 14:15:00:002 EDT] Starting execution
[17-10-29 14:15:00:019 EDT] SpreadsheetApp.getActive() [0 seconds]
[17-10-29 14:15:00:074 EDT] Spreadsheet.getSheetByName([Marcin]) [0.054 seconds]
[17-10-29 14:15:00:075 EDT] Sheet.getRange([1, 1]) [0 seconds]
[17-10-29 14:15:07:377 EDT] Range.getValue() [7.301 seconds]
[17-10-29 14:15:07:381 EDT] Execution succeeded [7.36 seconds total runtime]

The single cell contains a constant string value (i.e. no formulas). Is this dismal performance of getValue() normal (i.e. the new performance standard to expect from Google's servers), or is there something I can do to improve performance?

Update

Performance of getValue() appears to be heavily impacted by the size of the spreadsheet. Even though the above code executes getValue() on a single cell range its performance appears to be impacted by the overall size of the spreadsheet.

This makes no sense - anyone has experience here? Is this how Google designed the API or is this a bug?

2
google-apps-scriptgoogle-sheets
You might like to take a look at best practices. – Cooper
Thanks @Cooper. I am aware of the best practices and I wrote my code accordingly. It still does perform few getValues() calls on a single sheet so my only performance improvement option is to gobble up the entire sheet with a single getValues() call and than pick data within it - make code more difficult, but doable. This would be a short term solution though - many sheets in the spreadsheet, and size constantly growing. – Marcin K
The "performance standard to expect from Google's servers", specifically for Apps Script, can only be expressed in terms such as "eventually" and "probably". – user6655984
@Desire, you may be quite accurate :), regretably – Marcin K

2 Answers

2
votes

How about using Sheets API v4? Please think of this as one of several answers. When I deal with large data in Spreadsheet, I often use Sheets API because of the process speed. I don't know whether this sample script is useful for your situation. If this is not useful for you, I'm sorry.

Sample script :

This sample script can retrieve the same result with your script.

var sheetId = "### Spreadsheet ID ###";
var range = "Sheet1!A1:A1";
var value = Sheets.Spreadsheets.Values.get(sheetId, range);
// Logger.log(value.values)

In order to use this script, please enable Sheets API for Advanced Google Services and API console as follows.

Enable Sheets API v4 at Advanced Google Services

  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Google Sheets API v4

Enable Sheets API v4 at API console

  • On script editor
    • Resources -> Cloud Platform project
    • View API console
    • At Getting started, click Enable APIs and get credentials like keys.
    • At left side, click Library.
    • At Search for APIs & services, input "sheets". And click Google Sheets API.
    • Click Enable button.

References :

0
votes

There isn't anything that we can directly do to improve the performance of such script.

It's known that calling Google Apps Script services is slow.

For more complex scripts, take a look to the official guide posted on Best Practices.