Skip to content

Instantly share code, notes, and snippets.

@korof

korof/firesheetz.ts

Forked from CodingDoug/README.md
Created December 15, 2017 19:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save korof/69e8268c262d3d6c35fd66fd639ffec1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save korof/69e8268c262d3d6c35fd66fd639ffec1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @CodingDoug CodingDoug revised this gist Dec 11, 2017. 1 changed file with 4 additions and 3 deletions.
    7 changes: 4 additions & 3 deletions firesheetz.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -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,
  2. @CodingDoug CodingDoug revised this gist Dec 11, 2017. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions firesheetz.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -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
    //
  3. @CodingDoug CodingDoug created this gist Dec 11, 2017.
    89 changes: 89 additions & 0 deletions firesheetz.ts
    View file
    Original file line number Diff line number Diff line change
    @@ -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)
    })
    })
    })
    })