How to read and write to a google spreadsheet from heroku using python

Hi all!  This post isn’t about scholcomm or open science so won’t be of interest to most people who used to read this blog back in the day, but I’d kinda like to get back into blogging more and I’m deciding the way to do that is just start blogging more to get the habit back.  Fingers crossed.  :)

Also, all the techies among us have benefitted enormously from others who write posts about their “been there, done this, it worked for me” so I’d like to start giving back a bit more on that front!

Here’s how to get python to read and write from a google spreadsheet, storing the creds in an environment variable so you can run it from heroku for example.

First, read this great post by GREG BAUGUES at Twillo because this solution is just a minor modification to his great instructions.  Go enable the API as he describes, get your credentials file, and don’t forget to go to your google spreadsheet and give permissions to the generated email address as he directs.

Then, set up an environment variable with the value of the contents of the credential file you created, in single quotes, like:

heroku config:add GOOGLE_SHEETS_CREDS_JSON='{
  "type": "service_account",
  "project_id": "my-project",
  "private_key_id": "slkdfjlksdfljksdf",
  "private_key": "-----BEGIN PRIVATE KEY-----\nlsdjflskjdfljsdjfls
...

}
'

 

Then use the code in this gist, replacing the url with the url of your spreadsheet.

	import os
	import json
	import gspread
	from oauth2client.service_account import ServiceAccountCredentials
	# based on https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html
	# read that file for how to generate the creds and how to use gspread to read and write to the spreadsheet
	# use creds to create a client to interact with the Google Drive API
	scopes = ['https://spreadsheets.google.com/feeds'%5D
	json_creds = os.getenv("GOOGLE_SHEETS_CREDS_JSON")
	creds_dict = json.loads(json_creds)
	creds_dict["private_key"] = creds_dict["private_key"].replace("\\\\n", "\n")
	creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scopes)
	client = gspread.authorize(creds)
	# Find a workbook by url
	spreadsheet = client.open_by_url("https://docs.google.com/spreadsheets/d/1RcQuetbKVYRRf0GhGZQi38okY8gT1cPUs6l3RM94yQo/edit#gid=704459328")
	sheet = spreadsheet.sheet1
	# Extract and print all of the values
	rows = sheet.get_all_records()
	print(rows)

view raw

google_sheets_from_heroku.py

hosted with ❤ by GitHub

Add gspread and oauth2client to your requirements.txt, push it all to github, and it should work!  You can test it with heroku run python google_sheets_from_heroku.py… it should print out the contents of the first sheet of your spreadsheet.

I got it working a day or two ago so the details are a bit hazy now, but I think at some point I was prompted to go to https://console.developers.google.com/ and also give permissions for the “Google Sheets API” in addition to the “Google Drive API” so heads up to try that if you run into any snags.

Anyway, hope that helps somebody!

Long live blogging!