Google Sheets Shenanigans

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.)

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.