Using Google Sheets With Python

If you are looking for a lightweight, easy to use and cheap database for small hobby projects google sheets might be the thing for you. This tutorial will show you how to use google sheets with python as a simple database.

Setup

There are a few steps that need to be followed to start using the google sheets API.

– Create a project on google cloud console
– Activate the google drive API
– Create credentials for the google drive API
– Activate the google sheets API
– Install a few modules with pip
– Open the downloaded json file and get the client email
– Share the desired sheet with that email
– Connect to the google sheet via python code

Creating a New Project on Google Cloud

Go to the following link and create a new project https://console.cloud.google.com/.

Watch the video for detailed instructions…

Adding the API’s

Now we need to add the following API’s :

– google drive
– google sheets

Once you add the google drive API it will ask you to create credentials. Follow the steps and you should see a JSON file gets downloaded. SAVE THIS FILE.

Sharing the Sheet

Open up the json file that was downloaded earlier and find the client email. Copy the email and share your google sheet with that email address.

Install Modules via pip

Then next step is to install the following modules using pip.

– gspread
– oauth2client

This can be done by running the following command in cmd: pip install gspread oauth2client

Python Script

Now all you how to do is create a python script and place the JSON file downloaded earlier in the same directory as it.

Source Code

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint

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("creds.json", scope)

client = gspread.authorize(creds)

sheet = client.open("tutorial").sheet1  # Open the spreadhseet

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