Read a Google Sheet

This example shows how to read the rows of a Google Sheets spreadsheet as records. A GoogleSheetDocument represents the spreadsheet and a GoogleSheetReader reads one sheet, or one range, of it; the first row supplies the field names. Access is authorized with the OAuth 2.0 flow for installed applications from Google's client library.

To run it, enable the Google Sheets API in the Google Cloud Console, create an OAuth client ID of type Desktop app and save its JSON file as src/main/resources/integrations.json. The first run opens a browser window asking you to grant access; the token it returns is cached under the tokens directory so later runs do not prompt again.

Java Code Listing

package com.northconcepts.datapipeline.examples.google.sheets;

import java.io.File;
import java.io.InputStreamReader;
import java.util.Collections;
import java.util.List;

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.google.sheets.GoogleSheetDocument;
import com.northconcepts.datapipeline.google.sheets.GoogleSheetReader;
import com.northconcepts.datapipeline.job.Job;

public class ReadAGoogleSheet {

    private static final String SPREADSHEET_ID = "YOUR SPREADSHEET ID";
    private static final String SHEET_NAME = "Sheet1";
    private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
    private static final String TOKENS_DIRECTORY_PATH = "tokens/sheets-readonly";
    private static final List SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
    private static final String CLIENT_SECRET = "/integrations.json";

    private static Credential authorize(NetHttpTransport httpTransport) throws Throwable {
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY,
                new InputStreamReader(ReadAGoogleSheet.class.getResourceAsStream(CLIENT_SECRET)));
        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                httpTransport, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(new File(TOKENS_DIRECTORY_PATH)))
                .setAccessType("offline")
                .build();
        LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();
        return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
    }

    public static void main(String[] args) throws Throwable {
        NetHttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();

        GoogleSheetDocument document = new GoogleSheetDocument(authorize(httpTransport), httpTransport)
                .open(SPREADSHEET_ID);

        DataReader reader = new GoogleSheetReader(document)
                .setSheetName(SHEET_NAME)
                .setFieldNamesInFirstRow(true);

        DataWriter writer = StreamWriter.newSystemOutWriter();

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. SPREADSHEET_ID is the long id in the spreadsheet's URL (https://docs.google.com/spreadsheets/d/<id>/edit); SHEET_NAME is the tab to read. SCOPES requests read-only access and TOKENS_DIRECTORY_PATH keeps that token apart from the read/write token used by the writing examples.
2. authorize() loads the client secret from integrations.json and builds a GoogleAuthorizationCodeFlow for the SheetsScopes.SPREADSHEETS_READONLY scope (read-only access). A FileDataStoreFactory caches the granted token under tokens/sheets-readonly, and setAccessType("offline") requests a refresh token so the cached credential keeps working. AuthorizationCodeInstalledApp runs the consent flow in a browser, with a LocalServerReceiver on port 8888 receiving the redirect, and returns the Google Credential.
3. main() creates a trusted NetHttpTransport and a GoogleSheetDocument from the credential; open(SPREADSHEET_ID) connects to the spreadsheet and loads its list of sheets. The method also accepts the full spreadsheet URL.
4. A GoogleSheetReader is created on the document; setSheetName(SHEET_NAME) selects the tab and setFieldNamesInFirstRow(true) takes the field names from its first row. The rows are loaded when the reader opens.
5. Job.run() transfers the records to a StreamWriter that prints them to the console. The reader closes the document when it closes.

Console Output

Each row of Sheet1 below the header is printed as a record, followed by the record count.