A survey of using Google Sheets API v4

[wangkuiyi]

Because my most familiar language is Go, so I conducted my experiments using the Go client library.

Install Go

If you use Mac, you can install Go using Homebrew. Or, follow the official installation guide.

Configure the IDE

If you use Emacs, please follow Helin’s configuration.

Create a Google Account

To access Google API, you need a Gmail account.

Write a Google API Application

Before you can write an application program that access your Google data, you need to register the application so to get a token to identify it. Then, you can write and run your application. Please follow the Google API tutorial for both steps.

Google Sheets API v4

The most recent version of Sheets API is v4.

The Go binding is at https://github.com/google/google-api-go-client/blob/master/sheets/v4/sheets-gen.go. The source code is automatically generated from the RESTful format of the API. I use the GoDoc page to understand important services:

• Spreadsheets
• Spreadsheets.DeveloperMetadata
• Spreadsheets.Sheets
• Spreadsheets.Values

Range

From the quick start example program, you might noticed that we need to specify the range in a sheet to read from or to write to. There is no official documentation about it, but we have a version from trial-n-err: https://productforums.google.com/forum/#!topic/docs/8w9TzS7JEQI.

[wangkuiyi]

Done:

I successfully ran the demo program in the Google official Sheets API document.

I successfully adapted the above program to work with a sheet I created manually.

Experiences:

1. We'd like to "freeze" the first row by selecting a cell on the first row and select the menu "Edit/Freeze/1 row".
2. We'd have a column "Date & Time" so that we can sort all Git commits by time.

Problems:

1. There isn't a "search" method in Sheets API. So we might have to download and cache the whole spreadsheet every time.

2. We need to maintain a mapping from the column to KPI. And we need to make sure that the operation of adding a KPI is atomic. I feel that this can be done by creating a new "page", or sheet, in the terminology of Google Sheets, where each row contains two columns, the KPI name and the column name. For example, the example in our design doc needs two sheets:

   1. The mapping sheet looks like

      	A	B
      1	KPI 1 (Precision)	D
      2	KPI 2 (Recall)	E

   2. The data sheet looks like

      	A	B	C	D	E
      1	Git commit SHA	Date & Time	Status	KPI 1 (Precision)	KPI 2 (Recall)
      2	342eacff3	2018-01-01	Done	50.1%	44.3%