Skip to content

Instantly share code, notes, and snippets.

Last active August 3, 2023 16:41
Show Gist options
  • Star 78 You must be signed in to star a gist
  • Fork 17 You must be signed in to fork a gist
  • Save CodingDoug/44ad12f4836e79ca9fa11ba5af6955f7 to your computer and use it in GitHub Desktop.
Save CodingDoug/44ad12f4836e79ca9fa11ba5af6955f7 to your computer and use it in GitHub Desktop.
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:

See also this gist for copying in the other direction:


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 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": [
// 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
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// See the License for the specific language governing permissions and
// limitations under the License.
function myOnEdit(e) {
const RTDB_URL = ""
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)
Copy link

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?

Copy link

robe007 commented Apr 25, 2018

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

Copy link

robe007 commented Apr 25, 2018

@CodingDoug Works like a charm ! Nice one again.

Copy link

@CodingDoug i solved it: in my case i needed to add an extra scope: "".
Then it started to work thanks!

Copy link

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

Copy link

prabby commented Aug 9, 2018

lit af boi 🔥 🔥

Copy link

Hiii @CodingDoug,

I got error message,

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

can you helm me ?


Copy link

Is there a way to do this for firestore?

Copy link

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.

Copy link

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

Copy link

kadanes 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?

Copy link

Is there a way to do this for firestore? check this out

Copy link

how do you generate auto-id for firestore?

Copy link

Here are detailed steps on syncing Google Sheets and Firebase:

Copy link

DSHeal commented Feb 11, 2023

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

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