Using Google Sheets as Database in Python

Last Updated : 16 Jun, 2024

In this article we'll be discussing how we can use Google Sheets to run like a database for any Python file.

Google Spreadsheets:

Google Spreadsheets is free online web based application that resembles Microsoft Excel. You can use it to create and edit tables for various projects such as Contact List, Budget and almost everything you can imagine.

Application Program Interface:

Connectivity is a very important thing, we are all used to it to the instant connectivity that puts the world at our fingertips from desktop or mobile. So how do data travel from Point A to Point B, how different devices and applications connect with each other. An API is the messenger that takes requests and tells a system what you want to do and returns response back to you.

Google Spreadsheet API:

We can use this Google Spreadsheet as a data store and you can access this data store via an API so what we can do is we can put in our data and from your application you can access the data as a regular JSON API.

Step-by-step Approach:

So our first step is to create a spreadsheet on any of your Google accounts and give it an appropriate name, like we can name Google Sheets API Tutorial as shown below with some random entries:

For the next step visit Google Cloud Platform now a page opens like shown below :

Now click on My First Project following dialog box appears, now click on New Project

Now create your project. Now click on API & Services in the side menu bar then go to Library as shown below and search Google Drive and click on Google Drive API:

Click Enable

Now we are going to download a JSON file now which will store our credentials, so once downloaded we will go back to Library and search for Google Sheets API, Enable it, once enabled we are all set to hooking up some stuff with our code so that's it for our Google Cloud Platform, just make sure to keep track of where the JSON file is because we are actually going to have open that up now.

Now we are going to copy the client email and then go to Google Sheets we made earlier, go to share options paste that email in it and click send. This allows access to the Google sheet from our API. No we will go back to Pycharm now, and create a python file sheets.py.

Now we will go back to Pycharm now, and create a python file sheets.py. Now we are not writing any codes, yet we are going to install two packages or modules with pip so that we can actually use the API, so to do that in Pycharm we just open the inbuilt terminal, or we can open command prompt and type the below command:

pip install gspread oauth2client

Once you've done, you need to create a python script using some modules. Below is the complete program:

Python

# Import required modules
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"]



# Assign credentials ann path of style sheet
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Google Sheets API Tutorial").sheet1



# display data
data = sheet.get_all_records()
row4 = sheet.row_values(4)
col2 = sheet.col_values(2)
cell = sheet.cell(5, 2).value

print("Column 2 Data : ")
pprint(col2)
print("\nRow 4 Data : ")
pprint(row4)
print("\nCell (5,2) Data : ")
pprint(cell)
print("\nAll Records : ")
pprint(data)



# Inserting data
insertRow = [6, "Soumodeep Naskar", "Purple"]
sheet.insert_row(insertRow, 4)
print("\nAll Records after inserting new row : ")
pprint(data)



# Deleting data
sheet.delete_row(7)
print("\nAll Records after deleting row 7 : ")
pprint(data)



# Update a cell
sheet.update_cell(5, 2, "Nitin Das")
print("\nAll Records after updating cell (5,2) : ")
pprint(data)



# Display no. of rows, columns 
# and no. of rows having content
numRows = sheet.row_count
numCol = sheet.col_count
print("Number of Rows : ", numRows)
print("Number of Columns : ", numCol)
print("Number of Rows having content : ", len(data))