Sync Google Sheets to a Firebase Realtime Database
Often when testing a design, it’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 use that in your high-fidelity prototype as a data source.
In general, using a Realtime database is easier and faster than directly using the Sheets API. The Firebase free tier also supports up to 100,000 simultaneous connections vs. 400 using the Sheets API. Once the spreadsheet is synced, you can use any stack to access your data very easily. It also gives stakeholders and researchers an easy way to manipulate data, see the changes in realtime, and test multiple variations very quickly.
Step 1: Create your Firebase project
If you haven’t already, sign up for Firebase using the free tier and then create your project.
Step 2: Create your Realtime database
Navigate to Develop -> Database and click the “Create database” button.
Make sure you change your read and write permissions to “true” and click publish.
Editor’s Note: While this is fine for initial development work, don’t ever leave your security rules open like this in a production app. Make sure you lock them down, as the author notes later in the article.
Copy down the database URL. We’ll need it later.
Your database URL will be unique to your project.
Step 3: Create your spreadsheet and populate it using this format
The first row contains your keys. The first key should be set to “id” and each row should be labeled with the corresponding number, starting with “1”. An easy way to set the id for each row in column A is to enter this formula “=COUNTA($B$2:B2)” into cell A2 and then apply that to all rows.
You can add as many rows or columns as you need.
Step 4: Create your Apps Script project
In the menu, go 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 (e.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.g. https://sheets-sample-test.firebaseio.com/) otherwise it will throw an error.
In your menu, go to View -> Show manifest file, which will add a file called appsscript.json.
This will add an appsscript.json file to your project. Replace the contents with the following snippet.
Step 5: Start the sync
In the menu, go to Run -> Run function -> initialize. You’ll see a prompt to review and accept the permissions. This allows the App Script project to access the spreadsheet and upload data to Firebase. Click “Review Permissions” and then click “Allow.”
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 Security
If you are feeling fancy, you can add a little more security to the Firebase database. Go to the Firebase Console -> Database and change your rules to the following.
You’ll have to implement Firebase Authentication on your prototype, so that your users can read the data. Setting the write method to false means only your spreadsheet can write to the database.
Generating an Array
In the sheet if your id starts with 0 and increments by 1, then the script will generate an array instead of a key value pairs.
Nested data
When converting a table to JSON, it’s only possible to do one level of nesting. But what if you need nested data? The script provided above has a special function that lets you create a nested object. Simple name the title of the column with the path of the object join key using a double underscore __. If you want to nest street under address you can simply say ‘address__street’, this means you 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! You won’t be disappointed…
We think the technique we’ve described will elevate your prototypes with realistic data. Our goal is to help reduce the amount of time you spend on data input and free up more time to experiment and iterate! Feel free to reach out if you have any feedback about this guide.
— Siddhartha Gudipati and Edwin Lee
