Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Copying Data from a Google Sheet into Firebase Realtime Database in real time via Apps Script

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.

  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

{
"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"
]
}
// 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())
}
@marlowe19

This comment has been minimized.

Copy link

@marlowe19 marlowe19 commented Apr 24, 2018

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?

@robe007

This comment has been minimized.

Copy link

@robe007 robe007 commented Apr 25, 2018

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

@robe007

This comment has been minimized.

Copy link

@robe007 robe007 commented Apr 25, 2018

@CodingDoug Works like a charm ! Nice one again.

@marlowe19

This comment has been minimized.

Copy link

@marlowe19 marlowe19 commented May 5, 2018

@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!

@freo1995

This comment has been minimized.

Copy link

@freo1995 freo1995 commented Jul 9, 2018

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

@prabby

This comment has been minimized.

Copy link

@prabby prabby commented Aug 9, 2018

lit af boi 🔥 🔥

@Nanank-kho

This comment has been minimized.

Copy link

@Nanank-kho Nanank-kho commented Dec 27, 2018

Hiii @CodingDoug,

I got error message,

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

can you helm me ?

thanks

@pretheesh

This comment has been minimized.

Copy link

@pretheesh pretheesh commented May 15, 2019

Is there a way to do this for firestore?

@vikasrairajput

This comment has been minimized.

Copy link

@vikasrairajput vikasrairajput commented Dec 19, 2019

@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.

@vikasrairajput

This comment has been minimized.

Copy link

@vikasrairajput vikasrairajput commented Dec 19, 2019

@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.

@parthv21

This comment has been minimized.

Copy link

@parthv21 parthv21 commented Feb 5, 2020

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

@JaeHwanWO

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 17, 2020

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

@JaeHwanWO

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 17, 2020

how do you generate auto-id for firestore?

@alexandermckay

This comment has been minimized.

Copy link

@alexandermckay alexandermckay commented Mar 16, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment