Create and Write a Google Sheet

This example shows how to create a new Google Sheets spreadsheet and write the records of a CSV file into it. createSpreadsheet() on a GoogleSheetDocument creates the document and remembers its id, open() connects to it, and a GoogleSheetWriter fills its first sheet. A new spreadsheet comes with one tab named Sheet1, which is the sheet the writer targets. The URL of the new spreadsheet is printed at the end.

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.

To write into a spreadsheet that already exists, see Write to 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 CreateAndWriteAGoogleSheet {

    private static final String SPREADSHEET_TITLE = "DataPipeline Example";
    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(CreateAndWriteAGoogleSheet.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)
                .createSpreadsheet(SPREADSHEET_TITLE)
                .open();

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

        Job.run(reader, writer);

        System.out.println("Wrote to " + document.getSpreadsheetUrl());
    }

}

Code Walkthrough

1. SPREADSHEET_TITLE is the title of the new 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; createSpreadsheet(SPREADSHEET_TITLE) creates the spreadsheet in the user's Google Drive and open() connects to it.
5. A GoogleSheetWriter is created on the document with setSheetName(SHEET_NAME). The field names go in the first row and each record becomes the next row.
6. Job.run() transfers the records and closes the writer, which flushes the last batch of rows. document.getSpreadsheetUrl() then returns the link to the new spreadsheet.

Console Output

Wrote to https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcdefg

The id in the URL is generated by Google and differs on every run.