A guide on syncing data from Firestore to Google Sheets
(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.
(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.
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.
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.
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.
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.
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.
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.
