2

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": "[email protected]", "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 - enter image description here

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

Improve this question

2 Answers 2

Reset to default
10

@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: "[email protected]", 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. [email protected]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 stackoverflow.com/questions/39691100/…
3

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: "[email protected]", // 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 [email protected]. // Optional. Subject: "[email protected]", } // Initiate an http.Client, the following GET request will be // authorized and authenticated on the behalf of [email protected]. client := conf.Client(oauth2.NoContext) client.Get("...")
Improve this answer

1 Comment

Thanks @ZektorH for sharing the example and links. After changing the Scopes parameter, It worked for me. You really saved my life.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.