Firebase

Exporting and Importing data to/from Firestore using Google Sheets

Let me show you how I created a Google Sheets script to easily export and import data to/from Firestore.

Click here to jump straight to the repo with code.

Background

I was watching this excellent video on Firebase Console Tips & Tricks by David East and Sumit Chandel.

I had no idea you could do this from Sheets! I was really keen to use this approach and extend it so that:

  • It grabbed the actual number or rows (the video just grabbed 100)
  • It handled updates and deletes (the video only handled creates)
  • Also handled imports (video only covered exports)

I had an ideal use for this in my app: something I called publiclessons. These are lesson plans any one of my users can access.

I’ll be managing this collection and I was already wondering to myself: how will I manage this? I don’t really want to write a tool / UI just for this, especially as the data is relatively simple, I’ll be the only one using it and the data will change infrequently.

Using a Google Sheet is perfect!

Summary of what I did

  • Opened a Sheet
  • Clicked Tools > Script Editor
  • Typed up that code. In your case, just paste it!
  • Followed the quick start mentioned here, to install the library, get my secrets and add the secrets to my script https://github.com/grahamearley/FirestoreGoogleAppsScript
    • You’ll see several comments with TODO that you need to fill out
  • Window popped up occasionally asking for permission to connect to my GCP / Firestore instance. I gave it permission
  • Made sure to clicked Save

How it works

There’s two parts to my script – exports and imports.

Exports

This is largely based on the content of the video but I took it a bit further. Key things I added were:

  • Column A is the ID of the doc.
    • If it’s blank the document will be added and that field will show the ID from Firestore
    • If it’s not blank it will update the document in Firestore
  • Column H (the far right) I called toDelete. If that field is non-empty it will delete the row in Firestore, then the sheet
  • The video just grabbed 100 rows. My sheet grabs the actual data in the sheet

Imports

I added another menu item and added functionality to:

  • Import records that don’t exist in the sheet to the sheet
  • Update existing records in the sheet. The import will match up the properties to the correct column
  • Note that it does not handle the situation where the record is not in Firestore but is in the sheet

Result

This is what it looks like. Nice and easy to use.

Data ready to be exported
After data was exported to Firestore

Code

All the code can be found here, in this repo.

Also included is a sample csv field and a sample sheet of the same data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: