Skip to content

Instantly share code, notes, and snippets.

@CodingDoug
Last active December 17, 2022 10:23
Show Gist options
  • Save CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07 to your computer and use it in GitHub Desktop.
Save CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07 to your computer and use it in GitHub Desktop.
Copying data from Firebase Realtime Database to a Google Sheet in real time via Cloud Functions

Copying data from Firebase Realtime Database to a Google Sheet in real time via Cloud Functions

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/940022568089554944

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

Setup

  1. Follow step 1 to enable Google Sheets API in your Firebase project: https://developers.google.com/sheets/api/quickstart/nodejs

  2. Create a service account in your project; save the json file in the functions folder with the file name serviceAccount.json.

  3. Create a spreadsheet in Drive; rename the first worksheet 'Scores'; add Player and Score headers in row 1, columns A and B.

  4. Share it with edit access to the email address in your service account.

  5. Copy the spreadsheet id (from its URL) to the spreadsheetId string in the TypeScript source.

  6. npm install firebase-admin firebase-functions googleapis@34 lodash

  7. Deploy this (TypeScript) code.

  8. Update the keys/values in your database under /scores and watch them get updated in the sheet!

Helpful documentation

// 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.
import * as functions from 'firebase-functions'
import * as _ from 'lodash'
import { google } from 'googleapis'
const sheets = google.sheets('v4')
const spreadsheetId = 'YOUR_SPREADSHEET_ID_HERE'
const serviceAccount = require('../serviceAccount.json')
const jwtClient = new google.auth.JWT({
email: serviceAccount.client_email,
key: serviceAccount.private_key,
scopes: [ 'https://www.googleapis.com/auth/spreadsheets' ], // read and write sheets
})
const jwtAuthPromise = jwtClient.authorize()
type Scores = { string: number }
export const copyScoresToSheet = functions.database.ref('/scores').onUpdate(async change => {
const data: Scores = change.after.val()
// Sort the scores. scores is an array of arrays each containing name and score.
const scores = _.map<Scores, [string, number]>(data, (value, key) => [key, value])
scores.sort((a,b) => { return b[1] - a[1] })
await jwtAuthPromise
await sheets.spreadsheets.values.update({
auth: jwtClient,
spreadsheetId: spreadsheetId,
range: 'Scores!A2:B7', // update this range of cells
valueInputOption: 'RAW',
requestBody: { values: scores }
}, {})
})
@abhishek0196
Copy link

abhishek0196 commented Jan 22, 2020

Thanks for the Reply... I want to update my Google Sheet in real time from firebase realtime database. Will you please make the video tutorial for that. Thanks...

Sure I can try that but it might take time !!

@abhishek0196
Copy link

@ArtosProjets do you want to use service accounts or want a different solution?

@JaeHwanWO
Copy link

@abhishek0196
Copy link

abhishek0196 commented Feb 26, 2020

I am getting error `SyntaxError: Unexpected
Where and When are you exactly getting this error?
Can you help with some code snippets?

@JaeHwanWO
Copy link

Does anyone get error No key or keyFile set at GoogleToken.getTokenAsync ~~ ?

@JaeHwanWO
Copy link

Is anyone facing error requested entity was not found?

@JaeHwanWO
Copy link

JaeHwanWO commented Feb 26, 2020

I've done this tutorial so I'm gonna scribble some of problems I have faced woking out.

  1. In step two, it says to create a service in your project.
    To make a project, in your terminal, make a directory and
    npm install -g firebase-tools
    firebase login
    firebase init functions and I've chosen Use an existing project since I already had project. Select your project, select TypeScript, TSLint no, install dependencies yes and you are all set.
  • (if npm install firebase-tools gives error, try npm install firebase-tools and when firebase returns command not found, type alias firebase="npm config get prefix/bin/firebase")
  1. Now, you will see functions folder. This is where you should save serviceAccount.json file.

Get your json file from APIs & Auth > Credentials in the Google Developers Console and select Service account from the Add credentials dropdown, not from the link in step1. This is the mistake I’ve made. It will result in giving out errors No key or keyFile set at GoogleToken.getTokenAsync.

  1. In your directory, from functions/src/index.ts, copy & paste the file firesheetz.ts from above.
    You should fill out spreadSheetId in index.js file. If your docs link looks like this,
    https://docs.google.com/spreadsheets/d/As1ksdflk8QL7XCXMnS3rpQ/edit#gid=12345678 (This is made up but your link will look like this anyway)

spreadSheetId is behind /d/ and before /edit/.

  1. Now, to Deploy,

firebase deploy

  • if you get errors about googleapis not existing, try

cd functions
npm install googleapis

and it worked well.

  1. Also, don’t forget to share your document with Service account.
    ( my service account looked like firebase-adminsdk-somekeyword@jsceno-93906.iam.gserviceaccount.com )

  2. Also, this example adds only up to 6 rows. Filling out more than 6 items in firebase realtime db also causes error.

Hope this helps many people and saves time... I spent a lot of time figuring these out.

@abhishek0196
Copy link

I've done this tutorial so I'm gonna scribble some of problems I have faced woking out.

  1. In step two, it says to create a service in your project.
    To make a project, in your terminal, make a directory and
    npm install -g firebase-tools
    firebase login
    firebase init functions and I've chosen Use an existing project since I already had project. Select your project, select TypeScript, TSLint no, install dependencies yes and you are all set.
  • (if npm install firebase-tools gives error, try npm install firebase-tools and when firebase returns command not found, type alias firebase="npm config get prefix/bin/firebase")
  1. Now, you will see functions folder. This is where you should save serviceAccount.json file.

Get your json file from APIs & Auth > Credentials in the Google Developers Console and select Service account from the Add credentials dropdown, not from the link in step1. This is the mistake I’ve made. It will result in giving out errors No key or keyFile set at GoogleToken.getTokenAsync.

  1. In your directory, from functions/src/index.ts, copy & paste the file firesheetz.ts from above.
    You should fill out spreadSheetId in index.js file. If your docs link looks like this,
    https://docs.google.com/spreadsheets/d/As1ksdflk8QL7XCXMnS3rpQ/edit#gid=12345678 (This is made up but your link will look like this anyway)

spreadSheetId is behind /d/ and before /edit/.

  1. Now, to Deploy,

firebase deploy

  • if you get errors about googleapis not existing, try

cd functions
npm install googleapis

and it worked well.

  1. Also, don’t forget to share your document with Service account.
    ( my service account looked like firebase-adminsdk-somekeyword@jsceno-93906.iam.gserviceaccount.com )
  2. Also, this example adds only up to 6 rows. Filling out more than 6 items in firebase realtime db also causes error.

Hope this helps many people and saves time... I spent a lot of time figuring these out.

If you are facing error with realtime database you can use firestore.
Here is a link to the gist.

@JaeHwanWO
Copy link

I've done this tutorial so I'm gonna scribble some of problems I have faced woking out.

  1. In step two, it says to create a service in your project.
    To make a project, in your terminal, make a directory and
    npm install -g firebase-tools
    firebase login
    firebase init functions and I've chosen Use an existing project since I already had project. Select your project, select TypeScript, TSLint no, install dependencies yes and you are all set.
  • (if npm install firebase-tools gives error, try npm install firebase-tools and when firebase returns command not found, type alias firebase="npm config get prefix/bin/firebase")
  1. Now, you will see functions folder. This is where you should save serviceAccount.json file.

Get your json file from APIs & Auth > Credentials in the Google Developers Console and select Service account from the Add credentials dropdown, not from the link in step1. This is the mistake I’ve made. It will result in giving out errors No key or keyFile set at GoogleToken.getTokenAsync.

  1. In your directory, from functions/src/index.ts, copy & paste the file firesheetz.ts from above.
    You should fill out spreadSheetId in index.js file. If your docs link looks like this,
    https://docs.google.com/spreadsheets/d/As1ksdflk8QL7XCXMnS3rpQ/edit#gid=12345678 (This is made up but your link will look like this anyway)

spreadSheetId is behind /d/ and before /edit/.

  1. Now, to Deploy,

firebase deploy

  • if you get errors about googleapis not existing, try

cd functions
npm install googleapis
and it worked well.

  1. Also, don’t forget to share your document with Service account.
    ( my service account looked like firebase-adminsdk-somekeyword@jsceno-93906.iam.gserviceaccount.com )
  2. Also, this example adds only up to 6 rows. Filling out more than 6 items in firebase realtime db also causes error.

Hope this helps many people and saves time... I spent a lot of time figuring these out.

If you are facing error with realtime database you can use firestore.
Here is a link to the gist.

Thanks for the link but I've already made it with RealtimeDB and that comment was guide for others.

@alexandermckay
Copy link

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

@nabilacsrn
Copy link

im already lost in step 2, can you explain more specific?

@abhishek0196
Copy link

im already lost in step 2, can you explain more specific?

Screenshot from 2020-05-05 20-45-03
You can create the service account by following this link

@Jeet-Metapercept
Copy link

I've done this tutorial so I'm gonna scribble some of problems I have faced woking out.

1. In step two, it says to create a service in your **project**.
   To make a project, in your terminal, make a directory and
   `npm install -g firebase-tools`
   `firebase login`
   `firebase init functions` and I've chosen **Use an existing project** since I already had project. Select **your project**, select **TypeScript**, **TSLint no**, **install dependencies yes** and you are all set.


* (if npm install firebase-tools gives error,  try `npm install firebase-tools` and when firebase returns command not found, type `alias firebase="`npm config get prefix`/bin/firebase"`)


1. Now, you will see `functions` folder. This is where you should save **`serviceAccount.json`** file.

Get your json file from APIs & Auth > Credentials in the Google Developers Console and select Service account from the Add credentials dropdown, not from the link in step1. This is the mistake I’ve made. It will result in giving out errors No key or keyFile set at GoogleToken.getTokenAsync.

1. In your directory, from **functions/src/index.ts**, copy & paste the file **firesheetz.ts from above**.
   You should fill out **spreadSheetId** in index.js file. If your docs link looks like this,
   https://docs.google.com/spreadsheets/d/As1ksdflk8QL7XCXMnS3rpQ/edit#gid=12345678 (This is made up but your link will look like this anyway)

spreadSheetId is behind /d/ and before /edit/.

1. Now, to Deploy,

firebase deploy

* if you get errors about googleapis not existing, try

cd functions
npm install googleapis

and it worked well.

1. Also, **don’t forget to share your document with Service account.**
   ( my service account looked like  `firebase-adminsdk-somekeyword@jsceno-93906.iam.gserviceaccount.com` )

2. Also, this example adds only up to 6 rows. Filling out more than 6 items in firebase realtime db also causes error.

Hope this helps many people and saves time... I spent a lot of time figuring these out.

Thank you 👍

@websiddu
Copy link

websiddu commented Jul 2, 2020

I created a detailed post out this, i have also improved the code to automatically flatten the deep JSON place it in the sheet. This works with nested json documents as well.

https://websiddu.com/blog/sync-data-from-firebase-to-google-sheets.html

@hissh05
Copy link

hissh05 commented Sep 28, 2020

I created a detailed post out this, i have also improved the code to automatically flatten the deep JSON place it in the sheet. This works with nested json documents as well.

https://websiddu.com/blog/sync-data-from-firebase-to-google-sheets.html

I

I created a detailed post out this, i have also improved the code to automatically flatten the deep JSON place it in the sheet. This works with nested json documents as well.

https://websiddu.com/blog/sync-data-from-firebase-to-google-sheets.html

I got stuck in step-4 pls help websiddu
i am working android studio i dont know node js pls help me wht to do

@hissh05
Copy link

hissh05 commented Oct 8, 2020

image
i am getting this error.............................

@websiddu
Copy link

Seems like you don't have the uptodate version of the firebase-tools try updating the firebase tools and rerun the command.

@Abrar112
Copy link

websiddu i need your help. im following your step but, i got stuck when deploy it. igot messege " Functions deploy had errors with the following functions:
SyncToSheets"
how i can solve this messege?

@Abrar112
Copy link

image

@websiddu
Copy link

The error says update your node version. In you package.json try changing to "node": 12

@demirholo
Copy link

Any idea about the above error, it does not like the script. Because of the cli version or ts version?

image

@Drewan-25
Copy link

Hellow, i got problem after firebase deploy
Screenshot_77

@guillegregoret
Copy link

I have the same problem as @Drewan-25

@Abrar112
Copy link

Abrar112 commented Mar 13, 2021

Any idea about the above error, it does not like the script. Because of the cli version or ts version?

image

@guillegregoret and @Drewan-25

i think you must install this npm install firebase-admin firebase-functions googleapis lodash –save. in functions folder

@guillegregoret
Copy link

Any idea about the above error, it does not like the script. Because of the cli version or ts version?
image

@guillegregoret and @Drewan-25

i think you must install this npm install firebase-admin firebase-functions googleapis lodash –save. in functions folder

Thanks!!! It deployed correctly in the first try

@seanho96
Copy link

Is this only possible with Firebase's blaze plan? Anyone has experience with how the charges are like for pretty small scale projects (< 1000 calls)?

image

@chirag9027
Copy link

chirag9027 commented Jul 12, 2021

can i do vice versa?

have you find any solution , if yes than please suggest

@iamuseful
Copy link

Any idea about the above error, it does not like the script. Because of the cli version or ts version?
image

@guillegregoret and @Drewan-25
i think you must install this npm install firebase-admin firebase-functions googleapis lodash –save. in functions folder

Thanks!!! It deployed correctly in the first try

I had the same challenge...
I realized that it was caused by dependencies versions..... in package.json

I used the following versions, and it worked afterwards

{
"name": "functions",
"description": "Cloud Functions for Firebase",
"scripts": {
"serve": "firebase emulators:start --only functions",
"shell": "firebase functions:shell",
"start": "npm run shell",
"deploy": "firebase deploy --only functions",
"logs": "firebase functions:log"
},
"engines": {
"node": "10"
},
"main": "index.js",
"dependencies": {
"firebase-admin": "^9.8.0",
"firebase-functions": "^3.14.1",
"googleapis": "^39.2.0",
"lodash": "^4.17.21"
},
"devDependencies": {
"firebase-functions-test": "^0.1.6"
},
"private": true
}

@loobeet
Copy link

loobeet commented Jul 22, 2022

Hi all, thanks for all these inputs. I succeeded to create an automatic sync with one database. I have a second data base with more than 26 columns, does anyone know how we can create a log to replicate the Google Sheets algorithm of range generation ?
Thanks a lot !

@skfaisal93
Copy link

Functions deploy had errors with the following functions:
        SyncToSheets(us-central1)

I am getting this error while deploying the function. Here is my package.json

{
  "name": "functions",
  "description": "Cloud Functions for Firebase",
  "scripts": {
    "serve": "firebase emulators:start --only functions",
    "shell": "firebase functions:shell",
    "start": "npm run shell",
    "deploy": "firebase deploy --only functions",
    "logs": "firebase functions:log"
  },
  "engines": {
    "node": "12"
  },
  "main": "index.js",
  "dependencies": {
    "firebase-admin": "^9.8.0",
    "firebase-functions": "^3.14.1"
  },
  "devDependencies": {
    "firebase-functions-test": "^0.2.0"
  },
  "private": true
}

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