Cloudflare Worker talk to Google Sheets

I have credentials json for Google Cloud service account

This service account is allowed to read google sheet

To do so, we first need form and sign jwt token with private key of our service account

Then exchange this jwt for access token

And then we can talk to Google Sheet

Here is an example of how it may look like from bash script

#!/bin/bash

# path to our credentials file
PRIVATE_KEY_FILE_PATH=~/Downloads/credentials.json
# actually we need only email and private key from it
CLIENT_EMAIL=$(cat $PRIVATE_KEY_FILE_PATH | jq -r ".client_email")
PRIVATE_KEY=$(cat $PRIVATE_KEY_FILE_PATH | jq -r .private_key)

# now we need to form an jwt token and sign it with our private key
HEADER=$(echo -n '{"alg":"RS256","typ":"JWT"}' | base64 | tr '/+' '_-' | tr -d '=')
PAYLOAD=$(echo -n "{\"aud\":\"https://www.googleapis.com/oauth2/v4/token\",\"exp\":$(($(date +%s) + 3600)),\"iat\":$(date +%s),\"iss\":\"$CLIENT_EMAIL\",\"scope\":\"https://www.googleapis.com/auth/spreadsheets\"}" | base64 | tr '/+' '_-' | tr -d '=')
SIGNATURE=$(openssl dgst -sha256 -sign <(echo "$PRIVATE_KEY") <(printf "$HEADER.$PAYLOAD") | base64 | tr '/+' '_-' | tr -d '=')
JWT="$HEADER.$PAYLOAD.$SIGNATURE"

# then we can exchange it to access token
ACCESS_TOKEN=$(curl -s -X POST "https://www.googleapis.com/oauth2/v4/token" \
  -H "Content-Type: application/json" \
  -d '{"grant_type":"urn:ietf:params:oauth:grant-type:jwt-bearer","assertion":"'"$JWT"'"}' \
  | jq -r .access_token)

# and finally we can fetch data from Google Sheets using the access token
SPREADSHEET_ID="1Eac00000-00000000000000000000000000-0-0Qu9s"
RANGE='Sheet1!A:B'
curl -s -H "Authorization: Bearer $ACCESS_TOKEN" "https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/$RANGE" | jq .

Starting point was found in this gist

Now, we can try do the same in nodejs

import crypto from 'crypto'
import fs from 'fs'

// actually we need only client email and private key fields from credentials.json
const { client_email, private_key } = JSON.parse(fs.readFileSync('/Users/mac/Downloads/credentials.json', 'utf8'))

// form our jwt token
var header = Buffer.from(JSON.stringify({ typ: 'JWT', alg: 'RS256' })).toString('base64url')
var payload = Buffer.from(
  JSON.stringify({
    aud: 'https://www.googleapis.com/oauth2/v4/token',
    iat: Math.floor(Date.now() / 1000) - 10,
    exp: Math.floor(Date.now() / 1000) + 600,
    iss: client_email,
    scope: 'https://www.googleapis.com/auth/spreadsheets',
  })
).toString('base64url')
// sign it with our private key
const signature = crypto.createSign('RSA-SHA256').update(`${header}.${payload}`).sign(private_key, 'base64url')
var jwt = `${header}.${payload}.${signature}`
// console.log('jwt:', jwt)

// exchange it for access token
var { access_token } = await fetch(`https://www.googleapis.com/oauth2/v4/token`, {
  method: 'POST',
  headers: {
    'content-type': 'application/json',
  },
  body: JSON.stringify({
    grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
    assertion: jwt,
  }),
}).then(r => r.json())
// console.log('access_token:', access_token)

// get google sheet data
const spreadsheetId = '1Eac00000-00000000000000000000000000-0-0Qu9s'
const range = 'Sheet1!A:B'
const { values } = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`, {
  headers: { authorization: `Bearer ${access_token}` },
}).then(res => res.json())
console.log(values)

Taken from previous notes about authenticating github app

Everything simple so far, implementation is always the same and pretty straight forward, but the cloudflare works runtime is different it is more like browser rather than nodejs, but still doable

/**
 * Converts input string or ArrayBuffer to base64url encoded string
 * @param {string | ArrayBuffer} input
 */
function b64(input) {
  const uint8Array = typeof input === 'string' ? new TextEncoder().encode(input) : new Uint8Array(input)
  return btoa(String.fromCharCode(...uint8Array))
    .replace(/=/g, '')
    .replace(/\+/g, '-')
    .replace(/\//g, '_')
}

/**
 * Converts input string to ArrayBuffer
 * @param {string} str
 * @returns {ArrayBuffer}
 */
function str2ab(str) {
  const buf = new ArrayBuffer(str.length)
  const bufView = new Uint8Array(buf)
  for (let i = 0; i < str.length; i++) {
    bufView[i] = str.charCodeAt(i)
  }
  return buf
}

// data from credentials.json that we need to sign jwt token
const client_email = 'your_service_account_email@your_project.iam.gserviceaccount.com'
const private_key = '-----BEGIN PRIVATE KEY-----\n\n-----END PRIVATE KEY-----\n'

export default {
  async fetch(req) {
    // import our private key
    const key = await crypto.subtle.importKey(
      'pkcs8',
      str2ab(atob(private_key.replace('-----BEGIN PRIVATE KEY-----', '').replace('-----END PRIVATE KEY-----', ''))),
      { name: 'RSASSA-PKCS1-v1_5', hash: 'SHA-256' },
      false,
      ['sign']
    )

    // form jwt token
    const header = b64(JSON.stringify({ typ: 'JWT', alg: 'RS256' }))
    const payload = b64(
      JSON.stringify({
        aud: 'https://www.googleapis.com/oauth2/v4/token',
        iat: Math.floor(Date.now() / 1000) - 10,
        exp: Math.floor(Date.now() / 1000) + 600,
        iss: client_email,
        scope: 'https://www.googleapis.com/auth/spreadsheets',
      })
    )
    // sign it with private key
    const signature = await crypto.subtle.sign('RSASSA-PKCS1-v1_5', key, new TextEncoder().encode(`${header}.${payload}`))
    const jwt = `${header}.${payload}.${b64(signature)}`

    // exchange jwt for access token
    var { access_token } = await fetch(`https://www.googleapis.com/oauth2/v4/token`, {
      method: 'POST',
      headers: { 'content-type': 'application/json' },
      body: JSON.stringify({
        grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
        assertion: jwt,
      }),
    }).then(r => r.json())
    // console.log('access_token:', access_token)
    // return new Response(access_token)

    // get google sheet data
    const spreadsheetId = '1Eac00000-00000000000000000000000000-0-0Qu9s'
    const range = 'Sheet1!A:B'
    const { values } = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`, {
      headers: { authorization: `Bearer ${access_token}` },
    }).then(res => res.json())
    return new Response(JSON.stringify(values, null, 2), { headers: { 'content-type': 'application/json' } })
  },
}

There is an typescript library doing that in case of need something more complex

But in my case it is good enough to not depend even on it

With that we may have small cloudflare worker that will allow us to retrieve google sheets data as an rest endpoint, but for this to work from browser we need add few more things like cors, but this one is not a problem at all

Alternative, and probably much simpler approach would be to host such function right in Google Cloud - there it will run under service account of othe box and token retrive is automated behind the sceene with client library, aka

const functions = require('@google-cloud/functions-framework')
const { google } = require('googleapis')

const spreadsheetId = '1Eac00000-00000000000000000000000000-0-0Qu9s'
const range = 'Sheet1!A:B'

async function getGoogleClient() {
  const auth = new google.auth.GoogleAuth({ scopes: ['https://www.googleapis.com/auth/spreadsheets'] })
  return google.sheets({
    version: 'v4',
    auth: await auth.getClient(),
  })
}

functions.http('handler', async (req, res) => {
  const client = await getGoogleClient()
  const result = await client.spreadsheets.values.get({ spreadsheetId, range })
  return res.json(result.data.values)
})

But with this examples it is possible to retrieve such data from anywhere

For an example of how to do it via powershell, check out github app example