CodingDoug/README.md

## README.md

      
    Raw
  

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


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


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


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


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


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


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


Deploy this (TypeScript) code.


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


Helpful documentation


https://firebase.google.com/docs/functions/database-events
https://developers.google.com/sheets/api/
http://google.github.io/google-api-nodejs-client/


## firesheetz.ts
// 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 }
    }, {})
})
	// 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 }
	}, {})
	})