Google Sheets with Python 👏 (live demo)
TL;DR: You can interact with Google Spreadsheets (read, write, etc) from Python in a super simple way by using https://github.com/burnash/gspread Python library. Want to jump right into code? Give it a try yourself! 👇
At RMOTR School we have many students that want to automate boring Excel tasks using Python. If you host your Sheets on Google Drive, you will see how easy it is to interact with them. With just a few lines of code you will be able to read all the data from your Sheets, insert new rows, update cells, and more.
Getting started 👊
Live Demo linked above works with a copy of the Legislators 2017 public Spreadsheet, which contains a big variety of data types, columns and rows.
To be able to fully interact with the Spreadsheet, you will need to get your own copy of the “Legislators 2017" Sheet and give it the proper access permissions to connect from Python.
To see how to get the credentials JSON file, take a look at the very nice Twilio’s Blog Post, which explains it in details.
Connecting the Spreadsheet 🔌
To authenticate and access Google Sheets you will need to install these two Python libraries:
- gspread: https://github.com/burnash/gspread
- oauth2client: https://github.com/googleapis/oauth2client
(they are already installed in the live demo, so you don’t need to worry about them) 😉
Once you are set up, connecting to the Sheet is as simple as specifying the credentials JSON file, plus the Spreadsheet name.
Time to play! 🎉
Now you are free to do anything you want in your Spreadsheet. Actions like reading cell content, inserting or deleting rows or columns, and even sharing the Sheet with other users are really simply to do with this library.
See the whole list of actions you can perform on your Spreadsheets in the gspread documentation:
https://gspread.readthedocs.io/en/latest/
Happy coding! 🍰
