How to use Google Sheets as a database

How to use Google Sheets as a database

本篇中文版:如何用Google Excel當作資料庫

Resources used in this article

The previous article, Fully customizable Google Forms, is about how to use Google Forms as a web form and how to send data to Google Sheets.

In this article, I will write how to use Google Sheets as a database, and I will create a simple member list page to demonstrate.

These tools are used in this article:


Create a Google Sheet

On the google drive, press New, select Google Sheets and you will be taken to a new google sheet.

The data here comes from unnames.com, I chose four fields:

  • name
  • thumbnail
  • email
  • birthday

The following are fake members information compiled:

sample memeber data
sample memeber data

Publish Google Sheet to the web

This is the easiest and most important step, only the published google sheet will let us AJAX.

First, click on File in the top left corner, and you’ll see the Publish to the web option:

File -> Publish to the web
File -> Publish to the web

After clicking Publish to the web, a modal will appear asking for the scope of the release.

select the scope of the release
select the scope of the release

Because this sheet has only one sheet, so I select Entire Document.

Then click Publish and you will see the modal of successful publishing.

publish success
publish success

Create a member list page

The next step is create a page to display the data we catch from the google sheet.

Finally I create a page like this:

demo1 not render data yet
demo1 not render data yet

We have page and the data, all we have to do is AJAX google sheet and render the data on the page.


AJAX Google Sheet data

In fact, when we publish the google sheet, it’s easy to AJAX the data. All we need is the request URL.

The structure of the request google sheet url is like this:

https://spreadsheets.google.com/feeds/list/{excel_id}/{sheet}/public/values?alt=json

There are two elements that should be replaced here: excel_idsheet.

The sheet is … yeah, the sheet like excel sheet. We can only GET one at a time.

The excel_id is depending on the url, the google sheet url looks like this:

https://docs.google.com/spreadsheets/d/{excel_id}/edit#gid=0

So, the URL for my demo google sheet like this is:

https://docs.google.com/spreadsheets/d/1IrmKHvX6tQ8zgzPC3ggD3UPu1-hQrE29nfHRs40wUJQ/edit#gid=0

The excel_id is: 1IrmKHvX6tQ8zgzPC3ggD3UPu1-hQrE29nfHRs40wUJQ

After we put the excel id, sheet into the URL, we can get this:

https://spreadsheets.google.com/feeds/list/1IrmKHvX6tQ8zgzPC3ggD3UPu1-hQrE29nfHRs40wUJQ/1/public/values?alt=json

Finaly, we use the url to AJAX. I am using jQuery to handle GET method:

$.get('https://spreadsheets.google.com/feeds/list/1IrmKHvX6tQ8zgzPC3ggD3UPu1-hQrE29nfHRs40wUJQ/1/public/values?alt=json', function(data) {
  console.log(data);
});

The result of console.log:

original data
original data

The next step is to organize the data and use the for loop to render the data:

I use console.table to convert an array to a table:

result of data
result of data

Finally, the data is rendered by using for loop into the page. The result is as follows:

findal page
findal page

I don’t know why, it looks like a dating web sites. XD~

Here is the source code used in this article. Welcome to use:

https://github.com/auguston/letswrite-google-excel-db-en


If you find this article helpful, please click on the helpful button made by myself. If you are willing to share on the social, that’s even better.

Summary
How to use Google Sheets as a database
Article Name
How to use Google Sheets as a database
Description
In this article, I will write how to use Google Sheets as a database, and I will create a simple member list page to demonstrate.
Author
Publisher Name
Let's Write
Publisher Logo

Leave a Reply

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