How to use Google Spreadsheet as Admin Interface for your Firebase project

Manage, verify and approve transactions in your application using cloud functions and Google apps script.

In any setting where the development approach is deadline-driven, with busy product backlog, the aim is usually to have the user-facing app and backend built really fast in order for the product to quickly hit the market and gain traction in a bid to get an edge over competitors, shorten the time to scale, or gain some numbers for potential investors.

This is typical of lean startups but is not the case for many big companies that take their time and rely on well-tested processes and robust teams with experts and skilled personnel.

The focus here is on how to leverage Google Spreadsheet to handle admin operations, while focusing developer resources on user-facing features and backend development. This can be a big process gain for small teams with big targets and tight deadlines.

Google Spreadsheet is an online spreadsheet app that lets users create and manage spreadsheets. It has an API that allows codes to access and perform CRUD operations on spreadsheets just like any other user, but this is 1-way communication. We’ll see how to write the results of operations on the sheet to the backend, as 2-way communication makes the Spreadsheet a more powerful tool to serve as a true Admin interface. Read about sheets API.

To write back to the backend, we’ll leverage Google Apps Script, which is a rapid application development platform, allows writing codes in JavaScript and makes it easy to integrate with G Suites, to extend Spreadsheet capability.

The Spreadsheet can now be used as a ‘source of truth’ and its features leveraged to perform useful operations from simple graphs to complex analytics.

Firebase cloud functions, which run on a Node.js environment, allow the installation of packages that interact with Google, Firebase or 3rd party services. For a 2-way communication between cloud functions and Google Spreadsheet, we'll install the googleapis library, create cloud functions and Google apps script triggers.

1. googleapis library is used to authenticate and write to the spreadsheet.
2. Http onRequest trigger is to enable writing from Spreadsheet to cloud functions. Learn ways to call cloud functions here.
3. Cloud functions Firestore onWrite :=> onCreate, onUpdate & onDelete trigger is to prompt when to write to the Spreadsheet.
4. Google apps script onEdit/onChange simple trigger is to prompt when to write to cloud functions. See other triggers you might need.

Now let’s write some codes!

We’ll aim at achieving the following:

1. Create the spreadsheet and copy the id.
2. Create a cloud functions trigger that prompt writing to the spreadsheet
3. Create an HTTP onRequest trigger to call the cloud functions from Google apps script
4. Use Google apps script simple triggers.

BEFORE WE BEGIN:
----------------
1. I assume you have a firebase project and you know how to set up cloud functions, if not read here and here
2. If not already done, enable the Google Sheets API and check the quota for your project at https://console.developers.google.com/apis/api/sheets. You may need to enable billing
3. Install the Node.js client library by running `npm install googleapis --save` in your cloud functions code
4. Read this to get an idea on how to achieve (2) and how to utilize some of the sheets APIs exposed by (3)

Note: If you plan to run this from another environment other than cloud functions, ensure to read about using OAuth 2.0 to access Google APIs here

Let’s create a new Spreadsheet document: go to https://docs.google.com/spreadsheets and be sure to have a google account. After creating one, copy the id of the document as shown below:

Photo credit to Alexis’s post

Now, save the spreadsheet id as an environment variable for your project using the cloud functions config:

firebase function:config:set googleapis.sheets.id=your_sheets_id

After creating the spreadsheet and saving the sheet id, you have to determine what data to write from the backend to the sheet. Let’s see some use cases that could generate such data:

1. Customer bids for products in an e-commerce catalog
2. User documents or credentials uploaded for approval
3. User account or content flagging requests
4. User role change requests etc.

You see that these use cases need some sort of response after being written to the sheet, and the aim is to perform the actions right there on the spreadsheet.

When you know what to write to the sheet, the next thing is to set up a trigger to listen for when an entry is made, created or updated, or deleted from the system. For example, you want to know when a customer makes a bid for a product. To achieve this, we’ll create a document onWrite trigger on cloud functions. See an example in the following snippet:

writeToSheetTrigger.ts

The trigger in the snippet is a response to a write event on the database. This means that there should already be a way to collect entries in the user-facing app.

With the onWrite trigger set up, the snippet below can be used to write to the Spreadsheet, by calling the sheets API’s append method.

appendEntry.ts

See other query parameters here. From the onWrite trigger, we can call any method e.g. createEntry, with relevant data to save a new entry on the spreadsheet and send notifications to the app admins.

You can send slack or email notifications from cloud functions. See examples here

createEntry.ts

You should use the same approach to update or delete an entry, although you need a way to save an entries row id on the sheet and one way to do this is to have a metadata collection in Firestore which is updated when a new entry is created, and read from when update or delete operations are performed

The next thing is to create an HTTP onRequest trigger that provides a URL to call the functions from Google apps script.

updateCustomerEntry.ts

The trigger above receives a request from a calling code, Google apps script, performs validation on the request body for safety reasons and checks if the customer and product exist.

The key concern here is security; how to ensure that only authenticated admin users perform these operations? This is discussed in a follow-up to this post: How to secure your Spreadsheet Admin interface

On deploying the newly written functions, a URL is generated for the HTTP onRequest trigger, which would be used by Google apps script to write back the backend

With the above setups, we can now write to or delete data from the Spreadsheet when there are changes in Firestore; create, update or delete. An example spreadsheet for customer orders would look like this:

The next thing is to write a Google apps script to send the result of operations on the sheet to the backend. To do this, we’ll leverage simple triggers, which are prompted when actions, edits or changes, occur on the spreadsheet. Note that you can create and manage Google apps script projects and triggers from the web interface.

Operations should be permitted only on the columns of interest, such as the action column above. This is necessary if using onEdit/onChange trigger which responds to changes on each cell.

A sample Google apps script to write to cloud functions and update Firestore based on actions performed on customer requests can look like this:

updateCustomerEntry.gs

After acting on the entries, the resulting state of the customer order data on the sheet could look like the following. The result column is a write-back from cloud functions response from the call to the HTTP onRequest endpoint.

The approach shown here is a simple one, an overview of what’s possible. In a real situation, it can be extended to capture multiple use cases as is applicable to the operations of an application. One exciting thing about using the Google spreadsheet as Admin interface is that its power could be leveraged when it comes to graphing and analytics, and even linking with other G suites like Big Query or Google Data Studio to make beautiful dashboards which, for a startup, can be a catch to potential investors.

With this in grasp, a lean startup does not need to spend too much resource building an admin interface especially when there’re a slim budget and tight deadline.