How to use Google Sheets as a database

2019-08-22 20:26

本篇中文版：如何用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:

• database: Google Sheets
• data sample: uinames.com
• css framework: Skeleton
• source code: Github

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:

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:

After clicking Publish to the web, a modal will appear asking for 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.

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:

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:

1

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

There are two elements that should be replaced here: excel_id, sheet.

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:

1

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

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

1

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:

1

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:

1
2
3

fetch('https://spreadsheets.google.com/feeds/list/1IrmKHvX6tQ8zgzPC3ggD3UPu1-hQrE29nfHRs40wUJQ/1/public/values?alt=json')
  .then(res => res.json)
  .then(res => console.log(res))

The result of console.log:

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:

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

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/letswritetw/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.

Category： google

Tags