Spreadsheets

|

April 18, 2021

One Way to Connect Firebase to Google Sheets

Image of two windows, one pointed at the other
SECTIONS

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.

Fortunately, we have ways to connect these services to Google Sheets. We already did one last year, connecting Shopify data to Google Sheets via Google Apps Script. You can read it again here.

Today, we will connect Firebase to Google Sheets. We’ll consider transferring data from Firebase to Google Sheets in the process.

For our reference, our simple database contains the following entry:

Firebase Realtime Database
Firebase Realtime Database


For the code to work, you should use the same Google account as the one you use for your Firebase database. Let’s start!

Step 1: Go to Tools in the main menu, then click Script editor.

Tools, Script editor
Tools, Script editor

A new tab or window will load, leading you to Google Apps Script. 

Apps script, untitled project, blank code.gs
Apps script, untitled project, blank code.gs


Step 2: 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:

Add a library, Script ID, Version, Identifier
Add a library, Script ID, Version, Identifier


You will be asked to select the version and identifier. You can leave them as is.

Step 3: You need to authorize FirebaseApp to access your data. Click Review permissions.

Authorization required. Click Review permissions.
Authorization required. 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. 

Choose an account, for selecting the Google account for access.
Choose an account, for selecting the Google account for access.


A list of permissions required will be listed after selecting the Google account. Click Allow.

List of permissions required
List of permissions required

Step 4: 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 a service. Google Sheets API highlighted.
Add a service. Google Sheets API highlighted.


Step 5: 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. 

Firebase Realtime Database. The URL is erased for security.
Firebase Realtime Database. The URL is erased for security.


Step 6: Save the code by either clicking the Save button or by pressing Ctrl+S, then click Run.

Apps Script, Save button greyed out. Run button besides Save button.
Apps Script, Save button greyed out. Run button besides Save button.


The data will automatically appear in your Google Sheets.

Google Sheets. Data from Firebase loaded.
Google Sheets. Data from Firebase loaded.


I would like to add a lot of reminders before repurposing the code:

  • The following line strongly depends on the structure of your Realtime database:

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.

  • Again, this only works if you are using the same Google account for both Google Sheets and Firebase. You may be asked for permission to access Firebase via the Apps Script. Allow it. If you will access a Firebase which is not hosted in your Google account, You need to ask for the service account credentials, which can be generated via Google Cloud Platform.


It took me hours to figure out this code. 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. Get early access here.

Subscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.