Google Sheets Shenanigans

Jan 14, 2019 06:04 · 361 words · 2 minute read Data Google APIs

Google Sheets Shenanigans

When I was a tiny little wee man

It used to be that it’d be pretty easy to make a small script that would connect to a Google Sheet, read data, change data back, all that jazz.

Nowadays, it’s a bit more complicated; you have to have a Google project, and then either a service account or some OAuth thing.

But I need to do a thing!

I have this project that’s coming down my pipeline, where a user already is using Google Sheets to do some things. There’s a lot of manual steps involved and the client feels they could get a good boost in performance if they automated a few of the steps.

Problem is, software engineering is costly, and they probably don’t really want to inject a lot of money in there. Similarly, I don’t want to be changing all of their workflow by pushing some big piece of software on top.

In other words, I want to get to their Sheet and do stuff to and with their Sheet, but otherwise their workflows should remain the same, except for the parts that I’ll be automating.

HOW.

Turns out you can use a Google Service Account to touch Google Things, if you share them to the Service Account.

1- Save Service Account credentials file, and load it.

In go, you’ll end up with something like this to build your client (error management elided for the sake of terseness. Please handle your errors responsibly.)

b, _ := ioutil.ReadFile("credentials.json")
config, _ := google.JWTConfigFromJSON(
	b,
	"https://www.googleapis.com/auth/spreadsheets.readonly",
	"https://www.googleapis.com/auth/drive",
)
client := config.Client(oauth2.NoContext)
srv, err := sheets.New(client)

2- Share sheet with Service Account

Your Service Account credentials file will contain an email address looking like this: the-name-of-the-account-12345@your-project-name.iam.gserviceaccount.com. You need to share the sheet to that address, probably with Edit permission if you want it to work.

Sharing sheet

3- Use it.

Yup. There’s no step 4, you’re good to go.

spreadsheetId := "yourspreadsheetID"
readRange := "YourSheet!A2:E"
resp, _ := srv.Spreadsheets.Values.Get(spreadsheetId, readRange).Do()
if len(resp.Values) == 0 {
	fmt.Println("No data found.")
} else {
	data := make([][]interface{}, 0)
	for _, row := range resp.Values {
		fmt.Println(row)
	}
}

There you go

Sheet responsibly :)