1

I have large Google Sheets (expected to exceed 50,000 rows) that I want to perform searches on. I have been doing so on GAS by getting sheet data into 2D javascript arrays (range.getValues()), but I am noticing some lag in performance.

I stumbled across this post and am keen to test the speeds of Google Visualization Query. However, I am receiving the below error when trying to implement the same as such:

var clientFleetId = "18SEdE9o9xWK0U8Y0qnleTJ2Qk565tpNdKJWKlBuBgDU"
var clientPermitTypeSheet = "PERMIT TYPES"
var rangeA1 = "A5:F10"
var sqlText = "select A, C, F, 'google' where E = true"

function getAllTrailerPermits (){
  var file = SpreadsheetApp.openById(clientFleetId);
  var sheetId = file.getSheetByName(clientPermitTypeSheet).getSheetId();
  var request = 'https://docs.google.com/spreadsheets/d/' + clientFleetId + '/gviz/tq?gid=' + sheetId + '&range=' + rangeA1 + '&tq=' + encodeURIComponent(sqlText);
  var result = UrlFetchApp.fetch(request).getContentText();
  var from = result.indexOf("{");
  var to   = result.lastIndexOf("}")+1;  
  var jsonText = result.slice(from, to);  
  var parsedText = JSON.parse(jsonText);   // error is raised here

  // responseJSON = JSON.parse(jsonText.replace(/(^google\.visualization\.Query\.setResponse\(|\);$)/g,''));

  console.log(parsedText)

}

ERROR:

1:01:08 PM  Error   
SyntaxError: Unexpected token f in JSON at position 4
getAllTrailerPermits    @ permitTypes.gs:25

Is there any simple example showing how to manipulate Visualization queries to quickly query and put the results in a JavaScript 2D array?

Will appreciate any assistance!

Shalin
  • 105
  • 2
  • 14
  • 1
    In your situation, I would like to propose to retrieve the output values as CSV data by adding the query parameter of `tqx=out:csv` to the endpoint. By this, you can simply retrieve the values as 2 dimensional array by `Utilities.parseCsv()`. The sample script can be seen "Sample script 3" of https://stackoverflow.com/a/56663884 If your issue was resolved by the thread, I would like to flag as the duplicated question. – Tanaike Feb 23 '21 at 12:44
  • Thank you Tanaike. I believe this is a duplicate question as well. – Shalin Feb 23 '21 at 14:37
  • Thank you for replying. I'm glad your issue was resolved. I flagged this question as the duplicated question. – Tanaike Feb 24 '21 at 00:47

0 Answers0