Java

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

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

import com.chilkatsoft.chilkat2.ChilkatException;
import com.chilkatsoft.chilkat2.Http;
import com.chilkatsoft.chilkat2.HttpRequest;
import com.chilkatsoft.chilkat2.HttpResponse;
import com.chilkatsoft.chilkat2.JsonObject;

public class ChilkatExample {

    public static void main(String[] args) {
        // 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..
        JsonObject jsonToken = new JsonObject();
        jsonToken.loadFile("qa_data/tokens/googleSheets.json");
        if (!jsonToken.hasMember("access_token")) {
            System.out.println("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"
        HttpRequest req = new HttpRequest();
        req.setPath("/v4/spreadsheets/spreadsheetId/values:batchGet");
        req.setHttpVerb("GET");
        // Add each range to fetch.
        req.addParam("ranges", "Sheet1!A1:A2");
        req.addParam("ranges", "Sheet1!B1:B2");

        Http http = new Http();
        http.setAuthToken(jsonToken.stringOf("access_token"));

        // 443 is the SSL/TLS port for HTTPS.
        HttpResponse resp = new HttpResponse();
        try {
            http.httpSReq("sheets.googleapis.com", 443, true, req, resp);
        } catch (ChilkatException e) {
            System.out.println(e.getLastErrorText());
            return;
        }

        System.out.println(resp.getBodyStr());

        JsonObject json = new JsonObject();
        json.load(resp.getBodyStr());

        // 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"
        //         ]
        //       ]
        //     }
        //   ]
        // }

        int j = 0;
        int count_j = 0;
        int k = 0;
        int count_k = 0;

        String spreadsheetId = json.stringOf("spreadsheetId");
        int i = 0;
        int count_i = json.sizeOfArray("valueRanges");
        while (i < count_i) {
            json.setI(i);
            String range = json.stringOf("valueRanges[i].range");
            String majorDimension = json.stringOf("valueRanges[i].majorDimension");
            j = 0;
            count_j = json.sizeOfArray("valueRanges[i].values");
            while (j < count_j) {
                json.setJ(j);
                k = 0;
                count_k = json.sizeOfArray("valueRanges[i].values[j]");
                while (k < count_k) {
                    json.setK(k);
                    String strVal = json.stringOf("valueRanges[i].values[j][k]");
                    k = k + 1;
                }

                j = j + 1;
            }

            i = i + 1;
        }
    }
}