Copying Data from a Google Sheet into Firebase Realtime Database in real time via Apps Script

README.md

Copying Data from a Google Sheet into Firebase Realtime Database in real time via Apps Script

If you're trying to do this, you came to the right place!

Watch this code work in real time: https://twitter.com/CodingDoug/status/942576182276497409

See also this gist for copying in the other direction: https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07

Setup

These instructions assume that you already have a Firebase project, and you're working with a spreadsheet in Google Drive under the same account.

1. Create a new Google Sheet

2. Add three headers to the first row with the text "item_id", "qty", and "purchased"

3. Open up the script editor for this sheet with Tools -> Script editor...

4. Give this project a name by clicking the text at the very top.

5. Open up the manifest with View -> Show manifest file

6. Add the necessary scopes to the top level of the manifest JSON. These are required to use the Firebase Realtime Database REST API. The final manifest will looks like the manifest file added to this gist with the file name appscript.json.

   • https://www.googleapis.com/auth/userinfo.email
   • https://www.googleapis.com/auth/firebase.database
   • https://www.googleapis.com/auth/script.external_request

7. Switch back to the main script file Code.gs and paste the code from this gist into it. It defines a trigger function called myOnEdit. Change the value of RTDB_URL to your Realtime Database URL.

8. Install the trigger into this project.

   1. Edit -> Current Project's triggers
   2. Click the link to add one.
   3. Configure it to show "myOnEdit", "From spreadsheet", "On Edit"
   4. Click Save

9. You will have to authorize this app to ask you for permission to use the OAuth scopes from earlier. This involves bypassing a warning dialog. Choose "Advanced", follow the instructions, select your Google Account, and authorize the permissions.

   • Note: this processes authorizes your Google account to use the REST API to make changes to your Realtime Database in the same project. If you share this project with others, this trigger will not be installed, and you will have to perform additional steps to allow that other account to make REST calls.

10. Add rows to the spreadsheet. You may only use valid Realtime Database keys for item_id. qty is intended to hold a number, and purchase to hold a boolean "true" or "false". These rows should appear as nodes in your database.

If for some reason things don't work, check the execution transcript for errors. View -> Execution transcript

Helpful documentation

• https://developers.google.com/apps-script/guides/triggers/installable
• https://developers.google.com/apps-script/reference/script/
• https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes
• https://firebase.google.com/docs/database/rest/auth

appscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/userinfo.email",
    "https://www.googleapis.com/auth/firebase.database",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

code.gs

// Copyright 2017 Google LLC.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

function myOnEdit(e) {
    const RTDB_URL = "https://your-project.firebaseio.com"

    const sheet = e.range.getSheet()
    const range = sheet.getDataRange()
    const allValues = range.getValues()

    // Get column indexes from the names of the headers in the first row
    const headers = {}
    allValues[0].forEach(function(value, index) {
        headers[value] = index
    })

    // Collect all the data from the sheet into a object to send to the database
    const dbData = {}
    allValues.forEach(function(row, index) {
        if (index === 0) { return }  // skip header row
        dbData[row[headers.item_id]] = {
            qty: row[headers.qty],
            purchased: row[headers.purchased]
        }
    })

    const token = ScriptApp.getOAuthToken()
    const url = RTDB_URL + "/items.json?access_token=" + encodeURIComponent(token)
    const response = UrlFetchApp.fetch(url, {
        method: 'put',
        payload: JSON.stringify(dbData)
    })
    Logger.log(response.getResponseCode())
}

Hi coding Doug, i get this message in the execution transcript:
[18-04-24 16:05:25:846 CEST] Execution failed: TypeError: Cannot find function getSheet in object

Can you help me out?

@marlowe19 You don't need to execute the .gs file. Only edit the spreedsheet, and voilà !

@CodingDoug Works like a charm ! Nice one again.

@CodingDoug i solved it: in my case i needed to add an extra scope: "https://www.googleapis.com/auth/spreadsheets".
Then it started to work thanks!

Does this still work @CodingDoug? I keep getting nil records. Please note that I am pulling data from another sheet on the selected sheet using a query

lit af boi 🔥 🔥

Hiii @CodingDoug,

I got error message,

TypeError: Cannot read property "range" from undefined. (line 4, file "myOnEdit")

can you helm me ?

thanks

Is there a way to do this for firestore?

@Nanank-kho I am getting the same error. have you found any solution? I am trying to do with the Firestore instead of realtime Database.

@Nanank-kho, the error is happening because I try to run the function and it's not getting spreadsheet reference. When someone edits the spreadsheet file the onEdit function will be called where you will get the spreadsheet reference.

Is there a way to append individual column entries for a newly added row, to a specific key in the database?

Is there a way to do this for firestore?

http://grahamearley.website/blog/2017/10/18/firestore-in-google-apps-script.html check this out

how do you generate auto-id for firestore?

Here are detailed steps on syncing Google Sheets and Firebase: https://github.com/alexandermckay/sync-firebase-with-google-sheets

@CodingDoug God bless you, man! You ended my over-week-long search for the solution!