google-sheet-sync

Automatically sync data to a Google Sheet

This sample demonstrates how to sync new data written to a Firebase database to a Google Sheet. It includes a method for obtaining, storing, and using Oauth2 tokens for Google API access.

This code is also described in this blog post.

Functions Code

See file functions/index.js for the Google Sheet sync code.

Use the HTTPS function authGoogleAPI to request Oauth2 tokens for the Google API.

The trigger function is appendRecordToSpreadsheet.

To test it out, use the HTTPS function testSheetWrite.

Trigger rules

The function triggers on when data is added to the DATA_PATH of the active Firebase database. In this sample, objects written to DATA_PATH/{ID} in the form {firstColumn: value, secondColumn: value, thirdColumn: value} are appended to the sheet.

Deploy and test

To deploy and test the sample:

1. Create a Firebase project on the Firebase Console and visit the Storage tab.
2. Clone this repo: git clone https://github.com/firebase/functions-samples.
3. Open this sample's directory: cd functions-samples/google-sheet-sync
4. Setup your project by running firebase use --add and select the project you had created.
5. Install dependencies in the functions directory: cd functions; npm install; cd -
6. Using the Google APIs Console create an OAuth Client ID Click this link, select your project and then choose Web Application. In Authorized redirect URIs, you’ll need to enter https://{YOUR-PROJECT-ID}.firebaseapp.com/oauthcallback.
7. Configure your Google API client ID and secret by running:

   firebase functions:config:set googleapi.client_id="YOUR_CLIENT_ID" googleapi.client_secret="YOUR_CLIENT_SECRET"

8. Create a new Google Sheet, and copy the long string in the middle of the Sheet URL. This is the Spreadsheet ID.
9. Configure your Google Spreadsheet ID by running:

   firebase functions:config:set googleapi.sheet_id="YOUR_SPREADSHEET_ID"

10. Specify the path of the data in the Realtime Database that you want automatically copied to your Spreadsheet:

    firebase functions:config:set watchedpaths.data_path="THE_DATA_PATH_YOU_WANT"

11. Deploy your project using firebase deploy
12. Configure the app once by opening the following URL and going through the auth flow https://{YOUR-PROJET-ID}.firebaseapp.com/authgoogleapi
13. To test, go to {YOUR_PROJET_ID}.firebaseapp.com/testsheetwrite. This will automatically add some test data in your Firebase Realtime Database in the data path that you set in watchedpaths.data_path.
14. Check your Google Sheet, to see these same values which have been appended via the trigger function.