CODEWITHSUNDEEP
firebaseionic-frameworkfirebase-realtime-databaseangularfiregoogle-sheets-api
KarthikKPN
KarthikKPN

Reputation: 659

How to sync Firebase Database with Google Sheets?

I am working in an Ionic 3 project with ts to integrate Firebase into my app. The below code I used to integrate firebase with Ionic project

constructor(angFire: AngularFireDatabase){
}
 books: FirebaseListObservable<any>;

To send the data from my app to firebase, I used push method and to update entries I used update($key). Now I have all the data's in Firebase backend.

Now, how can I sync the firebase Database with Google Sheets so that each and every entry added to firebase backend has to get updated into sheets. I used a third party ZAPIER for this integration, but it would be nice if I get to learn on how to do this sync on my own.

Upon surfing, there are many tutorials to get the data's from the google sheets into Firebase. But I didn't come across any tutorials for vice versa.

I followed the below tutorial but it doesn't point to spreadsheets. https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase

Any help would be greatly appreciated!

Upvotes: 3

Views: 2886

Answers (3)

Saul Trejo
Saul Trejo

Reputation: 1

Firebase can't be used as a trigger in Zapier, only as an action, so you can't send data from it to Google Sheets.

Upvotes: -1

TrekFety
TrekFety

Reputation: 99

You can go for Zapier which is a 3rd party service through which you can easily integrate your Firebase and Google spreadsheets and vice versa. It has also got some support for google docs and other features.

https://zapier.com/zapbook/firebase/google-sheets/

Upvotes: 0

theblindprophet
theblindprophet

Reputation: 7947

I looked into importing Firebase right into Google Scripts either through the JavaScript SDK or or the REST API. Both have requirements/steps that Google Scripts cannot satisfy or that are extremely difficult to satisfy.

  • There is no foreseeable method of downloading the JavaScript SDK inside a Google Script because almost every method requires a DOM, which you don't have with a Google Sheet.
  • The REST API requires GoogleCredentials which, at a short glance, appear very difficult to get inside Google Scripts as well

So, the other option is to interact with Firebase in a true server side environment. This would be a lot of code, but here are the steps that I would take:

1) Setup a Pyrebase project so you can interact with your Firebase project via Python.

import pyrebase

config = {
    "apiKey": "apiKey",
    "authDomain": "projectId.firebaseapp.com",
    "databaseURL": "https://databaseName.firebaseio.com",
    "storageBucket": "projectId.appspot.com",
    "serviceAccount": "path/to/serviceAccountCredentials.json"
}
firebase = pyrebase.initialize_app(config)

...

db = firebase.database()
all_users = db.child("users").get()

2) Setup a Google Scripts/Sheets project as a class that can interact with your Google Sheet

from __future__ import print_function
import httplib2
import os

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage

try:
    import argparse
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'

class GoogleSheets:

    ...
    # The rest of the functions from that link can go here
    ...

    def write(self, sheet, sheet_name, row, col):
        """
            Write data to specified google sheet
        """

        if sheet == None or sheet == "":
            print("Sheet not specified.")
            return

        day = time.strftime("%m/%d/%Y")
        clock = time.strftime("%H:%M:%S")
        datetime = day + " - " + clock
        values = [[datetime]]

        spreadsheetId = sheet
        rangeName = sheet_name + "!" + str(row) + ":" + str(col)
        body = {
            'values': values
        }

        credentials = self.get_credentials()
        http = credentials.authorize(httplib2.Http())
        discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                'version=v4')
        service = discovery.build('sheets', 'v4', http=http,
                          discoveryServiceUrl=discoveryUrl)
        result = service.spreadsheets().values().update(
            spreadsheetId=spreadsheetId, range=rangeName,
            valueInputOption="RAW", body=body).execute()

3) Call the Google Sheets somewhere inside your Pyrebase project

from GoogleSheets import GoogleSheets

...

g = GoogleSheets()
g.write(<project-id>, <sheet-name>, <row>, <col>)

...

4) Set up a cron job to run the python script every so often

# every 2 minutes
*/2 * * * * /root/my_projects/file_example.py

You will need some basic server (Heroku, Digital Ocean) to run this.

This is not extensive because there is a lot of code to be written, but you could get the basics done. Makes we want to make a package now.

Upvotes: 3

Related Questions