How to use Google Spreadsheet as a database using Python

Photo: https://www.xenonstack.com/blog/google-sheets-as-a-database

Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google.

In this article we will go through how to use google spreadsheet as a database.

Table of contents

· Enable Google Drive and Google Sheet API and download credentials
· Turning Google sheets to a db.
· Connecting a python application to the spreadsheet
· Pros and Cons of using Google Sheets as a db.

Enable Google Drive and Google Sheet API and download credentials

To activate the Google drive, Google sheets API and to get the required credentials first sign in to this page. After the sign in process is completed follow the below steps.

1. On the search bar type “Create a project”
2. Click on “Create a Project”

3. A new window will open, type the name you would like to give the project. In this case I have provided “Medium Sample Project” as the project name.

4. Click on the “Create”4

5. Again on the search bar type “Google Drive API” and click on “Google Drive API”.

6. The google drive API window will open up. If it’s showing “Enable”,click on it to enable the API.

7. Again on the search bar type “Google Sheets API”

8. The google sheets API window will open up, if it’s showing “Enable”, click on it to enable the API.

9. In the next window select “Credentials”

10. Click on “Manage service accounts”

11. Click on “CREATE SERVICE ACCOUNT”

12. Provide any name for the Service account name. In this ex: have provided “Medium-service” as the service name.Proceed to click continue after providing the service name

13. The next two sections are optional, if not required just click continue and proceed till the last step and click “Done”.

14. Before moving on to the next step, please copy the email id created for the service account.

15. In the next window, click on the “⋮” icon under “Actions”, and select “Manage keys” from the drop-down list.

16. Click on “ADD KEY” and select to create a new key.

17. Select JSON as the format for the key, press “CREATE”.

18. After this you can see that a JSON file has started downloading.

We have successfully enable all the necessary API’s and created the key to access the spreadsheet

Turning Google sheets to a db.

In this section we will be creating a new spreadsheet and sharing the spreadsheet with the service account we created earlier.

1. Go to google sheets and create a new spreadsheet.

2. In the share option add the email you copied in step 14 of the last section.

3. If you have any data to add to the spreadsheet, you can proceed to add them to the spreadsheet.

Connecting a python application to the spreadsheet

In this section we will be creating a python file and connecting it to the spreadsheet created in step 1 of the earlier section. We will also go through some sample function on how to manipulate the data present in the spreadsheet.

1. Open any IDE of your choice and create a new folder(I’ll be using vs-code for this section).
2. Create a new folder and move the JSON which was downloaded in step 18 of the first section to this folder.
3. Create a new python file.
4. Create a new virtual environment and activate it.

python3 -m venv env_name 
sourve env_name/bin/activate 

5. Install pygsheets(A simple, intuitive python library to access google spreadsheets through the Google Sheets API v4.)

pip install pygsheets

6. Install pandas

pip install pandas

genv is the name of the virtual environment

7. Add the following code to your .py file and run it.

import pygsheets
import pandas as pd
gc = pygsheets.authorize(service_file='Json_file') # provide the json_file name including the .json extension
sh = gc.open_by_url("google_sheet_url") #provide the url of the g-sheet.

8. The variable sh now contains all the info related to sh.

print(sh.id) #to get the id of the spreadsheet.
print(sh.worksheets())#to get a list of all worksheets.

Output

16wchX_JnBwfKZb9r-72R1ayR68 #id of the spreadsheet
[<Worksheet 'games_sales' index:0>, <Worksheet 'worker' index:1>, <Worksheet 'thing' index:2>] #list of all the three worksheet present in the spreadsheet

9. For further functions refer here

10. To see how we can use pandas to work with the spread sheet refer here.

Pros and Cons of using Google Sheets as a db.

Pros

1. Runs in a browser, can be used on a complete foreign computer or mobile device, without installation or license hassle
2. Collaborative features, people can work on Google Sheets together — in real time
3. Saved in the cloud, nothing will get lost, accidentally (or maliciously).
4. No charges for the web storage, it is free.
5. Version control, every change is recorded with user and time data. It can be reverted to any previous state. And, there are also named versions.
6. Publishing, a sheet can be published, this way anybody who looks at the sheet (using the published link) will see the published version.
7. Chat inbuilt, again — fantastic collaboration feature.
8. Ubiquitous. You can access Google sheets, even from your phone or tablet.
9. Easy to input data. No need to write front-end code to facilitate CRUD (create, read, update, delete) of records. Just type as you go.

Cons

1. Prone to errors. A spreadsheet’s ease of use is also its weakness. Users can type willy-nilly on cells; there isn’t any validation. A database has the facility to restrict the type and format of data to be saved on the records. It’s challenging to enforce consistency of data in a spreadsheet.
2. Slow loading speed. When your data is in the order of millions, spreadsheet begins to crack. They’re not designed to handle volumes of this scale.
3. Limited data type. You can’t store BLOB data (audio, video, image, and other binary type data).
4. Relational design. You can’t avoid data redundancy in spreadsheets because you can’t normalize the worksheets the way you would normalize tables. The concept of primary keys and foreign keys are absent in a spreadsheet. A database is more structured than a spreadsheet.
5. Queries. It’s challenging to search data across sheets. With databases, querying data is a cinch. You can assemble data points in a variety of configurations using JOINS. Searching data in a database also has a significant speed advantage over spreadsheets because of the database indexes.

[1]: How to Use Google Sheets With Python (2022)

[2]: pygsheets documentation

Go from SELECT * to interview-worthy project. Get our free 5-page guide.