CodingDoug · August 8, 2025 20:09 · abhishek0196 · Jan 22, 2020 · abhishek0196 · Jan 22, 2020
diff --git a/firesheetz.ts b/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.

 // 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; 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 = '1_Ohp_hbH_615R21z7v9DIt4cYHwgzFcg7rRE47A3dmg'

 const serviceAccount = require('../firesheetz-yo-60d1f72013a1.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)
            })
        })
    })
 })
	// 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; 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 = '1_Ohp_hbH_615R21z7v9DIt4cYHwgzFcg7rRE47A3dmg'

	const serviceAccount = require('../firesheetz-yo-60d1f72013a1.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)
	})
	})
	})
	})
No results found