Reading Google Sheets from a Go program

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).