Why Use Boring Databases When You Can Use Google Sheets?

Recently I received a mail from Global Hack Week: API of MLH, which got me wondering about quarantine, where I used to have excessive time to try out new things and join so many communities. Unfortunately, I am not much of a social person and could not keep up with everything. But that doesn’t mean that I can’t still contribute to the trend.

Now anyone can use Oracle, Mongo or even Cassandra as a database, but seldom do people have the courage to use the real database, Google Sheets. I mean, think about it, it’s only the easiest accessible database, you do not need to deploy any cluster, you do not need to install anything.

Now, the first few steps to set up the API are as follows

1. Create a project on the Google Cloud Platform -> Go to cloud.google.com -> Go to console -> New Project

Creating a new project

2. After filling in the appropriate details and setting up the project enable the Google Drive and Google Sheets API because we are lazy and want to use gspread to make this sheet easier.

Enable Drive API

Enable Sheets API

3. After the APIs are enabled go to Credentials.

Go to Credentials

4. Create Credentials -> Service Account

Create Credentials

5. Fill in the details

Create Service Account

6. Go to the service account

Choose the created account

7. Go to the keys tab in the service account

Go to Keys tabs

8. Go to Add Key -> Create new Key -> Choose JSON format

Create Key

9. A JSON file will be downloaded which will be used to interact with the API.

credentials.json

Now, in order to use the google sheet with the api you need to copy the service account id from credentials.json file and add it to the sheet you want to access, if not this you can also make the sheet editable for everyone.

Provide the service account access to sheet

This would mark the end of setup of sheets api, and now let’s use gspread package and understand how to use it.

import gspread #https://docs.gspread.org/en/latest/
client = gspread.service_account(filename='credentials.json')

We need to import gspread package and initialize a client variable with the service account authorization where you’d pass the path to the credentials that was downloaded from google cloud.

Now, for those who are more eager to learn how it works

import gspread #https://docs.gspread.org/en/latest/
from oauth2client.service_account import ServiceAccountCredentials

# define credentials to access the Google Sheets API
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)

Using oauth2client library it creates a credentials which are then authorized by gspread.

This concludes the setup of libraries.

# specify the name of the spreadsheet and the sheet to write to
sheet_name = 'SheetsAPI'
worksheet_name = 'Sheet1'
# open the sheet and select the worksheet
sheet = client.open(sheet_name)
worksheet = sheet.worksheet(worksheet_name)

Now, to open a particular file use open() function to open a particular sheet and worksheet() to open a particular worksheet in file.

def getAllData():
    col = worksheet.get()
    return col

In order to fetch complete table use the .get() function.

# define the data to be inserted
data = [['John', 'Doe', 25],
['Jane', 'Doe', 30]]
# write the data to the worksheet
worksheet.insert_rows(data)

To insert multiple rows in the table use .insert_rows() function

def addCell(row,col,value):
worksheet.update_cell(row,col,value)

To edit a particular cell in the sheet use update_cell()

def returnCell(row,col):
  return worksheet.cell(int(row),int(col)).value

To return value of a particular cell use cell()

def deleteRow(row):
  worksheet.delete_row(row)

To delete a particular row use delete_row()

Now I can surely write more usages here, but if you really want to waste(utilize) your time learning how to use this robust database, kindly refer to the docs of gspread if you want to work with python, and the API docs of sheets if you want to use another language. And I guess I should have added this before, but you should check if Google Sheets is fulfilling your needs, as it does not check all the boxes for an efficient database for a large project, but I find it more than enough for smaller projects.

Check out it’s implementation in this app!