Examples
June 18, 2024

Firestore to Google Sheets

A guide on syncing data from Firestore to Google Sheets

What is Firestore?

(Since you probably already know what Firestore is, skip to the Options section below)

Developed by Google, Firestore is a versatile, scalable NoSQL cloud database, specifically designed to streamline data storage and synchronization for both client and server-side development. Operative as a core component of Google's Firebase platform, Firestore offers a wealth of impressive features, including real-time updates, automatic scaling, offline support, and diverse data queries. These capabilities empower developers to construct sophisticated, interactive applications with remarkable efficiency.

Firestore's database structure is organized into collections and documents, providing the capability to form intricate nested objects. This flexible, hierarchical organization of data suits the diverse requirements of contemporary, dynamic applications. As a cloud-based solution, Firestore maintains seamless integration with other Google cloud tools, solidifying its position as an invaluable resource for state-of-the-art application development in today's digital age.

Options for Replicating Firestore in Google Sheets

Coding

(Since coding takes too long and involves re-inventing the wheel, skip to the No-Code section below)

Firestore offers several methods to extract database data, streamlining its transformation and loading into other platforms such as Google Sheets. These methods vary based on the requirements and the context in which the data extraction occurs.

The first approach involves the use of Firestore's APIs. Developers can programmatically extract data directly from Firestore using Firestore's REST, RPC, or client libraries. These tools can handle large volumes of data and support several programming languages, such as Java, Go, Python, Node.js, C++, and others.

Client libraries from Google's documentation

Secondly, Google Cloud Shell or Google Cloud SDK can be used, especially in cases where direct interactions with Firestore from the command line are required.

Finally, data can also be extracted manually through the Firestore console through data export.  This requires database format transformation and is not recommended.

Firestore's native data export option (manual)

No-Code & Automated

A handful of data integration vendors offer turnkey data extraction and replication from Firestore to Google Sheets.

In this example we'll walk through how to set up the integration using Flatly.

Create a Service Account

First, since Firestore is a component of Google Cloud, you will need to access your Google Cloud Console and generate a new Service Account for this integration.

Creating a service account in Google Cloud Console

After you have created the Service Account, go to the Keys tab in your Service Account and create a new key as JSON.

Store this key locally, securely and temporarily.

Lastly, go to IAM - Permissions - Grant Access and add Firebase Viewer role to the Service Account you just created.

Initialize Flatly
  1. Select Firestore as your Data Source, then select Google Sheets as your Data Destination.
  2. Enter your JSON key, Project ID and Collection Path into the prompt in Flatly.

The Collection Path refers to the collection that you want to replicate.

Example:  cats/tabbies/tabbies_in_boulder

Note: There is an odd number (3) of items in the path, which is required for collection-level references.

Authorizing Firestore in Flatly

  1. Authorize Google Sheets, then continue.
  2. Scroll down and click Flatten Daily.
Syncing configured successfully in Flatly

Result

Your Firestore collection will be replicated in Google Sheets every day.

Note: If your collection is over 10 million cells, select Google BigQuery as your Data Destination instead and follow the prompts.

Recent blog