1

Google has build v4 of the Google Sheets API but the documentation of this is still at v3. There are no full examples of how to implement a simple OAuth authentication with Google Sheets API v4.

I see a migration guide here and I also see a partial example here But a full example is nowhere to be found. The "current" version is here and it was last updated in October 2018 without providing a v4 example.

Let's say that after I authorize the user, I'm trying to read a particular spreadsheet in workbook2 and I'm explicitly specifying what columns I need. Then I present that information with Google Visualization API.

So, here's how I do it in Google Sheets v3 legacy.

function makeApiCall() {
var queryString = encodeURIComponent('SELECT A,B,C,E,H');
   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();
}

For v4, if I follow this partial example here, how do I convert the tqURL to a GET method? and how can I still get the response (rsp) as a DataTable()?

    function makeApiCall() {
  var params = {
    spreadsheetId: 'my-spreadsheet-id',  // all clear here
    //what about workbook gid?
    // The ranges to retrieve from the spreadsheet.
    ranges: [],  // [A:A,B:B,C:C,E:E,H:H) ????
    includeGridData: false,
   //no need to include the access_token here?
  };

  var request = gapi.client.sheets.spreadsheets.get(params);
  request.then(function(response) {
  console.log(response.result);
  //var dataTable = responce.getDataTable(); ?? 
  }, function(reason) {
    console.error('error: ' + reason.result.error.message);
  });
}
Elnoob
  • 67
  • 7

2 Answers2

2

I don't believe there is an analog for the Visualization API in the Sheets API(V4). Technically, the visualization API isn't really a part of legacy Sheets API (V3). If anything its a part of the Charts API. So you should be able to continue using it.

You can even use it server-side directly in GAS (see Using Bound Google Scripts to Generate a Query Object).

TheAddonDepot
  • 8,408
  • 2
  • 20
  • 30
  • I'm not replacing the actual Visualization API, just the connection part to take advantage of Google sheets v4 since that's where my data is coming from. It worked. – Elnoob Apr 20 '19 at 16:08
  • @Elnoob What makes you think query is using v3? – TheMaster Apr 20 '19 at 17:32
  • @TheMaster ah, you are right. What the hell? The whole reason I wanted v4 is to be able to change the scopes "https://spreadsheets.google.com/feeds" to "https://www.googleapis.com/auth/spreadsheets.readonly". Is that not yet possible in google visualization api/charts api? – Elnoob Apr 20 '19 at 19:09
  • @Elnoob Should be possible. I don't see why it would not work. – TheMaster Apr 20 '19 at 19:48
  • Yeap, it worked but again, only if I follow the methods @Tanaike posted. If I attempt to pass a query string it fails. His method works perfectly except the chart doesn't like the dateformat and says that the "Column 3 is not a date" when in fact it is – Elnoob Apr 20 '19 at 20:14
1
  • You have already been able to use Sheets API.
    • Sheets API is enabled at API console.
    • The access token can be used for retrieving values from the Spreadsheet.
  • You want to retrieve the values of A:A,B:B,C:C,E:E,H:H from a sheet.
  • You want to retrieve the dataTable using getDataTable() from the values retrieved by Sheets API.

If my understanding is correct, how about this modification? In this modification, I modified your script in your question. Please think of this as just one of several answers.

Modification points:

  • In order to retrieve values from A:A,B:B,C:C,E:E,H:H, use the method of values.get in Sheets API.
    • In this case, at first, the values of A:H are retrieved and the values of the column A, B, C, E and H are retrieved.
  • In order to retrieve the dataTable using getDataTable(), use ChartWrapper Class.

Modified script:

function makeApiCall() {
  var params = { // Modified
    spreadsheetId: 'my-spreadsheet-id',
    range: 'Sheet1!A:H', // Retrieve the values of "A:H".
  };
  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[2], e[4], e[7]]); // Added
    var w = new google.visualization.ChartWrapper({dataTable: values}); // Added

    var dataTable = w.getDataTable();

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

Note:

  • When the method of values.get in Sheets API is used, please use a1Notation for the range. So in this case, the range becomes Sheet1!A:H. This means "A:H" of "Sheet1".

References:

If I misunderstood your question and the result was not what you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • thank you! This looks extremely promising. it looks like I'm receiving a lot of errors now because every column is considered a string. How do I specify in the "values" variable the format of each column? – Elnoob Apr 20 '19 at 06:57
  • @Elnoob Thank you for replying. I apologize for the inconvenience. Although I'm not sure about the values you are using, for example, how about adding ``valueRenderOption: "UNFORMATTED_VALUE"`` to ``params``? I updated my answer. Could you please confirm it? If this was not the result you want, I apologize. At that time, can you provide the sample Spreadsheet for replicating your issue? I would like to confirm about the issue. – Tanaike Apr 20 '19 at 07:28
  • 1
    I should be the one apologizing. I absolutely appreciate your response. This solved the issue. Thanks a lot! I can now use the v4 without requesting the dreaded "feeds" scope that asks for too much from a user. Thanks again! – Elnoob Apr 20 '19 at 07:49
  • One more issue, although I'll try to solve it myself tomorrow. It looks like the var values = response.result.values.map((e) => [e[0], e[1], e[2], e[4], e[7]]) doesn't do anything. It still brings all the columns (A,B,C,D,E,F,G,H) – Elnoob Apr 20 '19 at 08:04
  • @Elnoob Thank you for replying. I'm glad your issue was resolved. About ``var values = response.result.values.map((e) => [e[0], e[1], e[2], e[4], e[7]])``, for example, how about modifying to ``var values = response.result.values.map(function(e) {return [e[0], e[1], e[2], e[4], e[7]]})`` without using the arrow function? – Tanaike Apr 20 '19 at 08:07