Integrating your Google Spreadsheet with Firebase Functions
A spontaneous approach.
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
Now create a directory locally. i.e.
Once in the project director, initiate a Node project and install Firebase Tools as follows:
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.
Once you have completed the previous steps successfully, your directory should look like this;
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.
npm install googleapis@39 --save
Part 3 — Integrate
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.
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.
cd functions/spreadsheettouch createSpreadsheet.js createWorksheet.js updateWorksheet.js touch removeWorksheet.js
Below are code snippets for the Cloud Functions
This function will create a new spreadsheet. It accepts spreadsheet title as parameter.
This function will create a worksheet under a specified spreadsheet. It accepts the SSID (spreadsheet id) as parameter.
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.
This function will update a worksheet under a specified spreadsheet. It requires parameter such as SSID (spreadsheet id) and sheetRange (worksheet name).
Create an index.js file to export the functions.
Now import all the functions to functions/index.js file.
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
A URL will be generated for each function once deployed successfully. The format of the URL should be:
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
['name', 'age', 'gender'],
['john doe', 24, 'male'],
['felicia', 28, 'female']
curl -d "ssid=<SSID>&sheetRange=<sheet name>” -X GET https://<app_url>/removeWorkSheetParameters
- ssid: spread sheet id
- sheetId: work sheet id
Firebase gives you functionality like analytics, databases, messaging and crash reporting so you can move quickly and…
Node.js Quickstart | Sheets API | Google Developers
Complete the steps described in the rest of this page to create a simple Node.js command-line application that makes…