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.

4 comments

  1. Great work Ben and I really hope I can get to use this in my own project.

    I have encountered SyntaxError: Unexpected token export (line 17, file “Code.gs”) which points to the main(export) function.

    How did you include or define the export variable/token?

    Like

  2. Great one Ben, you really helped me complete a project and business idea successful.

    Also, Ben, what if I want to use specific Document IDs instead of something like this…’lippldEeWJ1wHZHgcXU’

    Let’s say firstName and lastName

    (davidOgundepo)
    (benChartrand)

    Thanks, Ben.

    Liked by 1 person

    1. I’ve used the ID field as a flag so that I know whether to add or update. There’s a few things you could do
      1) Add a flag elsewhere to act as a flag (another column) so it knows if it should do an addition. Once added you could remove that flag
      2) Check the docs. You might be able to use a general “set” command which does either an update (if it exists) or add (if it doesn’t). I don’t have the docs handy but, from what i recall, that command wasn’t available so I had do something else.

      Like

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: