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

Question

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?

Answer 1

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 :

• Advanced Google Services : https://developers.google.com/apps-script/guides/services/advanced
• Sheets API v4: https://developers.google.com/sheets/api/