As I build more back-office web interfaces I notice that users feel most comfortable in an Excel-like interface. That’s why it’s now so common to find data being edited and exchanged Google Sheets.
This got me wondering – how do I access, manipulate and write to Sheets from Python. I like the answer that I found – a library called gspread.
Basic usage
A high-level overview of how you use it:
- Create a service account in Google Developer Console
- Share your Google Sheet to that special email address
- You can now access it from python with gspread and related libraries
Making it even better with Pandas
While it’s great to have low-level access in Python it’s much more convenient if I can manipulate the data inside Pandas DataFrame. That way I don’t have to think about data structures and how to correctly represent data in each cell or row.
To do this I found two libraries:
They’re both similar and you essentially just import a snippet:
For a larger project, I used gspread-pandas (just because I found it first) and it gives you quite a lot of control over how and when you update the data.
Sheets and Data Validation
Sheets allow you to define data validation checks on specific cells. You can validate against a predefined list of items or reference a range of cells. This allowed me to build an elaborate export of data from an API and provide users a way to quickly review the data and also possibly use Sheets to update the data.
To manipulate Sheets data validation from Python you can use gspread-formatting library.
Video and Slides of Talk on this topic
If you’d like to see slides from my talk on this Subject at the Python Ljubljana meetup group I’ve embedded them below.
Was this useful for you?
If this is useful to you, please leave a comment or send me an email. I’ll be happy to write a more detailed tutorials and support you.