Use Google Sheets as a Database with Python[30 Days of Blog 4]

This post is part of my 30 blogs in 30 days series. More details here.

Are you a small business with less than 20 employees? Are you using google sheets, but want to move to a more robust IT solution? Why? You can just use Google Sheets as a database for a simple CRUD(Create, Record, Update, and Delete) application. let me show you how.

The Set-up

• Go to the Google API console and create a new project. Whatever you want to name.
• You will then be taken to API library. Search for Google sheets and enable it. While you are at it also enable Google Drive API.
• On Dashboard select the Hamburger Menu and select IAM & Admin. Then select Service Account.
• Click on Create Service Account.
• Name it whatever. Copy the ‘email’.
• When done you will see a page like this. Click on the 3-dot menu and select Create Key. Select JSON.
• Save it in your desired folder as ‘client_secret.json’
• Get your desired sheet and make copy. Share with the email you copied from the service account page.
• Install Python 3.

The Code

You will need to two external libraries gspread and oauth2client.

pip install gspread oauth2client

Create a new Python script in the same folder as. Import the useful modules.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

Now assign your names:

client_key = './client_secret.json'
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

These are important stuff for OAuth 2.0 clients. Don’t bother to understand this unless you are shooting for a CS degree. The now it’s time to authorize.

cred =  ServiceAccountCredentials.from_json_keyfile_name(clientkey,scope)
client = gspread.authorize(cred)

Now you can open any workbook you want and open any sheet from that workbook using the gspread client. In this case we will be working with copy of Test.

workbook = client.open('copy of Test')

This Workbook has 3 sheets. You can access them.

#To get sheets named in the standard convention
sheet1 = workbook.sheet1

#To get sheets with custom names
gdp = workbook.worksheet('GDP')

#To get sheets all the worksheets as a list
all_worksheets = workbook.worksheets()

These would all return sheet objects. You can do various operations on these sheets according to the Google API v4. For now, we will only concern ourselves with values. The sheet GDP has all the countries of the world sorted by GDP. To get a value of a cell(say B2) we can use the following code:

#Get value of a cell
gdp.acell('B2').value

#or

gdp.cell(2,2).value

Output:

'United States'

To get values of a row or a column or the whole sheet:

#Get Values of a Row
gdp.row_values(2)
#['1', 'United States', '21,439,453']

#Get Values of a Columns
gdp.col_values(2)
#['Country', 'United States', 'China[n 2]', 'Japan',...]

#Get all the values
gdp.get_all_records()
#[['Rank', 'Country', 'GDP(in millions)'], ['1', 'United States', '21,439,453'], ['2', 'China[n 2]', '14,140,163'],...]

You can also get a fancy ‘record’ data type for your values, which is just a list of dictionaries:

gdp.get_all_records()
#[{'Rank': 1, 'Country': 'United States', 'GDP(in millions)': '21,439,453'}, {'Rank': 2, 'Country': 'China[n 2]', 'GDP(in millions)': '14,140,163'}, {'Rank': 3, 'Country': 'Japan', 'GDP(in millions)': '5,154,475'}, {'Rank': 4, 'Country': 'Germany', 'GDP(in millions)': '3,863,344'}, {'Rank': 5, 'Country': 'India', 'GDP(in millions)': '2,935,570'}...]

Using col_values and row_values can run out your API quota really fast. Luckily python have List Comprehensions:

values = gdp.get_all_values()
records = gdp.get_all_get_all_records()

#Getting a row value is simple
row2 = values[2]

#Getting a column value requires a list comprehension
column2 = [row[1] for row in values]

#or

column2 = [i['Country'] for i in records]

Can get any column or row value without requesting the Google Sheets API over and over again.

Setting the value

Getting value is well and good but how about changing a value. For that we will use Sheet1.

Suppose I see that the value for Bilbo Bagin’s cheese quantity is incorrect.

sheet1.update('C2',25)

This is update the cell C2 with the value 25.

Now I want to add another entry into the sheet:

sheet1.update('A4:D4',[['Steven Universe','Cheddar','15','At Homeworld']])

The tricky part is to always remember that the value must be a list of lists, even if the range is along a single row.

Uses

Besides automating your data-entry through a CRUD, you can:

• Use Pandas to do some serious data analysis.
• Collate all your data from other sources, like email, vendor data, customer info, etc.
• Create a basic CRM using Google Forms + Python.

And many other options. So have fun, play around and save time and maybe even some money.