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.
- Make a copy of the spreadsheet
- On the Tools menu, select the Script editor… option
- Once you are in the script editor, select Resources > Libraries…
- Follow the steps here to select the Firebase library: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase
- 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: