CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
clarkus978
clarkus978

Reputation: 644

Query a Google Sheet from Google Apps Script

Previously, I would query a google sheet using the Google Visualization API. I've been working on converting my Google Chart to use Google Apps Script because of authentication issues. How can I do a GROUP BY query to send to my Google Chart using Google Apps Script?

Old Way (Google Visualization API)

function getCurrentData() {
   URL = 'https://docs.google.com/spreadsheets/d/SHEET_ID/gviz/tq?gid=2017811003&headers=1';
   var query = new google.visualization.Query(URL);

   var queryString = 'SELECT A, B, C GROUP BY A, B';
   query.setQuery(queryString);
   query.send(drawChartFunct);
}

New Way (Google Apps Script)

function getCurrentData() {
  var ssID   = "SHEET_ID",
      sheet  = SpreadsheetApp.openById(ssID).getSheets()[2],
      data   = sheet.getRange("A1:C13").getValues()

   ??Query_Code??

  return data;
}

Upvotes: 2

Views: 5323

Answers (1)

Wicket
Wicket

Reputation: 38434

getValues return an array of arrays (2D array / multidimensional array). Google Apps Script doesn't include own methods to handle this kind of objects, so you should use JavaScript either "vanilla" JavaScript or a library.

Upvotes: 1

Related Questions