We have always talked about utilizing the power of Google Sheets to analyze huge amounts of data, helping us extrapolate meaningful insights about our market and the performance of our campaigns and products.
Today, we will connect Firebase Realtime Database to Google Sheets. If you're looking to connect Firestore instead of Firebase, we've written a separate guide for connecting Firestore to Google Sheets here.
For our reference, our simple database contains the following entry:
For the code to work, you should use the same Google account as the one you use for your Firebase database. Let’s start!
Go to Tools in the main menu, then click Script editor.
A new tab or window will load, leading you to Google Apps Script.
On the left side of the screen, you would see a list of Files, Libraries, and Services. We want to use the FirebaseApp library and the Google Sheets API Services.
First, we will add the FirebaseApp library. Click the plus sign in the Libraries box. A box labeled Add a Library will appear. On the Script ID, add the following:
1hguuh4Zx72XVC1Zldm_vTtcUUKUA6iBUOoGnJUWLfqDWx5WlOJHqYkrt
Click Look up. You will get the following screen:
You will be asked to select the version and identifier. You can leave them as is.
You need to authorize FirebaseApp to access your data. Click Review permissions.
You will be asked to choose an account to continue. Most of the time this should list the account you are currently using for your project.
A list of permissions required will be listed after selecting the Google account. Click Allow.
To add the Google Sheets API, click the plus sign besides the Services box. A pop-up window will appear listing the Advanced Google Services that you can use. Look for Google Sheets API, then click Add.
Add the following code:
function getAllData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = ss.getActiveSheet();
var firebaseUrl = "insert_the_firebase_url_here";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
var dataSet = [base.getData()];
// the following lines will depend on the structure of your data
//
var rows = [],
data;
for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.id, data.name]);
}
dataRange = sheet.getRange(1, 1, rows.length, 2);
dataRange.setValues(rows);
}
You need to replace the insert_the_firebase_url_here with the actual URL of your Firebase database. You can access it by going to your Firebase database, and then clicking Realtime database.
The link will appear on the top of the box containing the data of your database. In the picture below, it has been erased on purpose for security.
Save the code by either clicking the Save button or by pressing Ctrl+S, then click Run.
The data will automatically appear in your Google Sheets.
I would like to add a lot of reminders before repurposing the code to export firebase to google sheets:
rows.push([data.id, data.name])
You need to change the “words” following the data variable to the ones you actually use in your Realtime database.
It took me hours to figure out this code to manage data across firebase and google sheets. I assembled the code from the following sources:
1- Quickstart: Read and write data in Firebase from Apps Script - Google Apps Script Examples
Sync a Google Sheets spreadsheet to a Firebase Realtime database
google script - json into google sheet
And I’ll be honest, it's a heck of a challenge.
Consider that the hours you spend coding this could have, instead, been spent actually seeing the final metrics and making the right decisions for your business.
I know a quicker way.
It’s called Lido.
You don’t need to scour the web for a good working code only to find out you still need to modify it so that you can connect your Firebase to Google Sheets. Sign up here.
