Using Google Sheets as database

So you’re building a simple internal app and you think “I’m gonna need a database right?” Well, not so fast. As this tweet points out, Google Sheets might be all you need.

Let’s see in two scenarios, how we can use Python or any other language to access Google Sheets.

Using Python:

To kick things off we’ll first create a project inside of Google’s API Manager. I’m going to name the project ‘Legislators’ because we’ll be working with a spreadsheet that contains the contact information for members of Congress in 2017.

Next, I’ll add the Google Drive API to this project, which will allow us to access spreadsheets inside of Google Sheets for our account.

Once that’s added, we need to create some credentials to access the API.

Since we’ll be doing it from a web server we’ll add the “Web server” option and give it access to application data not the User data..

Next, we’ll create a service account named Legislators and assign it the role Project Editor which will allow it to access and edit data within the API.

Clicking Continue this will generate a JSON file that I have renamed and add to the project as client_secret.json. You can Rename the file to anything you like and if you are an typing expert you can keep the default name.

If we open up that file in a text editor we’ll find an email address property called “client_email”.

If we copy that and take it over to our spreadsheet, we can Share the spreadsheet to that email address to give us access to the spreadsheet from the API. To share the spreadsheet with the email address click on the Share button on the top right of the screen.

Heading back to the terminal we’ll use pip to install gspread and oauth2client packages and then we’ll create a file called “spreadsheet.py” and you can open that in your favorite text editor.

pip install gspread oauth2client

The first thing we’ll do here is to import the gspread library as well as the service account credentials from oauth2client.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

Then we’ll create a scope on spreadsheets.google.com/feeds and create some credentials using that scope and the contents of client_secret.json that we looked at earlier.

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)

Then we’ll create a gspread client authorizing it using those credentials.

client = gspread.authorize(creds)

Now we can access our Google Sheets. We’ll call client.open on “Legislators 2017” and get access to Sheet 1.

sheet = client.open('Legislators 2017').sheet1

Then we’ll set our legislators equal to all of the records inside of that sheet and print them out to the terminal.

legislators = sheet.get_all_records()
print(legislators)

If it is difficult to understand what I am talking about then here is the full code of the “spreadsheet.py” file that I have been describing for a couple of lines.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

sheet = client.open('Legislators 2017').sheet1

legislators = sheet.get_all_records()
print(legislators)

Wait… If you run this code you get a ugly looking output of google sheets if you want to perfectly format it the you have to use Pretty Printer:

pp = pprint.PrettyPrinter()

I think you know how to use pretty printer to print. But some of you don’t so here’s how you can use:

pp.print(legislators)

So if you want a nice looking output here is the full modified code of the file:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

sheet = client.open('Legislators 2017').sheet1

pp = pprint.PrettyPrinter()
legislators = sheet.get_all_records()
pp.print(legislators)

Now if you run the code over to the terminal you’ll see a much nicer formatting on our results.

Since we’re going to be working with more generic results moving forward, I will change legislators to result and the first thing we’ll do is instead of getting all of the records we will get all of the values for an individual row. The row I want to look at is row 12.

result = sheet.row_values(12)

Now, what if we access the column values instead of the row values? Let’s change the code to do that and when we run it we’ll find that instead of returning a row, it returns the column of states in that particular column.

result = sheet.col_values(6)

Instead of grabbing an entire column, let grab a specific cell.

result = sheet.cell(6,11)

Now, since we are using google sheets as a databse you might think how can you update a specific cell. So, let’s see how we can:

sheet.update_cell(6, 11, 'enter_data')

Now, this is about updating existing data what about if you want to insert a new row into your database.

row = ["I'm", "Updating", "a", "spreadsheet", "from", "Python!"]
index = 3
sheet.insert_row(row, index)

Now if you head over to the google sheets you will find that you have inserted a new row into the spreadsheet at row 3.

And now let’s delete the row

sheet.delete_row(3)

Now, you will find that row 3 is deleted.

Now if you want to list the number of rows in your spreadsheet then let’s see how can we do this.

print(sheet.row_count)

That’s all you need to access Google sheets I mean use it as a database for you cool simple application. But what if you don’t know python or you want to use anyother language like javascript, PHP, java, you name it….

So as promised here it is…

Using any other language:

If you don’t like using Python or don’t know then you can use this alternative method. If you want to work with SQL like language for Google sheets without Python then also yo can use this method.

For this we will be using Blockspring to set up an API and connect to Google sheets. For this tutorial we will be using Javascript. You can download the code and modify as you want. You may rewrite the code to your prefered language.

So, before doing anything Signup for an account on Blockspring.

Before proceeding make sure that you have a Google sheet ready, that you can use for the purpose of this tutorial. For this tutorial, we will be using an example todo list that you can also find on Google sheet default examples.

Now, you just follow this below procedures to obtain your Google Sheet URL.

Obtain your Google Sheet URL :

1. First, open your Google sheet that you want to use.
2. On the top left click on File.
3. Now at the bottom, you will see Publish to the web. Click on it.
4. As soon as you click it, it will open a popup. And then click on the publish button under the Link tab and then press ok when prompted.

After clicking on the publish button copy the generated link:

Now, that you have obtained the link, it’s time to set up your API so that you can query your Google Spreadsheet from your code.

Before proceeding make sure you know Google’s Query Language. If you don’t know then make sure you read Google’s Documentation. Because you will be using this a lot.

If you are comfortable with this let’s continue for the next steps.

Paste your spreadsheet’s URL & a SQL query into Blockspring’s Query Google Spreadsheet API. This returns the results as JSON. You can test it out on Blockspring, or call the API from any language.

Now if you want to use your whole spreadsheets as a database you are done. You can now call the API from any language.

But if you want to expose specific rows and columns to the API then you can follow the below steps.

Here is a boilerplate that you can modify according to your need. You can click here it will redirect and clone the boilerplate if this link gives an error then make a new list on Blockspring by clicking here and paste the below code and add the parameters you will be using.

var blockspring = require('blockspring');

blockspring.define(function(request, response) {
    var min_cost = request.params["min_mana_cost"];
    var max_cost = request.params["max_mana_cost"];
    
    var whereClause = "WHERE K >= " + min_cost + " AND K <= " + max_cost + " ";
    
    var card_name = request.params["card_name"];
    if (card_name) {
    	whereClause += " AND E MATCHES '(?i).*" + card_name + ".*' ";    
    }
    
    var color = request.params["color"]
    if (color) {
    	whereClause += " AND I MATCHES '(?i).*" + color + ".*' ";   
    }
    
    var primary_type  = request.params["primary_type"];
    if (primary_type ) {
    	whereClause += " AND M MATCHES '(?i).*" + primary_type  + ".*' ";    
    }
    
    var sub_type = request.params["sub_type"];
    if (sub_type) {
    	whereClause += " AND N MATCHES '(?i).*" + sub_type + ".*' ";    
    }
    
    var min_power = request.params["min_power"];
    if (min_power) {
    	whereClause += " AND O > " + min_power + " ";    
    }
    
    var max_power = request.params["max_power"];
    if (max_power) {
    	whereClause += " AND O < " + max_power + " ";    
    }
    
    var min_toughness = request.params["min_toughness"];
    if (min_toughness) {
    	whereClause += " AND P > " + min_toughness + " ";    
    }
    
    var max_toughness = request.params["max_toughness"];
    if (max_toughness) {
    	whereClause += " AND P < " + max_toughness + " ";    
    }
    
    var rarity = request.params["rarity"];
    if (rarity) {
    	whereClause += " AND R CONTAINS '" + rarity + "' ";    
    }
    
    var multiverse_id = request.params["multiverse_id"];
    if (multiverse_id) {
    	whereClause += " AND B = " + multiverse_id + " ";    
    }
    
    var query = "SELECT A, B, D, E, F, G, K, I, M, N " + whereClause;
  
    blockspring.runParsed("query-google-spreadsheet", { 
        "query": query,
        "url": "https://docs.google.com/spreadsheets/d/11JYfkdJu2DNf1FLdvd4tlkzRklE1_c4lsxS_peHpXkA/edit#gid=1504627451" 
    }, { cache: true, expiry: 7200}, function(res) {
  		response.addOutput('cards', res.params.data);
        response.end();
	});
	
});

So, now to get started paste you google sheet link that you generated earlier on line 61 or from where it says this:

blockspring.runParsed("query-google-spreadsheet"), {

You will find url some what like this:

"url": "https://docs.google.com/spreadsheets/d/11JYfkdJu2DNf1FLdvd4tlkzRklE1_c4lsxS_peHpXkA/edit#gid=1504627451"

Replace it with your own Google Sheet URL. Now modify the code according to your need. Most of the code is if statements checking if specific URL parameters were supplied, and building up the WHERE clause. Simply replace these if statements with ones that are relevant to your API.

Conclusion:

I think that using python will be great for this Project but if you don’t know python or you want to use javascript or any other language instead of python then You can go with the second one.

That’s all. So, If you have faced any issues let me Know in the comment. If you are reading this post on medium then be sure to follow me.