How to use Google Sheets as a view on Python-generated data

Hello.

I've never written a how-to before and I'm in the mood for writing blog posts so here is a how-to telling you how to do a thing I find useful more often than you'd think.

What is the thing.

Because of a series of excellent life choices I spend a lot of my time writing Python scripts that generate data. Usually these scripts are charged with hoovering up, tidying and transforming data from a range of sources - Python is pretty great at this, but when the time comes to analyse the results I most often want to look at the data in a spreadsheet. Usually I want to do some filtering, maybe make a graph or two, and ultimately share the wisdom of the data with my peers.

Okay sounds amazing won't you show me how-to do this

All right! This is going to be great - you're only 4 or 5 simple steps away from being the successful spreadsheet samurai you always dreamt of being. Strap in, limber up your clicking fingers and let's get cracking.

1. Get ye to github

I wrote a thing that takes advantage of a couple of Python libraries: pydrive and unicodecsv to handle most of the annoying bits of this activity. Then I added on top a bit of BytesIO-wrangling to simplify uploading an in-memory list directly to Google Drive (rather having to go via an on-disk file).

The code is all on github; you can install it by typing these words into your terminal:

$ git clone https://github.com/lampholder/googleview
$ cd googleview
$ python setup.py install --user
2. Get Google credentials
  1. Go here: https://console.developers.google.com/apis/credentials
  2. Click 'Create Project':
  3. Think of a great name and give it to your project:
  4. Click 'OAuth consent screen'. Type another name into 'Product name shown to users'. Ignore everything else and click 'Save':
  5. Click the 'Create credentials' and select 'OAuth client ID':
  6. Choose 'Other', enter another name and click 'Create':
  7. Paste your 'clientID' and your 'client secret' into this delightful lump of YAML and write it to a file called 'google_credentials.yaml':
client_config_backend: settings  
client_config:  
  client_id: CLIENT_ID_GOES_HERE_THANKS
  client_secret: SECRET_GOES_HERE_PLEASE
  auth_uri: https://accounts.google.com/o/oauth2/auth
  token_uri: https://accounts.google.com/o/oauth2/token
  redirect_uri: urn:ietf:wg:oauth:2.0:oob
  revoke_uri:

save_credentials: True  
save_credentials_backend: file  
save_credentials_file: credentials.json

get_refresh_token: True

oauth_scope:  
  - https://www.googleapis.com/auth/drive.file
3. Find/make a Google drive folder you want to put stuff into automagically

Do this in the normal way; when you're done take a look at the url and make a note of the bit after /folders/. In the case of
https://drive.google.com/drive/u/0/folders/0A_8fhAlfKSOGNjc4cnpnpmh3eWs, your folder id is 0A_8fhAlfKSOGNjc4cnpnpmh3eWs

4. Do Python

I assume you're familiar with Python already; you're going to want to put something a bit like this into your script:

>>> from googleview.uploader import Uploader
>>> uploader = Uploader.from_file('google_credentials.yaml')
>>> folder_id = '0A_8fhAlfKSOGNjc4cnpnpmh3eWs'
>>> destination_filename = 'data.csv'
>>> data = [['name', 'age', 'height in metres'], ['Bob Roberts', 12, 2.0], ['张伟', 9, 3.4]]
>>> uploader.upload_csv(folder_id, desination_filename, data)

It'll make you a link to click to do the standard Google auth dance - you'll only have to do this on the first go; it'll persist the access token in a file called credentials.json. Now go check your Google Drive folder - it'll have a freshly-baked Google Sheet inside it! If this didn't happen, check out troubleshooting below.

5. Bonus step: hide the generated data behind (yet) another spreadsheet.

Your freshly-uploaded data will look bad in Google drive because all of the columns will be the same width and there will be no formatting on anything. To make things look pretty (and to stop people meddling with the data manually), you can create another spreadsheet to be the view on your data.

  1. Make a note of the URL of your generated spreadsheet
  2. Also note the letter of the rightmost column
  3. Make yourself a new spreadsheet
  4. Put =IMPORTRANGE("URL_OF GENERATED_SPREADSHEET", "generated_filename.csv!A:Z") in the top left cell of
    your new spreadsheet (where Z is letter of the rightmost column)
  5. When cell A1 changes to read #REF, click it to reveal the secret 'actually do what I want' button. Click this button.
  6. Channel your inner Llewelyn-Bowen and design the mother-loving heck out of your new spreadsheet. Feel smug knowing that:
    • only your Python script is updating the actual data, and
    • running the script again won't vape any beautiful beautiful styling.

Troubleshooting

Why isn't it working? Because Python's package-management is made of biscuits and will jump at the chance to turn to crumbs and ruin your day is why.

The important thing to check is that you're running the right version of oauth2client. If you're not, python will say something unambiguous and helpful like ImportError: cannot import name scopes_to_string.

Python gives you many tools to find out which version of a package you have; the only on worth using is pkg_resources which you can use to identify precisely which package the interpreter has found and loaded:

>>> import pkg_resources
>>> pkg_resources.get_distribution("oauth2client").version
'4.0.0'  

If you do need to murder a rogue version of the package, you can find the location of the target like so:

>>> pkg_resources.get_distribution("oauth2client").location
/Users/tom/Library/Python/2.7/lib/python/site-packages/oauth2client-4.0.0-py2.7.egg

And there you have it! A fully-formed how-to on how to do this excellent thing. I hope you enjoyed this beautiful and worthwhile blog post! Now go forth and spreadsheets.