Integrating your Google Spreadsheet with Firebase Functions

firebase cloud functions
Firebase Cloud Functions with Google Spreadsheet API

Firebase Function is a type of serverless architecture (also know as serverless computing). Firebase is just one among many providers (eg AWS Lambda, Microsoft Azure Functions) of serverless computing. To learn more about serverless architecture as well as how and when to use it, click here.

One of the things that come to mind when you mention Firebase is real time database. Nevertheless, Firebase offers several other products which includes Cloud Firestore, Hosting, Cloud Storage, Cloud Messaging, Analytics amongst others.

This post focuses on showing a simple way to integrate Google Spreadsheet with Firebase Functions seamlessly. Using cloud functions, you can authenticate users, connect with database, upload files and receive real time notifications.

First we need to create a Firebase project.

Part 1 — Set Up Your Firebase Project

Create you Firebase application herehttps://firebase.google.com. Once you have created your project, note down the project name and ID.

Now create a directory locally. i.e.

mkdir my-firebase-function
cd my-firebase-function

Once in the project director, initiate a Node project and install Firebase Tools as follows:

npm init
npm install -g firebase-tools

After running the above commands, login using the below command (you are required to login through the browser). Then initialise Firebase, select Cloud Functions press enter. After that, choose the existing project (same ID as you created previously) and press enter. When prompted with Install dependencies? choose yes and press enter, once you do, a directory called functions is created.

firebase login
firebase init

Once you have completed the previous steps successfully, your directory should look like this;

my-firebase-function/
├── node_modules/
├── firebase.json
├── functions/
├── index.js
├── node_modules/
├── package-lock.json
└── package.json
└── package.json

Part 2 — Set Up Your Google Spreadsheet API

Now that the Firebase setup is complete, the next step is turning on Google Sheet API. Follow this link to enable. Download the credentials.json and place it inside my-firebase-function/functions/.

Make sure you are inside the functions/ directory. If not, navigate to my-firebase-function/functions/ then Install the client library.

cd my-firebase-function/functions/
npm install googleapis@39 --save

Part 3 — Integrate

At this stage, you should have already setup Firebase and Cloud Function successfully. Firebase Cloud Functions currently allow using NodeJs and the example below are written using JavaScript.

Google Spreadsheet API Authentication

First we need to create a script to authenticate Google Spreadsheet API. The part below shows the snippet of the code used.

Click to view code snippet

Cloud Functions and Endpoints

To work with Google Spreadsheet API, we need to create some endpoint to access our Cloud Functions. The task of functions are below:

  • GET /<app_url>/createSpreadsheet
  • GET /<app_url>/createWorksheet
  • GET /<app_url>/updateWorksheet
  • GET /<app_url>/removeWorksheet

Next is to create the Cloud functions to manage Google Spreadsheet. For readability, each Function will be created in a separate file under my-firebase-function/functions/spreadsheet.

mkdir functions/spreadsheet
cd functions/spreadsheet
touch createSpreadsheet.js createWorksheet.js updateWorksheet.js touch removeWorksheet.js

Below are code snippets for the Cloud Functions

Create Spreadsheet

This function will create a new spreadsheet. It accepts spreadsheet title as parameter.

Click to view code snippet

Create Worksheet

This function will create a worksheet under a specified spreadsheet. It accepts the SSID (spreadsheet id) as parameter.

Click to view code snippet

Append / Update Worksheet

This function will update a worksheet under a specified spreadsheet. It requires parameter such as SSID (spreadsheet id), sheetRange (worksheet name) and data to append / update.

Click to view code snippet

Remove worksheet

This function will update a worksheet under a specified spreadsheet. It requires parameter such as SSID (spreadsheet id) and sheetRange (worksheet name).

Click to view code snippet

Create an index.js file to export the functions.

Click to view code snippet

Now import all the functions to functions/index.js file.

Click to view code snippet

Part 3 — Deploying Cloud Functions

Run the following command to deploy all the cloud functions

firebase deploy — only functions

Or for deploying specific function you can run

firebase deploy --only functions:createSpreadsheet

Note

A URL will be generated for each function once deployed successfully. The format of the URL should be:

https://<app_url>/<function_endpoint>

Part 4 — Testing

Now that the functions have being deployed successfully, below are few scripts for testing.

Create new Spreadsheet

Once created, the request returns the SSID (spreadsheet id). Take note of the SSID;

curl -d "title=Spreadsheet+Test” -X GET https://<app_url>/createSpreadSheetParameters
- title: Spreadsheet title

Create new Worksheet

curl -d "ssid=<SSID>&title=<sheet title>” -X GET https://<app_url>/createWorkSheetParameters
- ssid: Spread sheet identification
- title: Sheet title

Append / Update Worksheet

curl -d "ssid=<SSID>&sheetRange=<sheet name>&data=<array_of_data>” -X GET https://<app_url>/updateWorkSheetParameters
- ssid: spread sheet id
- sheetRange: sheet name
- data: array of data to be inserted / updated
example: [
['name', 'age', 'gender'],
['john doe', 24, 'male'],
['felicia', 28, 'female']
]

Remove Worksheet

curl -d "ssid=<SSID>&sheetRange=<sheet name>” -X GET https://<app_url>/removeWorkSheetParameters
- ssid: spread sheet id
- sheetId: work sheet id

--

--

Software engineer, runner, hiker, fitness enthusiast. https://namieluss.com

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Suleiman

Suleiman

Software engineer, runner, hiker, fitness enthusiast. https://namieluss.com