A couple of days ago I decided to set up a script to run hourly on a Raspberry Pi, to test my broadband connection speed and add the results to a Google Sheet.
Scripting a test was trivial, using pyspeedtest.
Collating the results took a little more effort. I wanted to append results to a Google Sheet – the API supports this nicely using spreadsheets.values.append, but I haven’t really done any proper scripting with the Google APIs before, other than using the
gcloud utility from a shell script.
I found the Google Sheets API Python Quickstart and it was easy to
pip install the python library but there was all this fiddly-looking code dealing with authentication using OAuth2. I needed this to work from a cron script! The Getting Started guide for the Python client library listed all sorts of OAuth2 techniques, and an option using API keys which apparently can’t be used to access user data (like my sheet).
I got there eventually, and the answer turned out to be pretty simple!
- Enable the Google Sheets API for a project in the developer console (basically, step 1a from the Quickstart).
- Create a Service Account from the developer console. This gets given an identifier which looks like an email address.
- Create a Private Key for the service account, which gets you a JSON file to download and keep safe (in my case this just sits on the Raspberry Pi).
- Share the Google Sheet with the service account (using the “email address”) granting Edit access.
In the Python script, get hold of the Application Default Credentials:
from oauth2client.client import GoogleCredentials from googleapiclient import discovery credentials = GoogleCredentials.get_application_default() service = discovery.build('sheets', 'v4', credentials=credentials)
- When running the Python script, ensure that the environment variable
GOOGLE_APPLICATION_CREDENTIALSholds the path to the private key JSON file mentioned earlier.
This looks like a lot of steps, but it’s actually quite easy and certainly simpler than the setup for using an OAuth2 flow!
See more info on Application Default Credentials and how to use them from any of the client libraries.