2

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;
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
clarkus978
  • 574
  • 3
  • 10
  • 22
  • 2
    You're in luck! You can still use the visualization API from Google Apps Script. [I wrote a wrapper for it as answer to a previous question](https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object/51328419#51328419). Enjoy! – TheAddonDepot Nov 27 '18 at 20:53
  • @Dimu Designs - Thank you for sharing the script. Everything works, but for some reason I'm getting 'null' for some of the header values. – clarkus978 Nov 28 '18 at 04:16
  • For example `var result = Utils.gvizQuery(ss.getId(), "SELECT A, B, C, D, E", 0, 0);` will return `[[null, null, null, null, null], [Date(2017,4,1), 120145, 28750, 12555, 161450],...]` The header is not captured correctly. – clarkus978 Nov 28 '18 at 19:23
  • 1
    +Clarkus978 Google sheets rows start at 1 (they are not zero-indexed as in most cases related to software programming). So try this instead: `var result = Utils.gvizQuery(ss.getId(), "SELECT A, B, C, D, E", 0, 1);` Note: you'll get back an array of objects, where each object has properties whose keys are the headers. – TheAddonDepot Nov 28 '18 at 19:39

1 Answers1

1

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166