Create Your Own APIs with Google Sheets and Google Apps Script (Part 1)

nightwolfdev - Mar 27 '23 - - Dev Community

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

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

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

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

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 email 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!

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .