1

I'm using a Google Sheet to output a nice chart using Google Visualization API.

Recently I moved my code that creates the dataTable to Google Sheets v4 from Google Sheets v3 and I'm not able to achieve this anymore since v4 doesn't support direct "query" manipulation.

I create multiple charts based on the same data so I can't rearrange the columns in the actual sheet.

I know an array or a JSON array doesn't have a method to shift/rearrange columns but I was able to accomplish it in v3 very easy by just querying "SELECT A,B,D,C), like so:

function makeApiCall() {
var queryString = encodeURIComponent('SELECT A,B,D,C');
   var tqURL = new google.visualization.Query(
        'https://docs.google.com/spreadsheets/d/*yourspreadsheetid*/gviz/tq?gid=*yourworkbookid*&headers=1&tqx=responseHandler:handleTqResponse' + '&access_token=' + encodeURIComponent(gapi.auth.getToken().access_token));
      tqURL.send(handleTqResponse);
}

function handleTqResponse(resp) {
  var dataTable = resp.getDataTable();
}

This would output the chart with columns rearranged A,B,D,C.

Here's what I tried in v4:

  function makeApiCall() {
  var params = { 
    spreadsheetId: 'my-spreadsheet-id',
    range: 'Sheet1!A:D', // Retrieve the values of "A:D".
  };
  var request = gapi.client.sheets.spreadsheets.values.get(params); // Modified
  request.then(function(response) {
  var values = response.result.values.map((e) => [e[0], e[1], e[3], e[4]]); // THIS doesn't work at all
  var w = new google.visualization.ChartWrapper({dataTable: values}); // Added

  var dataTable = w.getDataTable();

  }, function(reason) {
    console.error('error: ' + reason.result.error.message);
  });
}

Can this be accomplished with something like this? 

var values = response.result.values.map((e) => [e[0], e[1], e[3], e[4]]); // THIS doesn't work at all

Or maybe just specifying multiple ranges like so?

 range: 'Sheet1!A:B','Sheet1!D:D','Sheet1!C:C' // Retrieve the values of "A:B",D:D,C:C /THIS doesn't work
Elnoob
  • 67
  • 7
  • You can use a DataView to change the order of the columns, [see this answer](https://stackoverflow.com/a/55592057/5090771)... – WhiteHat Apr 20 '19 at 16:35
  • I don't think the first function has anything to do with v3. It's just a visualization protocol. You can query with ChartWrapper class too. – TheMaster Apr 20 '19 at 17:18
  • @TheMaster, ah. Thank you both, I just thought the params method is a google sheets api 4 thing, but it looks like all I did is change the way I get the response. Hm. – Elnoob Apr 20 '19 at 19:13
  • @Elnoob I'm not sure whether I could correctly understand about your issue, yet. So can I ask you about your situation? 1. You want to retrieve the values from column "A:D" using Sheets API. 2. You want to put the values as the column "A, B, D, C" to the data table. If my understanding is correct, how about modifying to ``[e[0], e[1], e[3], e[2]]``? When the values from column "A:D" are retrieved by Sheets API, the values are put in 2 dimensional array. And each column of ``A, B, C, D`` is corresponding to the index of ``0, 1, 2, 3``. If I misunderstand your situation, I apologize. – Tanaike Apr 20 '19 at 23:32
  • @Tanaike, yes you got that right. I was just confused that the output was not correct but it's because the date format is wrong. I'll prob remove this question. I am still struggling to get the date format right now. After modifying this code I don't understand how to change the date columns to JSON format (Date(2019,1,1)). – Elnoob Apr 20 '19 at 23:41

0 Answers0