Reading Google Sheets from a Go program



I recently needed to process some data from a Google Sheet in a Go program, and was looking for the most straightforward way to do so on my local machine. This post lists some approaches that I found to work, with full source code.

To access the Sheets API, you'll need a GCP project, and would typically have the gcloud command-line tool installed. To enable the sheets API for your project, run:

$ gcloud services enable sheets.googleapis.com --project=<PROJECT-NAME>

If you want to list which APIs are already enabled, you can do:

$ gcloud services list --enabled --project=<PROJECT-NAME>

The simplest approach I found to work was using a service account. This post demonstrates this approach, as well as a (slightly) more involved approach that uses Oauth 2.0

Service account

A service account on GCP can be thought of as a virtual account, along with its own email address, attached to a project. These accounts have their own auth, permissions, etc. This is very useful for running on a VM - you typically don't want the VM to be logged in with your primary Google account, and this service account can be specific to a given VM (or a group thereof).

Start by creating a new service account on this page. Once created, select Manage Keys in the Actions menu, and add a new key. This will download a private key to your machine; keep it safe! The following program expects this key file to be provided with the -keyfile flag:

package main

import (
  "context"
  "flag"
  "fmt"
  "io/ioutil"
  "log"

  "golang.org/x/oauth2/google"
  "google.golang.org/api/option"
  "google.golang.org/api/sheets/v4"
)

func main() {
  keyFilePath := flag.String("keyfile", "", "path to the credentials file")
  flag.Parse()

  ctx := context.Background()
  credentials, err := ioutil.ReadFile(*keyFilePath)
  if err != nil {
    log.Fatal("unable to read key file:", err)
  }

  scopes := []string{
    "https://www.googleapis.com/auth/spreadsheets.readonly",
  }
  config, err := google.JWTConfigFromJSON(credentials, scopes...)
  if err != nil {
    log.Fatal("unable to create JWT configuration:", err)
  }

  srv, err := sheets.NewService(ctx, option.WithHTTPClient(config.Client(ctx)))
  if err != nil {
    log.Fatalf("unable to retrieve sheets service: %v", err)
  }

  // ...

We can specify the requested scopes (permissions) when creating an auth config. Here we're asking for read-only access to the Google Sheets.

Once auth succeeds (sheets.NewService returns w/o an error), we can use the sheets package to read and analyze the sheet; the code below simply prints the document's title and emits all the values from columns A and B in Sheet1.

  docId := "1qsNWsZuw98r9HEl01vwxCO5O1sIsI-fr0bJ4KGVvWsU"
  doc, err := srv.Spreadsheets.Get(docId).Do()
  if err != nil {
    log.Fatalf("unable to retrieve data from document: %v", err)
  }
  fmt.Printf("The title of the doc is: %s\n", doc.Properties.Title)

  val, err := srv.Spreadsheets.Values.Get(docId, "Sheet1!A:B").Do()
  if err != nil {
    log.Fatalf("unable to retrieve range from document: %v", err)
  }

  fmt.Printf("Selected major dimension=%v, range=%v\n", val.MajorDimension, val.Range)
  for _, row := range val.Values {
    fmt.Println(row)
  }
}

Note the docId passed to the sheets package; this is the path segment in your spreadsheet's URL following the /d/. In this example, I'm using a test sheet I've created.

Important: unless your sheet is world-readable, your service account won't be able to access it. Here the account's email comes in handy; you can take it from the service account's GCP IAM page (Details tab), and give this email permissions to the sheet. This way you can have the program processing a private sheet that only you have access to.

OAuth

Another way to achieve what we want is with OAuth. This also requires a bit of setup in your project's GCP console. Follow the Go quickstart docs for that. Our sample assumes you've saved the credentials.json file somewhere locally and will pass it through the -credfile flag. Unlike the quickstart, it handles all the token exchange process automatically without having to ask you to copy a code from a web page. You still have to authenticate the first time you run it, of course.

The full code of the sample is available on GitHub; while the auth part is different, the actual sheets processing code is identical to the service account sample.

For an overview of the OAuth protocol, see my earlier post.

P.S. ADC

Initially, I had trouble accessing the sheet using ADC (Application Default Credentials), but following a HN comment on this post, I was motivated to try again and it worked. I may have mixed up my auth JSON files previously, because the code is identical to what I've originally tried. In any case, the code is available on GitHub along with the other options. Depending on the exact use case, ADC may be simpler than using a service account (though IMHO the service account is a more "reliable" method across machines because its configuration is more explicit - less is happening under the hood).


Recent posts

2024.04.25: Tokens for LLMs: Byte Pair Encoding in Go
2024.04.16: Method of differences and Newton polynomials
2024.03.31: Summary of reading: January - March 2024
2024.03.06: The life of an Ollama prompt
2024.02.22: Gemma, Ollama and LangChainGo
2024.02.21: gemini-cli: Access Gemini models from the command-line
2024.01.30: Using Gemini models in Go with LangChainGo
2024.01.13: Sign in with Google in Go
2023.12.31: Summary of reading: October - December 2023
2023.12.22: Using Gemini models from Go

See Archives for a full list.