How to Upload Initial Firebase Data With Firebase Keys
Sync Google Sheets to a Firebase Realtime Database
Oft when testing a design, information technology's useful to create a prototype with realistic data. Users tend to give better feedback when the content is believable and not "lorem ipsum" placeholder text. In this short tutorial, we'll show you how to sync a Google Sheets spreadsheet to a Firebase Realtime database and utilize that in your high-allegiance prototype equally a data source.
In general, using a Realtime database is easier and faster than straight using the Sheets API. The Firebase free tier as well supports up to 100,000 simultaneous connections vs. 400 using the Sheets API. Once the spreadsheet is synced, y'all can use whatever stack to access your information very easily. Information technology too gives stakeholders and researchers an easy way to manipulate information, see the changes in realtime, and test multiple variations very apace.
Step 1: Create your Firebase project
If you haven't already, sign upwardly for Firebase using the free tier and then create your project.
Footstep two: Create your Realtime database
Navigate to Develop -> Database and click the "Create database" button.
Make sure you lot change your read and write permissions to "true" and click publish.
Editor'due south Note: While this is fine for initial evolution piece of work, don't ever go out your security rules open like this in a product app. Brand sure you lock them downwards, as the author notes later in the article.
Copy down the database URL. Nosotros'll need information technology later.
Your database URL will be unique to your project.
Step 3: Create your spreadsheet and populate it using this format
The outset row contains your keys. The starting time key should exist set to "id" and each row should be labeled with the corresponding number, starting with "ane". An easy way to set the id for each row in column A is to enter this formula "=COUNTA($B$ii:B2)" into cell A2 and then apply that to all rows.
You lot can add together as many rows or columns as yous need.
Step iv: Create your Apps Script project
In the menu, get to Tools -> Script editor
It will take you to a code editor with the following file open: Code.gs.
Replace the contents with this snippet.
Search for this code at the top of the file:
Replace the "spreadsheetID" placeholder with your own. The ID is the bolded part in the full spreadsheet URL (due east.g. https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0)
Replace the "firebaseUrl" placeholder with your database URL from Step 2. Make sure to include the trailing slash (e.grand. https://sheets-sample-test.firebaseio.com/ ) otherwise it will throw an mistake.
In your bill of fare, become to View -> Bear witness manifest file, which volition add a file called appsscript.json.
This will add together an appsscript.json file to your project. Supercede the contents with the following snippet.
Step 5: Start the sync
In the carte, get to Run -> Run function -> initialize. Yous'll see a prompt to review and accept the permissions. This allows the App Script projection to access the spreadsheet and upload data to Firebase. Click "Review Permissions" then click "Permit."
Congrats! Your Firebase Realtime database has now been populated with the data from your spreadsheet! Any further edits will sync seamlessly and you can even share your spreadsheet with other people.
Tip & Tricks
Add together Security
If you lot are feeling fancy, you tin can add a little more security to the Firebase database. Get to the Firebase Console -> Database and change your rules to the following.
You'll have to implement Firebase Authentication on your image, and so that your users can read the data. Setting the write method to false means but your spreadsheet tin write to the database.
Generating an Array
In the canvass if your id starts with 0 and increments by i, then the script volition generate an array instead of a key value pairs.
Nested data
When converting a table to JSON, it'southward only possible to practice one level of nesting. But what if you need nested information? The script provided above has a special office that lets you create a nested object. Simple name the championship of the column with the path of the object join key using a double underscore __. If you desire to nest street under address you tin simply say 'address__street', this means yous cannot have a column named just address in your sheet.
This will generate an object like the one below, and you can also nest at any level.
Give it a try! Yous won't be disappointed…
Nosotros think the technique we've described will elevate your prototypes with realistic data. Our goal is to help reduce the corporeality of fourth dimension y'all spend on data input and free upwardly more than fourth dimension to experiment and iterate! Feel gratis to attain out if you have any feedback about this guide.
— Siddhartha Gudipati and Edwin Lee
Source: https://medium.com/firebase-developers/sheets-to-firebase-33132e31935b