May 31, 2024 at 18:07 Tags Go , Internet , Network Programming
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. Using gcloud 's ADC didn't work for me because it typically doesn't have access to sheets or docs.
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
" , 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
" , 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.