Dart

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

Reads multiple ranges from a Google Sheets spreadsheet in one GET request. Dart

import 'package:chilkat/chilkat.dart';

void main() {
  // This example requires the Chilkat API to have been previously unlocked.
  // See Global Unlock Sample for sample code.

  // This example uses a previously obtained access token having permission for the 
  // Google Sheets scope.

  // In this example, Get Google Sheets OAuth2 Access Token, the access
  // token was saved to a JSON file.  This example fetches the access token from the file..
  final jsonToken = CkJsonObject();
  jsonToken.loadFile('qa_data/tokens/googleSheets.json');
  if (!jsonToken.hasMember('access_token')) {
    print('No access token found.');
    return;
  }

  // We'll be sending a GET request with query params to this URL:  https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchGet?ranges=Sheet1!A1:A2&ranges=Sheet1!B1:B2
  // The domain is "sheets.googleapis.com"
  // The path is "/v4/spreadsheets/spreadsheetId/values:batchGet"
  final req = CkHttpRequest();
  req.path = '/v4/spreadsheets/spreadsheetId/values:batchGet';
  req.httpVerb = 'GET';
  // Add each range to fetch.
  req.addParam('ranges', 'Sheet1!A1:A2');
  req.addParam('ranges', 'Sheet1!B1:B2');

  final http = CkHttp();
  http.authToken = jsonToken.stringOf('access_token');

  // 443 is the SSL/TLS port for HTTPS.
  final resp = CkHttpResponse();
  try {
    http.httpSReq('sheets.googleapis.com', 443, true, req, resp);
  } on ChilkatException catch (e) {
    print(e.lastErrorText);
    return;
  }

  print(resp.bodyStr);

  final json = CkJsonObject();
  json.load(resp.bodyStr);

  // A sample response is shown below.
  // To generate the parsing source code for a JSON response, paste
  // the JSON into this online tool: Generate JSON parsing code

  // {
  //   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
  //   "valueRanges": [
  //     {
  //       "range": "Sheet1!A1:A2",
  //       "majorDimension": "ROWS",
  //       "values": [
  //         [
  //           "Item"
  //         ],
  //         [
  //           "Wheel"
  //         ]
  //       ]
  //     },
  //     {
  //       "range": "Sheet1!B1:B2",
  //       "majorDimension": "ROWS",
  //       "values": [
  //         [
  //           "Cost"
  //         ],
  //         [
  //           "$20.50"
  //         ]
  //       ]
  //     }
  //   ]
  // }

  var i = 0;
  var j = 0;
  var countJ = 0;
  var k = 0;
  var countK = 0;

  final spreadsheetId = json.stringOf('spreadsheetId');
  i = 0;
  final countI = json.sizeOfArray('valueRanges');
  while (i < countI) {
    json.i = i;
    final range = json.stringOf('valueRanges[i].range');
    final majorDimension = json.stringOf('valueRanges[i].majorDimension');
    j = 0;
    countJ = json.sizeOfArray('valueRanges[i].values');
    while (j < countJ) {
      json.j = j;
      k = 0;
      countK = json.sizeOfArray('valueRanges[i].values[j]');
      while (k < countK) {
        json.k = k;
        final strVal = json.stringOf('valueRanges[i].values[j][k]');
        k++;
      }

      j++;
    }

    i++;
  }
}