Google Apps Spreadsheet API -Google and Python Spreadsheets
The Google Drive API and Service Accounts
At the risk of being the Obvious Captain, you are going to need a spreadsheet if you want to move on with this post. If you don’t have one on hand that is full of good data, can I suggest that you make a copy of this spreadsheet with contact information for all lawmakers in the United States? (Side note: Ian Webster uses this data together with Twilio in order to make it easier for citizens to call the congress).
To access the spreadsheet programmatically, you will need to create an OAuth2 service and credentials account from the Google API console. If you have been traumatized by developing in OAuth2 before, do not worry; Service accounts are the easiest way to use it.
Continue to follow the steps and the GIF below. You can enter and exit the console in 60 seconds (much like Nick Cage in your favorite Nick Cage movie).
Go to the Google API Console.
Create a new project.
Click on Enable API. Search and activate the Google Drive API.
Create credentials for a Web Server to access Application Data.
Name the service account and grant Project Editor Paper.
Download the JSON file.
Copy the JSON file to your code directory and rename it: client_secret.json
There is one last step necessary to authorize the application, and it is easy to overlook it!
Find the client_email inside client_secret.json. Back in the spreadsheet, click on the Share button in the upper right, and paste the email client in the People field to give you editing rights. Press send.
If you skip this step, you will get an error of: gspread.exceptions.SpreadsheetNotFound when you try to access the spreadsheet from Python.
We are done with the boring part! Now we go with the code.
Read Data from a Spreadsheet with Phyton
With credentials in place (you copied them into the code directory, right?), Accessing a Google spreadsheet in Python requires only two packages:
Google Apps Spreadsheet API
oauth2client- to authorize with the Google Drive API using OAuth 2.0
gspread- to interact with the Google Spreadsheet
Install these packages with:
pip install gspread oauth2client
Then paste this code into a new file called spreadsheet.py:
from oauth2client.service_account import ServiceAccountCredentials
# use creds to create a client to interact with the Google Drive API
scope = [‘https://spreadsheets.google.com/feeds’]
creds = ServiceAccountCredentials.from_json_keyfile_name (‘client_secret.json’, scope)
client = gspread.authorize (creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open (“Copy of Legislators 2017”). sheet1
# Extract and print all of the values
list_of_hashes = sheet.get_all_records ()
Run Phyton spreadsheet.py and marvel at glorious and well-formatted data.
Insert, Update and Delete from a spreadsheet with Python
We have only scratched the surface of the well-documented and complete functionality of gspreads.
For example, you extracted the data in a hash list, but you can get a list of the lists if you prefer:
Or you can simply extract data from a single row, column or cell:
sheet.cell (1, 1) .value
You can write in the spreadsheet by changing a specific cell:
sheet.update_cell (1, 1, “I just wrote to a spreadsheet using Python!”)
Or you can insert a row in the spreadsheet:
row = [“I’m”, “inserting”, “a”, “row”, “into”, “a,”, “Spreadsheet”, “with”, “Python”]
index = 1
sheet.insert_row (row, index)
You can also delete a row from the spreadsheet:
And find out the total number of rows:
Check the gspread API reference for full details on these
functions, along with a few dozen others.
Using Google spreadsheets with Python opens up possibilities such as building a Flask application with a spreadsheet as the persistence layer, or importing data from a spreadsheet in Google to Jupyter Notebooks and doing pandas analysis. If you want to start using Python and Twilio, visit our Quick Start with Python site.
Gallery of Google Apps Spreadsheet API
( Click Image to Enlarge )
- Free Spreadsheet Templates For Bills
- Free Spreadsheets For Ipad
- Free Spreadsheet App For Android
- Free Rental Property Spreadsheet
- Free Online Spreadsheet Software