Using Google Sheets from Python

The API for Google’s spreadsheets has seen a few revisions now, so there’s a ton of stale information floating around. This post is not a step-by-step tutorial, but it is meant to point to what’s working well for me right now, in Feb 2020.

Libraries

Three libraries are bearing most of the load for me:

  • gspread - A reasonable abstraction over google’s REST API for v4. Works well for opening, editing and creating sheets. It’s particularly nice that opening by name works well. It’s going to work best if you use a service account; I couldn’t find any path to get it working using the interactive OAUTH path. That’s a little unfortunate, because it means some time messing around on the GCP dashboard before you’ll find your way to getting started.

  • gspread-formatting - Works with gspread and lets you control many formatting details that are inaccessible using the default gspread apis. My formatting needs are very rudimentary, and this makes it easy in conjunction with gspread. It was hard to find for some reason, though; I finally noticed it linked in some github issue for gspread. Just making it so I can find this again was about 60% of the motivation for writing this post.

  • oauth2client - This is now deprecated, but it’s integrated with gspread. I’m going to wait for a gspread update to change this out.

Basic workflow

  1. Get API keys - Set up a GCP project with a service account. Enable the drive and spreadsheets APIs. Get a key for the service account. Note the phony email address that goes with that key.

  2. Share a drive folder - Create a folder on the google drive you want to modify. Share that folder with the phony email address that goes with the key. If you’re going to create new sheets programmatically, you’ll have to share those sheets with your own google account to manipulate them.

  3. Connect

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# authorization scopes necessary to edit google spreadsheets
oauth2_scopes = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credfile = '/path/to/downloaded/credential.json'
credentials = ServiceAccountCredentials.from_json_keyfile_name(credfile, oauth2_scopes)
session = gspread.authorize(credentials)
workbook = session.open('Document Name')
  1. Search
gws = workbook.worksheet('Worksheet Name')
search_term = 'Something To Search For'
cell = gws.find(re.compile(search_term, re.IGNORECASE))
print(f'Found {search_term} at ({cell.row},{cell.col})')

This is probably worth a more detailed tutorial some day, but I’m just hoping it points future-me or whomever stumbles accross this while searching in the right direction.