Skip to content

Instantly share code, notes, and snippets.

@jerryOkafor
Forked from CodingDoug/README.md
Created December 12, 2017 16:19
Show Gist options
  • Save jerryOkafor/02aa9f1b658a1671416db4df568c98f0 to your computer and use it in GitHub Desktop.
Save jerryOkafor/02aa9f1b658a1671416db4df568c98f0 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
// 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.
// Watch this code in action in this tweet:
// https://twitter.com/CodingDoug/status/940022568089554944
// 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; require() it to "serviceAccount".
//
// 3. Create a spreadsheet in Drive; rename the first worksheet 'Scores';
// add Player and Score headers in row 1.
//
// 4. Share it with edit access to the email address of your service acct.
//
// 5. Copy the spreadsheet id (from its URL) to the spreadsheetId string.
//
// 6. `npm install firebase-admin firebase-functions googleapis lodash`
//
// 6. Deploy this (TypeScript) code.
//
// 7. Update the keys/values in your database under /scores and watch them
// get updated in the sheet!
import * as functions from 'firebase-functions'
import * as google from 'googleapis'
import * as _ from 'lodash'
const sheets = google.sheets('v4')
const spreadsheetId = 'YOUR_SPREADSHEET_ID_HERE'
const serviceAccount = require('../your_service_account_credentials.json')
const jwtClient = new google.auth.JWT(
serviceAccount.client_email,
null,
serviceAccount.private_key,
['https://www.googleapis.com/auth/spreadsheets'], // read and write sheets
null
);
const jwtAuthPromise = new Promise((resolve, reject) => {
jwtClient.authorize((err, tokens) => {
console.info("Tokens", tokens)
if (err) {
console.error(err)
reject(err)
return
}
resolve(tokens)
})
})
export const copyScoresToSheet = functions.database.ref('/scores').onUpdate(event => {
const data = event.data.val()
console.info(data)
// Sort the scores. scores is an array of arrays each containing name and score.
const scores = _.map<any, [string, number]>(data, (value, key) => [String(key), value])
scores.sort((a,b) => {return b[1] - a[1]})
return jwtAuthPromise.then(tokens => {
return new Promise((resolve, reject) => {
sheets.spreadsheets.values.update({
auth: jwtClient,
spreadsheetId: spreadsheetId,
range: 'Scores!A2:B7', // update this range of cells
valueInputOption: 'RAW',
resource: { values: scores }
}, (err, result) => {
if (err) {
console.log(err)
reject(err)
return
}
console.log(result)
resolve(result)
})
})
})
})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment