Automating data imports to firebase
The ask
We were creating multiple flavors and builds of a tester app, and keeping track of everything was becoming unweildy. Similarly, we were using at least an hour of dev time for each build QA needed. We needed a way for QA to set up the configurations themselves and access them all from a single app rather than having to keep track of multiple builds.
The Idea
This article planted the seed that perhaps the Google Form we were already using could be co-opted to directly send data to Firebase, which could then be parsed by the app.
Google Scripts
Based on the aforementioned article about writing data from an app's script, we're using Google Scripts to do the data transfer. In order to link from the form itself, open the form in edit mode and select Script Editor from the overflow menu. You have to open the script editor from the form and not from scripts.google.com in order to use the Form as a trigger to run your script.
If you want to trigger your script from a Google Sheets document, open the script editor from the sheet's overflow menu just like you would for a Google Form
Reading in data
var ss = SpreadsheetApp.openById(<Sheets ID>); var sheet = ss.getSheets()[0]; var data = sheet.getDataRange().getValues(); for(var i = 1; i < data.length; i++) { // parse rows as Firebase objects }
TODO: I know there's a way to access the form/sheet that triggered the script to run directly rather than referencing a sheet by ID, but I haven't figured out the implementation in my specific use case yet.
I'm pulling the data from the Google sheet that the form data populates to. The Sheets ID is visible in the URL when you open a Google Sheet. The URL will be formatted as https://docs.google.com/spreadsheets/d/<Sheets ID>
Turning Rows into Firebase objects
Think of the data variable in the above example as an array of rows, and each row is an array of cells. So data[n][m] is the contents of the cell at row n, column m. We start at row 1 in our for loop assuming that row 0 is just a row of column headers. Let us say for instance that we want to create a Firebase object that fully reflects data which looks like this:
| Name | Age | Height ft | Height in | Birthdate | |-------|-----|-----------|-----------|-----------| | Alan | 50 | 5 | 8 | Jan 1 | | Bob | 37 | 6 | 2 | Feb 2 | | Carol | 29 | 5 | 11 | Mar 3 |
Each Firebase object will need Name, Age, Height, and Birthdate as keys. Height will contain a sub-object with keys of ft and in. Each object should also have a unique key, which can be anything you determine - in this case we're just going to use row number.
//get data from sheet ... var objects = {}; for(var i = 1; i < data.length; i++) { objects[i] = parseData(data[i]); } // send data to Firebase ... function parseData(Array) { return { name:Array[0], age:Array[1], birthdate:Array[4], height:{ft:Array[2], in:Array[3]} } }
Send Data to Firebase
Per the script example I found, in order to connect to Firebase you need to install the library in your Google Script. In the script editor, open the Resources menu and click Libraries... Paste MYeP8ZEEt1ylVDxS7uyg9plDOcoke7-2l into the box next to Find a Library and press Select. This is the project key for the FirebaseApp plugin library. In the version drop-down menu, choose the most recent public release version and click Save.
Now you will have access to Firebase-specific methods in your script. If you have open write access on your Firebase database, you can write your spreadsheet data like so:
//get data from sheet ... var objects = {}; for(var i = 1; i < data.length; i++) { objects[i] = parseData(data[i]); } var base = FirebaseApp.getDatabaseByUrl(<Your Firebase URL>); base.setData("", objects);
If you require authorization to write to your database (recommended), you'll need to pass the Secret as a second parameter in the call to getDatabaseByUrl. To obtain your secret, open your project in the Firebase console, click the gear icon next to Overview, and select Project Settings. Go to the Service Accounts tab, and select Database Secrets under Legacy credentials. If no secrets are populated, click Add Secret, then click on the row and select Show. Copy this value into a variable in your Google Script and pass it as the second argument in your getDatabaseByUrl call.
Note: this is a deprecated means of authenticating and will likely go away in the future. This document should be updated soon with the new recommended authentication process for scripts.
Trigger
Once you have run your script and determined that it is processing your data as expected, it's time to set up a trigger. In the Resources menu, select current project's triggers. If you have created your script from a Google Sheets document or a Google Form, the Events drop-down menu should be pre-populated with from form or from spreadsheet. There may also be an option to run the script at selected time intervals. Choose the name of your function, and in the case of a Google Form From form and On form submit. Now after you save and close the script, every time you fill out the form your data should automatically be added to your Firebase Database.
