Copying Data from a Google Sheet into Firebase Realtime Database in real time via Apps Script
If you're trying to do this, you came to the right place!
Watch this code work in real time: https://twitter.com/CodingDoug/status/942576182276497409
See also this gist for copying in the other direction: https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07
These instructions assume that you already have a Firebase project, and you're working with a spreadsheet in Google Drive under the same account.
Create a new Google Sheet
Add three headers to the first row with the text "item_id", "qty", and "purchased"
Open up the script editor for this sheet with
Tools -> Script editor...
Give this project a name by clicking the text at the very top.
Open up the manifest with View -> Show manifest file
Add the necessary scopes to the top level of the manifest JSON. These are required to use the Firebase Realtime Database REST API. The final manifest will looks like the manifest file added to this gist with the file name
Switch back to the main script file
Code.gsand paste the code from this gist into it. It defines a trigger function called
myOnEdit. Change the value of
RTDB_URLto your Realtime Database URL.
Install the trigger into this project.
Edit -> Current Project's triggers
- Click the link to add one.
- Configure it to show "myOnEdit", "From spreadsheet", "On Edit"
- Click Save
You will have to authorize this app to ask you for permission to use the OAuth scopes from earlier. This involves bypassing a warning dialog. Choose "Advanced", follow the instructions, select your Google Account, and authorize the permissions.
- Note: this processes authorizes your Google account to use the REST API to make changes to your Realtime Database in the same project. If you share this project with others, this trigger will not be installed, and you will have to perform additional steps to allow that other account to make REST calls.
Add rows to the spreadsheet. You may only use valid Realtime Database keys for item_id. qty is intended to hold a number, and purchase to hold a boolean "true" or "false". These rows should appear as nodes in your database.
If for some reason things don't work, check the execution transcript for errors.
View -> Execution transcript