How to sync Firebase database with Google Sheets?
I am working on an Ionic 3 project with ts to integrate Firebase into my app. In the below code I used to integrate firebase with Ionic project
constructor(angFire: AngularFireDatabase){
}
books: FirebaseListObservable<any>;
To send data from my app to firebase I used the method push
and updated the records I was using update($key)
. Now I have all the data in my Firebase firewall.
Now how can I sync the firebase database with Google Sheets so that every record added to the firebase backend is updated to sheets. I used a third party ZAPIER for this integration , but it would be nice if I could learn how to do this sync myself.
When surfing, there are many tutorials to get data from google sheets to Firebase. But I have not come across any tutorials for the opposite.
I followed the tutorial below 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!
source to share
I looked at importing Firebase directly into google scripts, either via the JavaScript SDK or REST API. Both have requirements / steps that Google Scripts cannot meet or are extremely difficult to meet.
- There is no predictable method for loading the JavaScript SDK inside Google Script because almost every method requires a DOM that you don't have with Google Sheet.
- The REST API requires
GoogleCredentials
, which at a short glance turns out to be very difficult to get into google scripts, and
So another option is to interact with Firebase in a real server environment. This is going to be a lot of code, but here are the steps I would take:
1) Set up your Pyrebase project so you can interact with your Firebase project using 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) Set Google Project / Scripts 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 Google Sheets somewhere inside the Pyrebase project
from GoogleSheets import GoogleSheets
...
g = GoogleSheets()
g.write(<project-id>, <sheet-name>, <row>, <col>)
...
4) Set up a cron job to run python Script quickly
# every 2 minutes
*/2 * * * * /root/my_projects/file_example.py
For this, you need a base server (Heroku, Digital Ocean).
This is not extensive because there is a lot of code to write, but you can get the basics. We want to make the package now.
source to share