Undocumented Sheet API limitation problems

Question

I already looked at posts with people getting similar issues but couldn't find a clear answer.

I try retrieve a 2D array of 264735 slots using this lines of code:

  var optionalArguments = {majorDimension: "ROWS",
                     valueRenderOption: "FORMULA",
  };
  var sourceValuesObject = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeA1Notation, optionalArguments)

but here is what I get:

response code : 413. Message : response too large.

This looks weird because I can't see this kind of limitations written nowhere plus how can user grab large amount of datas if 300000 cells or less make the API bug.

I've tried splitting the requests and it works but this is getting my code more complicated and slower plus I get an empty response when trying to update back the values to my sheet's range.

Am I pointing the right direction, Is this normal, is there a workaround?

EDIT: Here is a sample spreasheet

First I've tried getting the splitted ranges using Sheets.Spreadsheets.Values.get inside a for loop and it worked.

Doing the same thing with batchGet gives me the same error so I guess I'm having too large datas inside the cells.

Answer 1

How about this workaround?

Experiment:

This experiment uses your shared sample spreadsheet.

When the endpoint of Sheets API is directly called by UrlFetchApp, if the response size is larger than 50 MB (52,428,800 Byte), the response less than 50 MB is returned. The size of 50 MB is due to the limitation of UrlFetchApp. On the other hand, at Advanced Google Service, it cannot confirm this situation, because the error occurs when it is over the limitation. So by using UrlFetchApp, the reason of the error in your situation can be confirmed. So at first, I confirmed this using the following script.

var spreadsheetId = "#####";
var range = "'Copie de Feuille 1'!A1:JE1000";
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + range + "?majorDimension=ROWS&valueRenderOption=FORMULA";
var res = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});
Logger.log(res.getContentText().length.toString())
var values = JSON.parse(res.getContentText());

When the above script is run, 52428450 is returned. And the error of "Unterminated string literal" occurs at the last line. This means that the object is incomplete. From this result, it is found that the values cannot be retrieved by one call of values.get with the range of 'Copie de Feuille 1'!A1:JE1000. This is the same situation with I'-'I`s comment.

In your sample spreadsheet, it was found that the boundary of range that the error occurs is 'Copie de Feuille 1'!A1:JE722. When the values are tried to be retrieved from the range 'Copie de Feuille 1'!A1:JE723, the error occurs. The size of values retrieved from 'Copie de Feuille 1'!A1:JE722 is 52,390,229 bytes. This is less than 50 MB (52,428,800 Byte). The size from 'Copie de Feuille 1'!A1:JE723 is 52,428,450 bytes which is the same value from 'Copie de Feuille 1'!A1:JE1000. From this, it is found that it is over the limitation of UrlFetchApp.

Workaround:

In order to avoid this error and retrieve all values, as a workaround, I think that I would like to propose that it splits the range for retrieving values from Spreadsheet. But in your question, you want the speed. So I would like to propose the following sample script.

1. Create requests.
2. Fetch the created requests using UrlFetchApp.fetchAll().
   • By UrlFetchApp.fetchAll(), each request can work by the asynchronous processing.
   • At Sheets API of Advanced Google Service, this cannot be used. And also, at values.batchGet, because the all retrieved values is over the limitation, the error occurs.
   • By this, the process cost using UrlFetchApp.fetchAll() becomes lower than that of Sheets API of Advanced Google Service.

Sample script:

var ranges = ["'Copie de Feuille 1'!A1:JE500", "'Copie de Feuille 1'!A501:JE1000"]; // This was used from the shared spreadsheet. So please modify this for your environment.
var token = ScriptApp.getOAuthToken();
var requests = ranges.map(function(e) {
  return {
    method: "get",
    url: "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + e + "?majorDimension=ROWS&valueRenderOption=FORMULA",
    headers: {Authorization: "Bearer " + token},
    muteHttpExceptions: true,
  }
});
var res = UrlFetchApp.fetchAll(requests);
var values = res.reduce(function(ar, e) {
  Array.prototype.push.apply(ar, JSON.parse(e.getContentText()).values);
  return ar;
}, []);
Logger.log(values.length) // 1000
Logger.log(values[0].length) // 265

Note:

• If you want to use the script, please confirm that Sheets API is enabled at API console.
• When the arrays are merged, if the limitation error of array occurs, please use each array without merging arrays.
• When Sheets API of Advanced Google Service is used, also no error occurs at the range of 'Copie de Feuille 1'!A1:JE722 and the error occurs at 'Copie de Feuille 1'!A1:JE723. This result is the same with the result of UrlFetchApp.

References:

• Quotas for Google Services
• fetchAll(requests)

Answer 2

Well here is what I've found by trying to compare different ways to get values:

function compare(){
  getValuesSpreasheetApp();
  getValuesUrlFetch();
  getValues();
}
//using spreadsheetApp.getFormulas()
function getValuesSpreasheetApp(){
  var t0 = new Date().getTime();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataRange = ss.getActiveSheet().getDataRange();
  var formulas = dataRange.getFormulas();
  var values = dataRange.getValues();
  var t1 = new Date().getTime();
  Logger.log("spreadsheetApp: " + (t1 -t0));
}
//using UrlFetchApp.fetchAll
function getValuesUrlFetch()
{
  var t0 = new Date().getTime();
  var ranges = ["'Copie de Feuille 1'!A1:JE500", "'Copie de Feuille 1'!A501:JE1000"];
  var token = ScriptApp.getOAuthToken();
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var requests = ranges.map(function(e) {
      return {
      method: "get",
      url: "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + e + "?majorDimension=ROWS&valueRenderOption=FORMULA",
      headers: {Authorization: "Bearer " + token},
      muteHttpExceptions: true,
    }
  });
  var res = UrlFetchApp.fetchAll(requests);
  var values = res.reduce(function(ar, e) {
    Array.prototype.push.apply(ar, JSON.parse(e.getContentText()).values);
    return ar;
  }, []);
  var t1 = new Date().getTime();
  Logger.log("UrlFetch: " + (t1 -t0));
}
//using Sheets.Spreadsheets.Values.get()
function getValues(){
  var t0 = new Date().getTime();
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ranges = ["'Copie de Feuille 1'!A1:JE500", "'Copie de Feuille 1'!A501:JE1000"];
  var optionalArguments = {majorDimension: "ROWS",
                 valueRenderOption: "FORMULA",
 };
  res = [];
  for(var i = 0; i < ranges.length; i++)
    res.push(Sheets.Spreadsheets.Values.get(spreadsheetId, ranges[i]));
  var values = res.reduce(function(ar, e) {
    Array.prototype.push.apply(ar, e.values);
    return ar;
  }, []);
  var t1 = new Date().getTime();
  Logger.log("values.get: " + (t1 -t0));
}

and here is the result:

[18-10-23 01:35:30:034 CEST] spreadsheetApp: 13828

[18-10-23 01:35:33:614 CEST] UrlFecth: 3580

[18-10-23 01:35:38:185 CEST] values.get: 4570

Thanks to @Tanaike for giving me such a precise answer on when the error occurs and giving me a alernative solution. here is the benchmark and fetchAll is the fastest solution.