Reading Google Sheets from a Go Program

[ibobev]

https://eli.thegreenplace.net/2024/reading-google-sheets-from-a-go-program/

[DazWilkin]

You can use Application Default Credentials (ADC) with Sheets:

https://cloud.google.com/docs/authentication/application-def...

You can:

  PROJECT="..."
  ACCOUNT="..."

  # Add this value to your Sheet(s)
  EMAIL="${ACCOUNT}@${PROJECT}.iam.gserviceaccount.com"

  export GOOGLE_APPLICATION_CREDENTIALS=${PWD}/${ACCOUNT}.json

And then:

  ctx := context.Background()

  scopes := []string{
    "https://www.googleapis.com/auth/spreadsheets.readonly",
  }

  opts := option.WithScopes(scopes...)
  srv, err := sheets.NewService(ctx, opts)

Remaining code remains unchanged.

One omission from your post is that you will need to enable the Sheets service|API either using gcloud:

  gcloud enable sheets.googleapis.com --project=${PROJECT}

Or by URL: https://console.cloud.google.com/apis/library/sheets.googlea...

[DazWilkin → DazWilkin]

Correction the following refers to the created key:

  ${PWD}/${ACCOUNT}.json

Possibly created by:

  gcloud iam service-accounts create ${ACCOUNT} --project=${PROJECT}
  gcloud iam service-accounts keys create ${PWD}/${ACCOUNT}.json \
  --iam-account=${EMAIL} \
  --project=${PROJECT}

Because Google Workspace does not support IAM, no IAM roles need be assigned. Instead OAuth scopes are used.

[eliben → DazWilkin]

Thanks, in your instructions it's not clear where the `$ACCOUNT.json` file is coming from?

I will mention the service enable.

[DazWilkin → eliben]

Good point! I'll revise.

[eliben → DazWilkin]

Interesting, so this approach still works through an explicitly created service account? Do you think it's possible to make it work without a GCP Service Account at all?

[zer00eyz]

This is one of those things that's going to be stuck in my head for a while. It's like the author gave me a hammer and now im going to go hunting for a nail.

It seems you can write to them as well... This seems much more magical than "Here is a link to a CSV, import the data yourself".

[rahimnathwani → zer00eyz]

You probably already knew that Google Apps Script allows your spreadsheet to run functions that can trigger HTTP requests.

Did you know that your spreadsheet can also receive HTTP requests that trigger functions?

[thornewolf → rahimnathwani]

i tried to find some resources on this but came up empty. can you provide a link? sounds like a fun workflow

[rahimnathwani → thornewolf]

https://developers.google.com/apps-script/guides/web

[notagoodidea → zer00eyz]

You are in for a whole lot of magical bad ideas :) Also Google Sheets are "vulnerable" to CSV injection [1] too for "here is a link to an evil CSV, import the data yourself" scenario.

Honestly if there was a way to Auth (even basic Auth or Bearer) via classic Google Sheets, you could create a monster so easily.

[1] http://georgemauer.net/2017/10/07/csv-injection.html

[zer00eyz → notagoodidea]

CSV injection is an OLD trick! It's one I learned 20 years ago from someone who, was at the time, older than I am now.

> Honestly if there was a way to Auth (even basic Auth or Bearer) via classic Google Sheets, you could create a monster so easily.

Excel/sheets is an IDE for accountants. They make monsters over there all the time!

[bbkane]

Yes! I'm using a similar technique to write to Google sheets in https://github.com/bbkane/starghaze/

Account setup instructions at https://github.com/bbkane/starghaze/blob/master/dev_notes.md...