Write to a Google Sheet

This example shows how to write the records of a CSV file into an existing Google Sheets spreadsheet. A GoogleSheetWriter writes to one sheet of a GoogleSheetDocument: it creates the sheet when the name is new, clears the target block first, writes the field names in the first row and sends the values in batches. Nulls become empty cells, and dates, times and binary values are written as text.

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.

Writing needs the read/write SheetsScopes.SPREADSHEETS scope, so the token is cached in its own tokens/sheets directory; a read-only token granted to Read a Google Sheet cannot be reused here. To create the spreadsheet instead of writing into an existing one, see Create and Write a Google Sheet.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

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.csv.CSVReader;
import com.northconcepts.datapipeline.google.sheets.GoogleSheetDocument;
import com.northconcepts.datapipeline.google.sheets.GoogleSheetWriter;
import com.northconcepts.datapipeline.job.Job;

public class WriteToAGoogleSheet {

    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";
    private static final List SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
    private static final String CLIENT_SECRET = "/integrations.json";

    private static Credential authorize(NetHttpTransport httpTransport) throws Throwable {
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY,
                new InputStreamReader(WriteToAGoogleSheet.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();

        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
                .setFieldNamesInFirstRow(true);

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

        DataWriter writer = new GoogleSheetWriter(document)
                .setSheetName(SHEET_NAME);

        Job.run(reader, writer);
    }

}

Code Walkthrough

1. SPREADSHEET_ID is the id of an existing spreadsheet and SHEET_NAME the tab to write to; SCOPES requests read/write access.
2. authorize() loads the client secret from integrations.json and builds a GoogleAuthorizationCodeFlow for the SheetsScopes.SPREADSHEETS scope (read/write access). A FileDataStoreFactory caches the granted token under tokens/sheets, 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. A CSVReader reads credit-balance-01.csv with field names in the first row.
4. A GoogleSheetDocument is created from the credential and open(SPREADSHEET_ID) connects to the spreadsheet.
5. A GoogleSheetWriter is created on the document and setSheetName(SHEET_NAME) selects the tab. Writing starts at the top-left cell: the block is cleared, the field names go in the first row and each record becomes the next row.
6. Job.run() transfers the records; closing the writer flushes the last batch of rows and closes the document.

Output spreadsheet

Sheet1 of the spreadsheet holds a header row with the seven field names followed by the five records from the CSV file.