During development of an application, sometimes the APIs required to gather the data are not ready. You could hard code data into your application. However, that doesn’t provide the experience a user would encounter when HTTP requests are being made to real API endpoints. Why wait? Create your own! In this article series, you’ll learn how to use Google Sheets as a database and create API endpoints with Google Apps Script to get, post, and search data!
Article Series
- Spreadsheet Setup (Part 1)
- Get Data from Sheets (Part 2)
- Create and Deploy Apps Script Web App (Part 3)
- Filter Data with Search Criteria (Part 4)
- Post Data to Sheets (Part 5)
Prerequisites
- In order to use Google Sheets, you’ll need a Google account. It’s free! You don’t need to sign up for a Gmail account. You can use your existing email account. Learn more at Create a Google Account.
- Once you have an account, create a new Google spreadsheet by visiting sheets.new.
Google Sheets as a Database
A database is a collection of tabular data made up of rows and columns. A spreadsheet is a collection of tabular data made up of rows and columns. Each sheet can be a representation of a database table. Of course a spreadsheet doesn’t have the power that a relational database would have, but for prototyping APIs, it will do just fine!
We will create a spreadsheet (database) with the following sheets (tables):
- divisions
- titles
- users
Divisions Sheet
Create a sheet called divisions with the following columns:
- id – Unique number for the division
- active – Whether the division is active (1) or inactive (0)
- name – Name for the division
Let’s populate the sheet with the following data:
| id | active | name |
|---|---|---|
| 1 | 1 | Accounting |
| 2 | 1 | Customer Service |
| 3 | 1 | Human Resources |
| 4 | 1 | Legal |
| 5 | 1 | Manufacturing |
| 6 | 1 | Marketing |
| 7 | 1 | Operations |
| 8 | 1 | Research & Development |
| 9 | 0 | Inactive Division |
Titles Sheet
Create a sheet called titles with the following columns:
- id – Unique number for the title
- active – Whether the title is active (1) or inactive (0)
- name – Name for the title
Let’s populate the sheet with the following data:
| id | active | name |
|---|---|---|
| 1 | 1 | Dev Ops |
| 2 | 1 | Full Stack Developer |
| 3 | 1 | Product Manager |
| 4 | 1 | Scrum Master |
| 5 | 1 | Tester |
| 6 | 1 | UI Designer |
| 7 | 1 | UX Designer |
| 8 | 1 | Web Designer |
| 9 | 1 | Web Developer |
| 10 | 0 | Inactive Title |
Users Sheet
Create a sheet called users with the following columns:
- id – Unique number for the user
- active – Whether the user is active (1) or inactive (0)
- first_name – First name of the user
- last_name – Last name of the user
- email – Email address of the user
- phone – Phone number of the user
- division_id – Division ID from divisions sheet for the division of the user
- title_id – Title ID from titles sheet for the title of the user
Let’s populate the sheet with the following data:
| id | active | first_name | last_name | phone | division_id | title_id | |
|---|---|---|---|---|---|---|---|
| 1 | 1 | Jayson | Afre | jafre@domain.com | 1111111111 | 8 | 9 |
| 2 | 1 | Edgar | Brickler | ebrickler@domain.com | 2222222222 | 1 | 8 |
| 3 | 1 | Suzie | Dubell | sdubell@domain.com | 3333333333 | 6 | 6 |
| 4 | 0 | Inactive | Inactive | inactive@domain.com | 4444444444 | 8 | 2 |
Next Steps?
We have created a spreadsheet (database) with sheets (tables). Next, let’s learn how to use Google Apps Script to get data from sheets we specify and restructure the data from an array of arrays to an array of objects! Continue to Part 2.
Visit our website at https://nightwolf.dev and follow us on Twitter!
Top comments (0)