A Quick and Dirty Way to Connect Firestore to Google Sheets

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.

TABLE OF CONTENTS
8 min read

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

  • project_id
  • private_key
  • client_email

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:

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

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