A simple, easy-to-use Google Apps Script solution that synchronizes data between Google Sheets and Firebase Realtime Database, enabling real-time collaboration, data management, and analytics.
Code forked from Vaibhav Gehani (Link)
This code was tested on ~3000 entries, not more. I'm not sure how performance wise it would behave with more entries. Feel free to share issues and updates.
- Synchronize data between Google Sheets and Firebase Realtime Database
- Real-time updates for changes in either platform
- Import specific sheets from a spreadsheet
- Display a progress status"
- Customizable data mapping for various use cases
- Automatic triggers on sheet changes -> Made it optionnal
- User authentication and access control support
- A Google Sheets document
- A Firebase project with Realtime Database enabled
- Create a new Google Sheets document and set up your sheet(s) with the desired structure and data.
- Create a new Google Apps Script project:
- In your Google Sheets document, click on
Extensions
>Apps Script
. - This will open the Apps Script editor. Copy the provided code into the
Code.gs
file.
- In your Google Sheets document, click on
- Update the environment variables:
- Replace the
spreadsheetID
andfirebaseUrl
variables in thegetEnvironment()
function with your own Google Sheets document ID and Firebase Realtime Database URL.
- Replace the
- Add the FirebaseApp library to your project:
- Click on
Resources
>Libraries...
in the Apps Script editor. - Add the library with ID
1hguuhS4U2_tiTohGwphq7xphjPqLeqJqE3gvWJMY9F6U9q3nqjveNQNz
and the latest version.
- Click on
- Update your sheet names:
- In the
initialize()
function, update thesheetsToImport
array with the names of the sheets you want to sync with Firebase Realtime Database.
- In the
- Enable Google Sheets API:
- In the Apps Script editor, click on
Resources
>Advanced Google Services
. - Scroll down to
Google Sheets API
and toggle it on.
- In the Apps Script editor, click on
- Configure OAuth 2.0 authentication:
- In the Apps Script editor, click on
Resources
>Cloud Platform project
. - Click on the blue project ID link to open the Google Cloud Platform Console.
- Click on
Navigation menu
>APIs & Services
>Credentials
. - Click on
Create credentials
>OAuth 2.0 Client ID
. - Select
Web application
as the application type, and give it a name. - In the
Authorized redirect URIs
field, addhttps://script.google.com/macros/d/{SCRIPT_ID}/usercallback
, replacing{SCRIPT_ID}
with your actual script ID (found in the Apps Script editor URL). - Click
Create
and take note of the generatedClient ID
andClient Secret
. - Back in the Apps Script editor, click on
File
>Project properties
>Script properties
. - Add two new properties:
client_id
with the value of yourClient ID
, andclient_secret
with the value of yourClient Secret
.
- In the Apps Script editor, click on
- Run the script:
- In the Apps Script editor, click on the
Select function
dropdown and chooseonOpen
. - Click the
Run
button (play icon) to execute the script. - Go back to your Google Sheets document and you will see a new menu item called
Firebase Sync
.
- In the Apps Script editor, click on the