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:

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:

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:

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!