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:
Go to Tools in the main menu, then click Script editor.
A new tab or window will load, leading you to Google Apps Script.
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.
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:
You will be asked to select the version and identifier. You can leave them as is.
You need to authorize FirebaseApp to access your data. 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.
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 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.
Click Save project on the toolbar, then click Run the selected function. The output looks like this:
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.
