I'm doing a very cool project for OpenIssue, LLC, a company I have been connected to for a number of years. This project involves a lot of API work, including Salesforce, varied database systems, and lots of AWS resources. I expect during the course of this project to get to work with APIs from a very wide variety of services, from fundraising systems, to eventually even Twitter and Slack.

We decided we needed to connect to Google Sheets for a particular part of the project. I came across the python project gspread when I was googling around, and found this well written article about using gspread to read and write to google spreadsheets.

So we implemented this project, and it worked well, except it was extremely slow to write (reading was fast, but writing was slow.) I did some research, and found that the google API version 4 (the most recent one) is much faster than v3, which is what gspread is based on.

So we switched to pygsheets, because we couldn't wait for gspread to get updated. Pygsheets is newer, and has fewer contributors, but is updated more frequently, and supports the latest API.

It worked really well - we didn't run into any issues with pygsheets, and the write back to a spreadsheet is super fast.

It's just as easy to implement pygsheets. Create a project and get credentials in the same way as outlined in the article above, or follow these instructions.

Initialize the connection:

gc = pygsheets.authorize(service_file='./client_secret.json')

How to read a sheet:

sheet = gc.open(kwargs['spreadsheet_title'])
wks = sheet.worksheet_by_title(kwargs['worksheet'])
all_records = wks.get_all_records()

This returns a nice and easy to use list of dicts, with the keys being the column headers. One thing is you can't write to a sheet using a dict or list of dicts, you have to convert to a list with the right order, and insert the keys as a list first before the rest of the data.

Anyway, it's great to be able to read and write to Google sheets via python easily.