Google Sheets and Python

Nowadays Google Docs has been integrating a whole bunch of features with its amazing API that you can use from triggering a Python script hosted in a local server by a request from IFTTT webhooks for example, which is the final project of this series of posts about my self-education in python and a whole bunch of boring stuff that I love.

Over here I will not explain about basics of Python or the project mentioned before, here I will just remind my self and hopefully others about creating your JSON file that I will use in my python script, for access to Google sheets and update a specific cell, necessary to have a sheet created for this project.

I will assume that we have a Google console already set up if you don’t have one, why are you reading this post?.

Creating a New Project on Google Cloud

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Sheets API.
  4. Click Enable API. Search for and enable the Google Drive API.
  5. Create credentials for a Web Server to access Application Data.
  6. Name the service account and grant it a Project Role of Editor.
  7. Download the JSON file.
  8. Copy the JSON file to your code directory and rename it to creds.json

If you need more details check the video below.

Sharing the Sheet

  • find the  client_email inside. Back
  • In your spreadsheet, click the Share button in the top right, and
  • paste the client email into the People field to give it edit rights.
  • Hit Send.

If you skip this step, you’ll get a gspread.exceptions.SpreadsheetNotFound error when you try to access the sheet from Python.

Read Data from a Gsheet with Python

This is the easy part now that we have the code from the Sheet URL, which we will use in our python code.

  1. oauth2client – to authorize with the Google Drive API
  2. gspread – to interact with Google Spreadsheets

Install Modules via pip

pip install gspread oauth2client

Then paste this code into a new file called spreadsheet.py:

import gspread
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",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
#I have the JSON File saved in the same directory as this script.
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("wanatux test").sheet1
#For some reason this only works in sheet1, you wont be able to use another one as far as today 7/29/2020
# Extract and print all of the values
list_of_hashes = sheet.get_all_records()
print(list_of_hashes)

data = sheet.get_all_records() # Get a list of all records

row = sheet.row_values(3) # Get a specific row
col = sheet.col_values(3) # Get a specific column
cell = sheet.cell(1,2).value # Get the value of a specific cell

insertRow = ["hello", 5, "red", "blue"]
sheet.add_rows(insertRow, 4) # Insert the list as a row at index 4

sheet.update_cell(2,2, "CHANGED") # Update one cell

numRows = sheet.row_count # Get the number of rows in the sheet

Run python spreadsheet.py and you will be able to see the data or manipulate the sheet, above you can some commands for testing..

Insert, Update, and Delete from a Spreadsheet with Python

We’ve just scratched the surface of gspreads’ well documented and comprehensive functionality. 

For instance, we extracted the data into a list of hashes, but you can get a list of lists if you’d prefer:

sheet.get_all_values()

Or you could just pull the data from a single row, column, or cell:

sheet.row_values(1)

sheet.col_values(1)

sheet.cell(1, 1).value

You can write to 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:

sheet.delete_row(1)

And find out the total number of rows:

sheet.row_count

Check the gspread API reference for the full details on these functions along with a few dozen others. 

Leave a Reply