- To provide a simple method to syncronise a Google Sheet file with a Realtime Database.
- Google Sheets provides a simple interface for non-programmers to add, edit and delete data whilst the Firebase API provides an effective interface to make programmatic read/write request to the data.
- Multiple sheets.
- No column or row range restrictions.
- Single cell updates (Firebase --> Sheets).
- Instantaneous (~1 second delays)
- Free
- The project aims for approximately 85-90% test coverage.
- Any contributions should include necessary tests.
- Clone repo
yarn installcd functions && npm install && cd ..
- Create a Firebase project
- Copy the DB Url
- Create a new Google Sheet
- Change the file name from 'Untitled Spreadsheet' to 'Firebase' (non-essential)
- Change the sheet name from 'Sheet 1' to 'Users' (non-essential)
- Open the 'Tools' menu
- Open 'Script Editor'
- Change the script name from 'Untitled' to 'Firebase' (non-essential)
- Open the 'View' menu
- Click 'Show manifest file'
- Open
appscript.jsonand add the following oAuth scopes
{
// other stuff
"oauthScopes": [
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/firebase.database",
"https://www.googleapis.com/auth/script.external_request"
]
}- Open
Code.gs - Delete all the existing code in
Code.gsand replace it with the code fromappscript/update-firebase.js, taking care not to include the imports/exports which are for testing purposes only. - Update the
dbUrlin theformatUrlfunction. - Open the 'Edit' menu
- Open 'Current Project Triggers'
- Click 'Add Trigger'
- Add the following settings:
- Click 'Save' (may have to scroll slightly)
- Click 'Advanced' on the 'This App isn't Verified' pop-up
- Click 'Go to Firebase'
- Click 'Allow'
- This is what you should see when you edit the sheet:
- Sign into the same Google account you used to create the spreadsheet.
- Go to the Google Console.
- Create a new project or select an existing one.
- Click 'Create Credentials' and generate a service account.
- Set 'Role' to 'Owner'.
- Download file.
- Enable the Sheet API for you service account (screenshot)
- Copy the spreadsheet Url
- Run
node utils/id-extract.js <PASTE SPREADSHEET URL> - Move the service account file you downloaded into
functions/configdir and rename it toservice-account.json. - Share the spreadsheet with the service account email available in the
service-accont.jsonfile and uncheckNotify People.
- Show all available Firebase projects -
firebase projects:list - Helper to select the one you would like to use -
firebase use --add - Deploy functions -
firebase deploy --only functions - Make sure everything is working.
- Tighten up restrictions with the read/write rules.
- AppScript does not have feature parity with JavaScript. It only recently (early 2020) made the jump to ES6. Whilst most of the features you would expect have been added. Some such as the
...operator have not. Using modern features can cause your code to fail silently so test consistently if you decide to refactor or add further code. - In Google Sheets (AppScript), you may have to select the option Run > Enable New App Script Runtime Powered By Chrome V8 in order for your code to work.
- Don't forget to save!!!