CodingDoug · August 8, 2025 20:09 · Oct 9, 2018 · Apr 15, 2018 · Apr 15, 2018 · Dec 18, 2017
diff --git a/README.md b/README.md
@@ -22,7 +22,7 @@ See also this gist for copying in the other direction: <https://gist.github.com/
 5. Copy the spreadsheet id (from its URL) to the `spreadsheetId` string
    in the TypeScript source.
 
-6. `npm install firebase-admin firebase-functions googleapis lodash`
+6. `npm install firebase-admin firebase-functions googleapis@34 lodash`
 
 6. Deploy this (TypeScript) code.
 

diff --git a/firesheetz.ts b/firesheetz.ts
@@ -13,25 +13,23 @@
 // limitations under the License.
 
 import * as functions from 'firebase-functions'
-import { google } from 'googleapis'
 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(
-    serviceAccount.client_email,
-    null,
-    serviceAccount.private_key,
-    ['https://www.googleapis.com/auth/spreadsheets'], // read and write sheets
-    null
-)
-
-type Scores = { string: number }
 
+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()
 
@@ -45,6 +43,6 @@ export const copyScoresToSheet = functions.database.ref('/scores').onUpdate(asyn
         spreadsheetId: spreadsheetId,
         range: 'Scores!A2:B7',  // update this range of cells
         valueInputOption: 'RAW',
-        resource: { values: scores }
-    })
+        requestBody: { values: scores }
+    }, {})
 })
diff --git a/README.md b/README.md
@@ -28,3 +28,9 @@ See also this gist for copying in the other direction: <https://gist.github.com/
 
 7. 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/>
diff --git a/README.md b/README.md
@@ -12,18 +12,19 @@ See also this gist for copying in the other direction: <https://gist.github.com/
    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".
+   `functions` folder with the file name `serviceAccount.json`.
 
 3. Create a spreadsheet in Drive; rename the first worksheet 'Scores';
-   add Player and Score headers in row 1.
+   add Player and Score headers in row 1, columns A and B.
 
-4. Share it with edit access to the email address of your service acct.
+4. Share it with edit access to the email address in your service account.
 
-5. Copy the spreadsheet id (from its URL) to the spreadsheetId string.
+5. Copy the spreadsheet id (from its URL) to the `spreadsheetId` string
+   in the TypeScript source.
 
 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
+7. Update the keys/values in your database under `/scores` and watch them
    get updated in the sheet!
diff --git a/firesheetz.ts b/firesheetz.ts
@@ -13,58 +13,38 @@
 // limitations under the License.
 
 import * as functions from 'firebase-functions'
-import * as google from 'googleapis'
+import { 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 serviceAccount = require('../serviceAccount.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)
-    })
-})
+type Scores = { string: number }
+
+const jwtAuthPromise = jwtClient.authorize()
 
-export const copyScoresToSheet = functions.database.ref('/scores').onUpdate(event => {
-    const data = event.data.val()
-    console.info(data)
+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<any, [string, number]>(data, (value, key) => [String(key), value])
-    scores.sort((a,b) => {return b[1] - a[1]})
+    const scores = _.map<Scores, [string, number]>(data, (value, key) => [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)
-            })
-        })
+    await jwtAuthPromise
+    await sheets.spreadsheets.values.update({
+        auth: jwtClient,
+        spreadsheetId: spreadsheetId,
+        range: 'Scores!A2:B7',  // update this range of cells
+        valueInputOption: 'RAW',
+        resource: { values: scores }
     })
 })
diff --git a/README.md b/README.md
@@ -0,0 +1,29 @@
+# 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
+
+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!
diff --git a/firesheetz.ts b/firesheetz.ts
@@ -12,29 +12,6 @@
 // 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'

diff --git a/firesheetz.ts b/firesheetz.ts
@@ -21,7 +21,8 @@
 // 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.
+// 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.
 //
@@ -39,9 +40,9 @@ import * as google from 'googleapis'
 import * as _ from 'lodash'
 const sheets = google.sheets('v4')
 
-const spreadsheetId = '1_Ohp_hbH_615R21z7v9DIt4cYHwgzFcg7rRE47A3dmg'
+const spreadsheetId = 'YOUR_SPREADSHEET_ID_HERE'
 
-const serviceAccount = require('../firesheetz-yo-60d1f72013a1.json')
+const serviceAccount = require('../your_service_account_credentials.json')
 const jwtClient = new google.auth.JWT(
     serviceAccount.client_email,
     null,

diff --git a/firesheetz.ts b/firesheetz.ts
@@ -12,6 +12,9 @@
 // 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
 //

diff --git a/firesheetz.ts b/firesheetz.ts
@@ -0,0 +1,89 @@
+// 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.
+
+// 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)
+            })
+        })
+    })
+})