Google Docs How to ? Programming tricks

How to Use Google Spreadsheet As a Database

Now Google Docs has become a very popular place to store data online. Within past few years it has gained momentum like anything. Everybody wants their data to be secure and that's what  Google Docs provide. It also gives us a very user friendly interface to deal with data. It is now fully controlled as we can set its visibility to public according to our need. Today we will learn how to use Google Sheet as a database.

How we can do it ?

Just like your database you can store large number of rows in a Google Sheet. But when you go to fetch the data, how to retrieve some selective rows from it ? Of-course we will need some query to selectively choose some rows. Fortunately Google Docs supports SQL like queries to be run on the data and get the result.

Lets take an example of a school with some students. The students have appeared the exam recently and their marks has been calculated. You want to publish their result online so that students can see their score from their home. This can be done very easily with a Google Sheet and also there is no need of any programming knowledge.

How to publish student's results online with Google Sheet

Go to Google Docs and create a spreadsheet. Store the students mark in a sheet with their name and roll number. You can see an example in the following snapshot. Then in spreadsheet go to File–>Share to change its visibility. Change it to "Anyone who has the link can view". Now you have two options to display their result. The same have been described below.
 

MarkSheet

Display their score in an iframe

Display their score in a popup Window

What needs to be modified ?

Just you need to change the key with your own key. Another point to remember is, always keep the Roll number as the first column in the spreadsheet.

7 thoughts on “How to Use Google Spreadsheet As a Database”

  1. This is some great code. However, I’m trying to use it to look up names and I can’t figure out how to get the search to accept characters/string instead of a number. Is there anyway to modify the code anywhere to change this?

      1. I know this is old post but code works well except won’t find text string only numbers. How to search text without putting your search text directly into URL i.e. still using input form to enter a search term “abcdef” and have data retrieved? Thanks

  2. Hi, great work!, i´m wondering about two things: 1. How I can start whitout show all data at open the page. And 2. How I can show the content of only one file but reorganizing all cells, for example if I need to print or expotr to a pdf, thanks for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *