Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 }
}, {})
})
@zinderud

This comment has been minimized.

Copy link

@zinderud zinderud commented Apr 16, 2018

Thnks

@robe007

This comment has been minimized.

Copy link

@robe007 robe007 commented Apr 25, 2018

Works like a charm ! Excellent one

@JoceM

This comment has been minimized.

Copy link

@JoceM JoceM commented May 1, 2018

Hi, when Google Sheet API is activated, a client_secret json file is created. Do we need to create a service account file additional to this client_secret file ? If so, where should the client_secret file be copied in the project ? Thanks for the great support.

@jehan96

This comment has been minimized.

Copy link

@jehan96 jehan96 commented Jun 9, 2018

can i do vice versa?

@hmm29

This comment has been minimized.

Copy link

@hmm29 hmm29 commented Jun 18, 2018

Thank you for this @CodingDoug

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jun 29, 2018

Sorry got a little lost and new to this-where and how do we deploy the "firesheetz.ts"?

@M1Ergash

This comment has been minimized.

Copy link

@M1Ergash M1Ergash commented Jul 5, 2018

hello, dear programmers,
can you help me to get it worked please ?
I confused in instruction, but I really need for my work.
please spend your couple of minutes for me for instructions.
thanks in advance

@yidan

This comment has been minimized.

Copy link

@yidan yidan commented Jul 26, 2018

Thank you so much for the code and the instruction! I got this error message when deploying the code to Firebase:
error TS2345: Argument of type '{ auth: JWT; spreadsheetId: string; range: string; valueInputOption: string; resource: { values: ...' is not assignable to parameter of type 'BodyResponseCallback<Schema$UpdateValuesResponse>'.
Object literal may only specify known properties, and 'auth' does not exist in type 'BodyResponseCallback<Schema$UpdateValuesResponse>'

Any clue what went wrong?

@DisplayedAim

This comment has been minimized.

Copy link

@DisplayedAim DisplayedAim commented Aug 5, 2018

I'm getting the exact same error as above ^

@kiyama14

This comment has been minimized.

Copy link

@kiyama14 kiyama14 commented Aug 16, 2018

any luck with the error above?^^

@Kiyos

This comment has been minimized.

Copy link

@Kiyos Kiyos commented Aug 23, 2018

Having the same problem with auth: jwtClient. I’m using googleapis@33.0.0.

@itjustwerks

This comment has been minimized.

Copy link

@itjustwerks itjustwerks commented Sep 10, 2018

Had this error too. Looks like you have to set up the request as a separate object and pass it into the sheets.spreadsheets.values.update method. Also, when deploying, I'm not sure how to get Firebase's Cloud Functions to pick up functions defined in files other than src/index.ts. I ended up moving the code over to that file to get it to deploy correctly. Here's the updated code: https://www.codepile.net/pile/JrGLWoEo

@ofmarconi

This comment has been minimized.

Copy link

@ofmarconi ofmarconi commented Sep 13, 2018

Had this error too. Looks like you have to set up the request as a separate object and pass it into the sheets.spreadsheets.values.update method. Also, when deploying, I'm not sure how to get Firebase's Cloud Functions to pick up functions defined in files other than src/index.ts. I ended up moving the code over to that file to get it to deploy correctly. Here's the updated code: https://www.codepile.net/pile/JrGLWoEo

But can you explain better? Give an example so we can understand what goes wrong?

Or rather, create a new tutorial, this does not work: /

@ofmarconi

This comment has been minimized.

Copy link

@ofmarconi ofmarconi commented Sep 13, 2018

@CodingDoug, can you help us? Amateurs are not understanding step by step.

@CodingDoug

This comment has been minimized.

Copy link
Owner Author

@CodingDoug CodingDoug commented Oct 9, 2018

Hey everyone, sorry if you had problems with this sample. Apparently the googleapis module APIs changed when I first made this with googleapis@27. I've updated now for googleapis@34 - this should be working again.

@lloydtozvireva

This comment has been minimized.

Copy link

@lloydtozvireva lloydtozvireva commented Oct 27, 2018

@CodingDoug. i really see this is a good piece of work na dits exactly what i was loking for.

i have an andoid app posting data in firebase and i need this concept to enable extration from Frebase to google sheets. My problem is i have done step 1. And from there i am lost. i cant find the functions folder, where is it in firebase or i need to create it on my local machine. can you supply a VIDEO/ tutorial on these steps. Thank you. i was almost happy when i saw your tweet only to see its and GIF not a video hehehe

@muratgungor

This comment has been minimized.

Copy link

@muratgungor muratgungor commented Dec 27, 2018

it didnt work. fuck off

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Jul 9, 2019

it didnt work. fuck off

what happened?

@alexandermckay

This comment has been minimized.

Copy link

@alexandermckay alexandermckay commented Aug 30, 2019

I have just about got this figured out, syncing in both directions.
I'll post a link to a tutorial video once I am done.

@vaibhavkumar049

This comment has been minimized.

Copy link

@vaibhavkumar049 vaibhavkumar049 commented Sep 16, 2019

@alexandermckay wrote the blog?

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Sep 16, 2019

@alexandermckay wrote the blog?

Do you need any help? I had done with the help of this tutorial from firestore to sheet and it works fine.

@vaibhavkumar049

This comment has been minimized.

Copy link

@vaibhavkumar049 vaibhavkumar049 commented Sep 21, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

@mxdigitalservices

This comment has been minimized.

Copy link

@mxdigitalservices mxdigitalservices commented Oct 25, 2019

how to write data in other celds??

@simoubouss

This comment has been minimized.

Copy link

@simoubouss simoubouss commented Dec 8, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Dec 9, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

which database are you using firestore or realtime?

@simoubouss

This comment has been minimized.

Copy link

@simoubouss simoubouss commented Dec 9, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

which database are you using firestore or realtime?

i am using Realtime ..i rewrite the code , it's work , but i need to store each document in a row such as [name ,mail,phone number... ]

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Dec 9, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

which database are you using firestore or realtime?

i am using Realtime ..i rewrite the code , it's work , but i need to store each document in a row such as [name ,mail,phone number... ]

It would be helpful if you can share your code snippets of what all things you had done.

@simoubouss

This comment has been minimized.

Copy link

@simoubouss simoubouss commented Dec 9, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

which database are you using firestore or realtime?

i am using Realtime ..i rewrite the code , it's work , but i need to store each document in a row such as [name ,mail,phone number... ]

It would be helpful if you can share your code snippets of what all things you had done.
it's like what you wrote on your blog .. but i will create it in Realtime database

@simoubouss

This comment has been minimized.

Copy link

@simoubouss simoubouss commented Dec 9, 2019

if anyone wants to update all docs from firestore use .onWrite method and use wildcard

please how can i do that , i want to store a lot of records from firebase into googlesheets

which database are you using firestore or realtime?

i am using Realtime ..i rewrite the code , it's work , but i need to store each document in a row such as [name ,mail,phone number... ]

It would be helpful if you can share your code snippets of what all things you had done.
it's like what you wrote on your blog .. but i will create it in Realtime database
i use your code .. it's work .. thaank you but instead i will use just firestore

@ArtosProjects

This comment has been minimized.

Copy link

@ArtosProjects ArtosProjects commented Jan 19, 2020

can you please make a tutorial video for this.
Thanks in advance.

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Jan 20, 2020

can you please make a tutorial video for this.
Thanks in advance.

Which one do you want realtime database or the firestore?

@ArtosProjects

This comment has been minimized.

Copy link

@ArtosProjects ArtosProjects commented Jan 22, 2020

can you please make a tutorial video for this.
Thanks in advance.

Which one do you want realtime database or the firestore?

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

@ArtosProjects

This comment has been minimized.

Copy link

@ArtosProjects ArtosProjects commented Jan 22, 2020

@abhishek0196

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented Jan 22, 2020

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

@JaeHwanWO

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 26, 2020

@abhishek0196

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 26, 2020

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

@JaeHwanWO

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 26, 2020

Is anyone facing error requested entity was not found?

@JaeHwanWO

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

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

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

@JaeHwanWO

This comment has been minimized.

Copy link

@JaeHwanWO JaeHwanWO commented Feb 27, 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.

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

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

@nabilacsrn

This comment has been minimized.

Copy link

@nabilacsrn nabilacsrn commented May 5, 2020

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

@abhishek0196

This comment has been minimized.

Copy link

@abhishek0196 abhishek0196 commented May 5, 2020

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

This comment has been minimized.

Copy link

@Jeet-Metapercept Jeet-Metapercept commented Jun 18, 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.

Thank you 👍

@websiddu

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

@hissh05 hissh05 commented Oct 8, 2020

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

@websiddu

This comment has been minimized.

Copy link

@websiddu websiddu commented Oct 26, 2020

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

@Abrar112

This comment has been minimized.

Copy link

@Abrar112 Abrar112 commented Nov 20, 2020

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

This comment has been minimized.

Copy link

@Abrar112 Abrar112 commented Nov 20, 2020

image

@websiddu

This comment has been minimized.

Copy link

@websiddu websiddu commented Nov 21, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.