Connect Firestore to Google Sheets (the Quick and Dirty Way)

Learn a rough way of connecting Firestore to the program we all know and use. Here we outline for you the easiest way of connecting the data analyzation tool.

8 min read
Table of Contents

This Process is Easier With Lido!

  • Import Firestore data in just a few clicks
  • Lido looks just like Google Sheets but with more advanced features!
  • No complex code or developer time needed!
Click Here to Learn More!



We have always talked about utilizing the power of Google Sheets to analyze large 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. We have also connected Firebase Realtime Database to Google Sheets. You can read it again here.

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

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


Firestore is a more advanced database within Firebase, as it allows for more complex databases grouped into so-called “documents”. Thus, Firestore is a preferred database platform if you want to store more complex and diverse information into several arrays.

To continue, you need access to the database via a service account. The service account details should have the following information:

Follow Google’s guide to creating a service account here:

Create a service account - Google Workspace Admin Help

If you got the service account details, you can now complete the following steps:

How to Sync Firestore to Google Sheets

Step 1: Open Google Apps Script in the Script editor

Go to Tools in the main menu, then click 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

Step 2: Add the FirestoreApp Library

On the left side of the screen, you will see a list of Files, Libraries, and Services. We want to use the FirestoreApp library and the Google Sheets API Services. First, we will add the FirestoreApp library. Click the plus sign in the Libraries box.

AppsScript, Libraries. Add a library highlighted.

A box labeled Add a Library will appear. On the Script ID, add the following:

1VUSl4b1r1eoNcRWotZM3e87ygkxvXltOgyDZhixqncz9lQ3MjfT1iKFw

Click Look up. You will see the following screen:

Add a library. FirestoreApp selected.

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

Step 3: Authorize FirestoreApp to Access your Data

You need to authorize FirebaseApp to access your data. 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. Simply allow the request for permissions.

Step 4: Add the Google Sheets API

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.

Step 5: Add our Custom Code

Add the following code, which contains notes on what to modify for your Firestore database:

function getFireStore() {

 var config = {

   'project_id' : 'project_id_here’,

   'private_key' : 'private_key_here’,

   'client_email' : ‘client_email_here’,

 };

 var firestore = FirestoreApp.getFirestore(config.client_email, config.private_key, config.project_id);

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = ss.getActiveSheet();

// the following lines depend on the structure of your database

// specify the document in the Firestore to access by replacing the countries with the name of your database

 

 const allDocuments = firestore.getDocuments("countries");

 

// for each column and row in the document selected

 

 for(var i = 0; i < allDocuments.length; i++){

   //initializes the  array to be printed to Google Sheets

   var myArray = [];

   //accessing the first column, replace the “name” with the header of your first column

   var country = allDocuments[i].fields["name"];

   myArray.push(country.stringValue);

   //accessing the second column that has several entries, replace the “cities” with the header of your

    //second column

   var cities = allDocuments[i].fields["cities"];

   var cities2 = cities.arrayValue.values;

   var cities3 = [];

   for(var j = 0; j < cities2.length; j++){

      cities3.push(cities.arrayValue.values[j].stringValue);

   }

   myArray.push(cities3.join());

   //accessing the third column, replace the “capital” with the header of your third column

   var capital = allDocuments[i].fields["capital"];

   myArray.push(capital.stringValue);

   sheet.appendRow(myArray)

 }

}

This code is more complicated, but remember that the second part of the code depends on the structure of your Firestore database. Diligently follow the comments in the code so that it works for your database.

Step 6: Import Firebase Data to Google Sheets

Click Save project on the toolbar, then click Run the selected function. The output looks like this:

The spreadsheet with the data

If you run the code again, it will simply append the new data on the succeeding rows, even if they are duplicates of what was already written.


I already talked about how challenging Firebase integration via Google Apps Script is–it’s even more so with Firestore. The following sources helped me make this work:

How can I write the data in Firestore to Google Sheet with app script?

Import Firestore data inside Google excel sheet | by Amit Saini

Google Apps Script Patterns: Writing rows of data to Google Sheets

The Array method join() in Apps Script

I am sure you won’t appreciate spending hours doing this code, only to modify it again for your quickly changing Firestore database. I won’t beat around the bush: I know a quicker way. 

It’s called Lido.

Create an account and get started today.

Enter Your Email Below to Try Lido for FREE!

Experience all of the benefits of the classic spreadsheet with much more powerful features that save users time!
  • One Click Data Imports From Anywhere
  • Transform Spreadsheets into Software in Seconds
  • Pre-built & Custom Templates
  • Visualize Real-Time Data in Centralized Dashboards
100% FREE - No Credit Card Required