Unable to access Google Spreadsheets with a service account credentials using Golang

Question

I am writing an AWS Lambda code for accessing and updating a spreadsheet with the URL as input using Golang. So far I am able to access the spreadsheet locally using OAuth Client ID by following the Google guide - https://developers.google.com/sheets/api/quickstart/go

But as I want to run the code from AWS Lambda, I want to perform authentication using the Google Service account. I have created a service account and have received the credentials which contain the below information.

{
"type": "service_account",
"project_id": "quickstart-1XXXXXXX806",
"private_key_id": "a1XXXXXXXXXXXXXXXXXXXXX3c3e5d8e",
"private_key": "-----BEGIN PRIVATE KEY-----\nMZ4C8......\nD\n-----END PRIVATE KEY-----\n",
"client_email": "lambda@quickstart-1XXXXXXX806.iam.gserviceaccount.com",
"client_id": "1XXXXXXXXXXXXXXXXX2",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/lambda%40quickstart-1573627656806.iam.gserviceaccount.com"

}

I have read much documentation but I didn't find any references for accessing Google spreadsheets using a Google service account using Golang code. Every document refer to the following Github link - https://github.com/googleapis/google-api-go-client But as I have recently started working in Golang, I really don't understand how to implement it. After reading the Google guide I understand the flow required for service account as below -

But I am not still able to write Golang code for this. I would appreciate if anyone could share some references:)

Rahul Satal · Accepted Answer · 2019-11-26T13:24:13.057

@ZektorH answer helped me to complete this code. Below is the complete working example for pulling data from Google spreadsheets using Service Account.

package main

import (
    "fmt"
    "log"

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


func main() {

    // Create a JWT configurations object for the Google service account
    conf := &jwt.Config{
        Email:        "lambda@quickstart-XXXXXXXXXX.iam.gserviceaccount.com",
        PrivateKey:   []byte("-----BEGIN PRIVATE KEY-----\nxxxxxx\n-----END PRIVATE KEY-----\n"),
        PrivateKeyID: "a1a6xxxxxxxxxxxxxxxxxxxxxxxe5d8e",
        TokenURL:     "https://oauth2.googleapis.com/token",
        Scopes: []string{
            "https://www.googleapis.com/auth/spreadsheets.readonly",
        },
    }

    client := conf.Client(oauth2.NoContext)

    // Create a service object for Google sheets
    srv, err := sheets.New(client)
    if err != nil {
        log.Fatalf("Unable to retrieve Sheets client: %v", err)
    }

    // Change the Spreadsheet Id with yours    
    spreadsheetId := "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"

    // Define the Sheet Name and fields to select
    readRange := "Sheet1!A2:B"

    // Pull the data from the sheet
    resp, err := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
    if err != nil {
        log.Fatalf("Unable to retrieve data from sheet: %v", err)
    }

    // Display pulled data
    if len(resp.Values) == 0 {
        fmt.Println("No data found.")
    } else {
        fmt.Println("Name, Major:")
        for _, row := range resp.Values {
            fmt.Printf("%s, %s\n", row[0], row[1])
        }
    }
}

Also, note that if you get an error as below:

googleapi: Error 403: The caller does not have permission, forbidden

then probably you have not given permission to your google account to access that spreadsheet. If so-

simply go in your browser to the Google sheet you want to interact with.
Go to SHARE on the top right of your screen.
Go to advanced settings and share it with email address of your service account ex. lambda@quickstart-XXXXXXXXXX.iam.gserviceaccount.com

Thank you for the tip on how to resolve the permission issue, this really made me crazy. Also, there is a better way to initialize the credentials as mentioned here https://stackoverflow.com/questions/39691100/golang-google-sheets-api-v4-write-update-example/58797998#58797998 — Christophe Vidal, Mar 09 '20 at 10:01

score 3 · Answer 2 · answered Nov 15 '19 at 10:53

After some digging I found some documentation that explains how to use Service Accounts on Golang. There is an example on how to perform authentication with Service Accounts on the documentation:

// Your credentials should be obtained from the Google
// Developer Console (https://console.developers.google.com).
conf := &jwt.Config{
    Email: "xxx@developer.gserviceaccount.com",
    // The contents of your RSA private key or your PEM file
    // that contains a private key.
    // If you have a p12 file instead, you
    // can use `openssl` to export the private key into a pem file.
    //
    //    $ openssl pkcs12 -in key.p12 -passin pass:notasecret -out key.pem -nodes
    //
    // The field only supports PEM containers with no passphrase.
    // The openssl command will convert p12 keys to passphrase-less PEM containers.
    PrivateKey: []byte("-----BEGIN RSA PRIVATE KEY-----..."),
    Scopes: []string{
        "https://www.googleapis.com/auth/bigquery",
        "https://www.googleapis.com/auth/blogger",
    },
    TokenURL: google.JWTTokenURL,
    // If you would like to impersonate a user, you can
    // create a transport with a subject. The following GET
    // request will be made on the behalf of user@example.com.
    // Optional.
    Subject: "user@example.com",
}
// Initiate an http.Client, the following GET request will be
// authorized and authenticated on the behalf of user@example.com.
client := conf.Client(oauth2.NoContext)
client.Get("...")

Thanks @ZektorH for sharing the example and links. After changing the Scopes parameter, It worked for me. You really saved my life. — Rahul Satal, Nov 15 '19 at 11:24

Unable to access Google Spreadsheets with a service account credentials using Golang

2 Answers2