Google Sheets: A Database?

Made by Canva

What if I tell you there is a database that is,

1. 100% Free

2. Has a world-class admin UI

3. And almost everybody in the world knows how to use it.

Believe me or not but Google Sheets can be used as a lite weight database. And in today’s article, we’ll learn how to integrate it using Python3. Without westing further ado, let’s get started.

Article Summary

1. Create Google Sheets
2. Create Google Service Account
3. Get All Records From Google Sheets Using Python
4. CRUD Operations
5. Conclusion

Create Google Sheets

We are having the data of an E-commerce website Zalando.uk, stored the data into a spreadsheet and named it “Zalando Data”. You can access the data here.

Create Google Service Account

In order to interact with this spreadsheet using python, we will have to create a Service Account. It is a special type of account that is used to make authorized API calls to Google Cloud Services. The interesting part here is you can even create it without spending a penny. Let’s follow the following steps and get the credential ready.

Step:1

Go to Google Cloud Platform, and create a new project.

Once it is created, you’ll see a notification that gives the confirmation of our new project.

Yes, I agree. I did that typo while naming the project. It should be “ZalandoData” instead of “ZlandoData”.

Step 2:

Now, that our project is created we need to enable the APIs that we require to make CRUD operation to the database. Oops! “to the google sheets”.

we will add two APIs for our project i.e “ZlandoData”.

• Google Drive API
• Google Sheets API

GCP provides an API library to enable and manage the APIs for the projects.
All we need is it enables the Drive API and get the credentials, we will use it later in this tutorial.

1. Enable the Drive API

2. Save the service account credentials

Once the Drive API is enabled, we can create the service account credentials by clicking on the “+Create Credentials” option under the Credential menu.

Under the service account, details put your name, which will reflect on the credential file. Select Editor or Owner access to add credentials to your project. Once your service account is ready, download the key credentials in JSON format.

Now, a JSON file will be downloaded which contains the keys to access the API. For a naming convention, rename it as Cred.json. Great! our google service account is ready to use.

3. Enable the Sheets API

Similarly, enable the Sheets API.

We have already created the service account so we don’t need to create credentials again.

Step:3

To allow access to our Google Sheets from our API we will share the sheets with the client_email which is present inside our Cred.json file.

Get All Records From Google Sheets using Python

Now, we are ready to code and access the sheets using python.

It’s always a good practice to create a virtual environment. Let’s create a virtual environment and install gspread and oauth2client packages using PIP so that we can use the API.

$ pip install gspread oauth2client

The API provides all the functionalities to achieve the sheet as a database. Let’s write an app.py that can print all the data present on our spreadsheet.

Details about the code above:

1. scope: Scopes are the predefined dependencies that provide easy and secure access to our API.
2. Credentials: we can pass the credential JSON which we have downloaded from the GCP project.
3. client.open(“Zalando Data”).worksheet(“zalando_data”): Here “Zalando Data” is our Google Spreadsheet name where zalando_data is the sheet name under that spreadsheet.
4. get_all_records(): This method gives output as all the records present inside the sheet in the list of dictionary format.

Let’s check the output:

CRUD Operations:

CRUD! Create, Read, Update, and Delete are the four basic operations of persistent storage. Let’s perform is CRUD operation on our Google Sheet.

Create:

To insert a row into our google sheets we can use the insert_row() functionality. Let’s insert a new row on row number 8.

# Insert the list as a row at index 8
insertRow = [“GA111A28J-A11”,”Gabor”,”Trainers — weiß/ice”,”https://img01.ztat.net/article/spp-media-p1/bc1c433d1649346e9eb02d316961bfc9/dd8d3ad466d64631b956405046eb9ca1.jpg","£89.99","https://www.zalando.co.uk/gabor-trainers-weissice-ga111a28j-a11.html"]
sheet.insert_row(insertRow, 8)

As you can see, we have inserted a new row on the 8th index. But the cool part is it didn’t overwrite. It creates a completely new row on the 8th index and pushes everything down.

Read:

As we can access the sheet correctly let’s see how we can print the exact row and column by calling its number.

1. Call by row:

Get a specific row, we can use the row_values() and pass the row number.

# Get a specific row
row = sheet.row_values(3)
pprint(row)

2. Call by Column:

Similarly, we can use the col_values() function and get the column value by passing the column number.

# Get a specific column
col = sheet.col_values(3)
pprint(col)

Update:

We can update a row or column value by simply using the update_cell method. Let’s update the “Brand Name” of the 8th row from “Gabor” to “ADIDAS”.

# Update one cell
sheet.update_cell(8, 2, “ADIDAS”)

The first parameter takes the row number where the second parameter takes the column number.

Delete:

To delete a row we can use the delete_row method by calling the row number. Let’s delete the 8th row from our sheet.

# Delete the row
sheet.delete_row(8)

That’s amazing, right!

Conclusion:

Great! you added a new database skill under your belt. There are a few caveats before you use it as a production database.

1. Google Sheets was never built for a database, so this does not support Join operations or complex queries.
2. Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user.

But even with these limitations, Google Sheets can be the best choice for prototyping your project. And most importantly it’s super easy to use.

Thank you for reading!

Follow me on Medium for the latest updates. 😃