DEV Community

Jen Chang
Jen Chang

Posted on • Originally published at Medium on

Importing Data From Google Spreadsheet into Firebase

For a group project, we have decided to collect resources using a Google Form, which stores the data into a spreadsheet. In order to make this more useful for us, we decided to import the data into Firebase. The following are the steps used to import the data from the Google Spreadsheet into Firebase.

Spreadsheet Setup

  1. Make a copy of the spreadsheet
  2. On the Tools menu, select the Script editor… option

  1. Once you are in the script editor, select Resources > Libraries…

  1. Follow the steps here to select the Firebase library: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase

  1. Follow the steps here to start importing the data into Firebase: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase/tutorials/read-and-write-data-in-firebase-from-apps-script

There are following caveats when importing the spreadsheet data over to Firebase:

You cannot have invalid characters for the Firebase key so take a look at the character set limitations below. The key is part of the URLs used to construct the Firebase references. Therefore as a workaround, encoding the email address as base64 will work.

Character Set Limitations

Note that URLs used to construct Firebase references may contain any unicode characters except:

  • . (period)
  • $ (dollar sign)
  • [ (left square bracket)
  • ] (right square bracket)
  • # (hash or pound sign)
  • / (forward slash)

Permission must be provided before the app script can be run against the spreadsheet.

Finally, the app script for importing our data from the spreadsheet to our Firebase looks something like this:

Be sure to replace the spreadsheet URL (copy from the exact URL after opening your spreadsheet manually) and the Firebase URL (copied from the Firebase Dashboard). Updated the JSON format manually as needed before executing the script. However, even if you make a mistake, you can go to the Firebase Dashboard and delete all the data.

For reference, to delete the data from the Firebase Dashboard, click on the X and confirm by clicking on the DELETE button as shown below:

Top comments (0)