{
"path": "/3ma5kuezmb22a",
"site": "at://did:plc:ml6hwrktvr7r5nhxs7f7eoz4/site.standard.publication/3m6prszcmtk2z",
"tags": [],
"$type": "site.standard.document",
"title": "A website powered by Cloudflare Workers and Google Sheets",
"content": {
"$type": "pub.leaflet.content",
"pages": [
{
"id": "019b2807-4489-7bbf-ad2a-be7338c223e6",
"$type": "pub.leaflet.pages.linearDocument",
"blocks": [
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Yesterday I shared a post about creating a cloudflare worker to access data from a Google Sheet, to power a small website."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.bskyPost",
"postRef": {
"cid": "bafyreicrls7mtenftmbgwuglupeujqwstalyyxkvzo5d5naudgfwxokblu",
"uri": "at://did:plc:ml6hwrktvr7r5nhxs7f7eoz4/app.bsky.feed.post/3ma3ct33bis2z"
}
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Time to write up a little bit about how I did it. The reason for doing this was to abstract the connection to Google Sheets away from the frontend."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "I play golf with a few friends, and we keep a Google Sheet documenting how well we've each played over the past 11 years."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "While I could have hooked up the Google Sheets API in the browser executing JavaScript, this would have exposed the JSON Private Key."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "This let's the Google Sheet continue to function as-is, and let's us share our stats on public facing web pages too – for fun."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "For this post, I've simplified the data quite a bit, but the concept remains the same."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Let's talk a little about what's involved here from a technical perspective."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 44,
"byteStart": 27
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
},
{
"index": {
"byteEnd": 62,
"byteStart": 50
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
},
{
"index": {
"byteEnd": 99,
"byteStart": 71
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
},
{
"index": {
"byteEnd": 135,
"byteStart": 109
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
},
{
"index": {
"byteEnd": 221,
"byteStart": 209
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
},
{
"index": {
"byteEnd": 281,
"byteStart": 260
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
}
],
"plaintext": "This is about connecting a Cloudflare Worker to a Google Sheet using a Google Cloud Service Account. It uses Cloudflare Worker Secrets to store the the JSON Private Key, and then when it finds a result in the Google Sheet it caches the result for 5 minutes in Cloudflare Workers KV."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "All in, this should take a lot less time than it did to write about – I'd set aside about 30 minutes or so, maybe a little longer if you don't already have a Cloudflare or Google account."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Install Cloudflare Wrangler"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "We'll need to install Cloudflare's command line tool (CLI) for this."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "npm i -D wrangler@latest"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Make a Cloudflare worker"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 43,
"byteStart": 26
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
}
]
}
],
"plaintext": "We'll need to start a new Cloudflare Worker project. In an empty directory, run the following command."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "shellscript",
"plaintext": "npx wrangler init"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 19,
"byteStart": 2
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
},
{
"$type": "pub.leaflet.richtext.facet#italic"
}
]
}
],
"plaintext": "A Create Cloudflare workflow will present in terminal."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "As you work through the workflow:"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 29,
"byteStart": 10
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "1. Select Hello World example"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 21,
"byteStart": 10
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "2. Select Worker only"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 20,
"byteStart": 10
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "3. Select TypeScript"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Once all dependencies have all been installed, say yes to Git, and Deploy."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Once it's deployed, a new tab will open in your browser, pointing to your worker's URL – \"Hello World!\""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Make a Google Cloud Console Service Account"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 39,
"byteStart": 6
},
"features": [
{
"uri": "https://console.cloud.google.com/",
"$type": "pub.leaflet.richtext.facet#link"
}
]
}
],
"plaintext": "Go to https://console.cloud.google.com/"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 27,
"byteStart": 11
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
},
{
"$type": "pub.leaflet.richtext.facet#italic"
}
]
}
],
"plaintext": "Search for Service Accounts"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Give it a meaningful Service account name, and Service account ID (which becomes the email address)."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Copy the email address – in a couple of steps time, you'll need to share the Google Sheet this email address in the Google Sheet."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Click Create and continue. "
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Skip the Permissions and Principals with access, and click Done."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "On the Service accounts page, click the email address of the new Service account that you just created."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 14,
"byteStart": 10
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
},
{
"$type": "pub.leaflet.richtext.facet#italic"
}
]
}
],
"plaintext": "Click the Keys tab."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "From the Add key dropdown menu, select Create new key. Select JSON."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "The Private Key should be downloaded automatically in your browser – we'll come back to the contents of this file later."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Enable Google Sheets API access"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 42,
"byteStart": 9
},
"features": [
{
"uri": "https://console.cloud.google.com/",
"$type": "pub.leaflet.richtext.facet#link"
}
]
}
],
"plaintext": "Still in https://console.cloud.google.com/, search for Google Sheets API and Enable the API access."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "You'll need this to enable the Service Acccount to use the Google Sheets API. If you don't do this, you'll have an Access Denied error later in the worker!"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Add the Service Account to the Google Sheet"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 102,
"byteStart": 96
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "Time to go to your Google Sheet. under Share, add the email address of the Service Account as a Viewer to the sheet."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 75,
"byteStart": 69
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
},
{
"index": {
"byteEnd": 107,
"byteStart": 103
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
},
{
"$type": "pub.leaflet.richtext.facet#italic"
}
]
},
{
"index": {
"byteEnd": 142,
"byteStart": 123
},
"features": [
{
"uri": "https://xkcd.com/327/",
"$type": "pub.leaflet.richtext.facet#link"
}
]
}
],
"plaintext": "Following the Principle of Least Privilege, we're ensuring that as a Viewer, the Service Account can't edit the sheet, and Little Bobby Tables away all the data"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"src": "https://imgs.xkcd.com/comics/exploits_of_a_mom_2x.png",
"$type": "pub.leaflet.blocks.website",
"title": "",
"description": "",
"previewImage": {
"$type": "blob",
"ref": {
"$link": "bafkreid7ndctli4z6wnxgkeg2aqvugsqzrjmbnmo4qo7j75hx6kvpmxbh4"
},
"mimeType": "image/png",
"size": 21966
}
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Make a Cloudflare Secret"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Now we have the Service Account setup, provisioned access to the Google Sheets API, and added the Service Account as a Viewer to the Google Sheet, we can use the Service Account in the Worker to access the Google Sheet!"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "To do that, it's time to put our Private Key as a Secret into Cloudflare."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "In your favourite text editor, open the JSON Private Key file that was downloaded when we created the JSON Key earlier in the Google Cloud Console."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.blockquote",
"facets": [
{
"index": {
"byteEnd": 167,
"byteStart": 7
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#bold"
},
{
"$type": "pub.leaflet.richtext.facet#italic"
}
]
}
],
"plaintext": "ℹ️ You will need to remove the linebreaks from the JSON file, and ensure the contents is all on a single line, or you'll get newline errors when loading the worker"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Copy the contents to the clipboard."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Next, we'll run the following command:"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "shellscript",
"plaintext": "npx wrangler secret put GOOGLE_SERVICE_ACCOUNT_KEY"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Paste in the value of the (now in a single line) JSON Private Key from the Service Account."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "You'll see a host of asterisks whizzing by in your terminal as the content of the clipboard is pasted in."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Updating the Cloudflare Worker to access Google Sheets"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "First, let's add a constant for the Spreadsheet ID, and the interface to describe the JSON Private Key we just created as a secret above. This interface allows TypeScript to reason about the shape of the JSON object from the secret."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "// Google Sheets configuration\nconst SPREADSHEET_ID = ''; // This is the string between \"https://docs.google.com/spreadsheets/d/\" and \"/edit?gid=0#gid=0\"\n\n/**\n * Google Service Account JSON key structure\n */\ninterface GoogleServiceAccountKey {\n\ttype: string;\n\tproject_id: string;\n\tprivate_key_id: string;\n\tprivate_key: string;\n\tclient_email: string;\n\tclient_id: string;\n\tauth_uri: string;\n\ttoken_uri: string;\n\tauth_provider_x509_cert_url: string;\n\tclient_x509_cert_url: string;\n\tuniverse_domain: string;\n}"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Next, let's create a short lived access token from our JSON Private Key."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "/**\n * Get access token from Google OAuth\n */\nasync function getGoogleAccessToken(serviceAccountKey: GoogleServiceAccountKey): Promise<string> {\n\tconst jwt = await generateGoogleJWT(serviceAccountKey);\n\t\n\tconst response = await fetch('https://oauth2.googleapis.com/token', {\n\t method: 'POST',\n\t headers: {\n\t\t'Content-Type': 'application/x-www-form-urlencoded'\n\t },\n\t body: `grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=${jwt}`\n\t});\n \n\tif (!response.ok) {\n\t throw new Error(`Failed to get access token: ${response.statusText}`);\n\t}\n \n\tconst data = await response.json() as { access_token: string };\n\treturn data.access_token;\n}\n \n\n/**\n * Generate JWT token for Google Service Account authentication\n */\nasync function generateGoogleJWT(serviceAccountKey: GoogleServiceAccountKey): Promise<string> {\n\tconst header = {\n\t alg: 'RS256',\n\t typ: 'JWT',\n\t kid: serviceAccountKey.private_key_id\n\t};\n \n\tconst now = Math.floor(Date.now() / 1000);\n\tconst payload = {\n\t iss: serviceAccountKey.client_email,\n\t scope: 'https://www.googleapis.com/auth/spreadsheets.readonly',\n\t aud: 'https://oauth2.googleapis.com/token',\n\t exp: now + 60,\n\t iat: now\n\t};\n \n\t// Base64url encode header and payload\n\tconst base64urlEncode = (obj: object): string => {\n\t return btoa(JSON.stringify(obj))\n\t\t.replace(/\\+/g, '-')\n\t\t.replace(/\\//g, '_')\n\t\t.replace(/=/g, '');\n\t};\n \n\tconst encodedHeader = base64urlEncode(header);\n\tconst encodedPayload = base64urlEncode(payload);\n\tconst unsignedToken = `${encodedHeader}.${encodedPayload}`;\n \n\t// Import private key and sign\n\tconst privateKey = serviceAccountKey.private_key;\n\tconst pemHeader = '-----BEGIN PRIVATE KEY-----';\n\tconst pemFooter = '-----END PRIVATE KEY-----';\n\t\n\t// Extract and clean the base64 content\n\tconst pemContents = privateKey\n\t .replace(pemHeader, '')\n\t .replace(pemFooter, '')\n\t .replace(/\\\\n/g, '')\n\t .replace(/\\n/g, '')\n\t .replace(/\\s/g, '');\n\t\n\tconst binaryKey = Uint8Array.from(atob(pemContents), c => c.charCodeAt(0));\n\t\n\tconst cryptoKey = await crypto.subtle.importKey(\n\t 'pkcs8',\n\t binaryKey,\n\t {\n\t\tname: 'RSASSA-PKCS1-v1_5',\n\t\thash: 'SHA-256'\n\t },\n\t false,\n\t ['sign']\n\t);\n \n\tconst signature = await crypto.subtle.sign(\n\t 'RSASSA-PKCS1-v1_5',\n\t cryptoKey,\n\t new TextEncoder().encode(unsignedToken)\n\t);\n \n\tconst base64urlSignature = btoa(String.fromCharCode(...new Uint8Array(signature)))\n\t .replace(/\\+/g, '-')\n\t .replace(/\\//g, '_')\n\t .replace(/=/g, '');\n \n\treturn `${unsignedToken}.${base64urlSignature}`;\n }"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Next up, let's fetch some data from the sheets – we're almost at the good parts!"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "/**\n * Fetch data from Google Sheets\n */\nasync function fetchSheetData(accessToken: string, range: string): Promise<string[][]> {\n\tconst url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/${encodeURIComponent(range)}`;\n\t\n\tconst response = await fetch(url, {\n\t headers: {\n\t\t'Authorization': `Bearer ${accessToken}`\n\t }\n\t});\n \n\tif (!response.ok) {\n\t const errorBody = await response.text();\n\t console.error('Google Sheets API error:', response.status, errorBody);\n\t throw new Error(`Failed to fetch sheet data: ${response.statusText} - ${errorBody}`);\n\t}\n \n\tconst data = await response.json() as { values?: string[][] };\n\treturn data.values || [];\n}"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Now we've got all the functions there, time to stitch it together:"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "export default {\n\tasync fetch(request, env, ctx): Promise<Response> {\n\t\t// Parse service account key\n\t\tconst serviceAccountKey = JSON.parse(env.GOOGLE_SERVICE_ACCOUNT_KEY);\n\n\t\t// Get access token\n\t\tconst accessToken = await getGoogleAccessToken(serviceAccountKey);\n\n\t\tconst data = await fetchSheetData(accessToken, 'Handles!A1:B');\n\n\t\treturn new Response(data.toString());\n\t},\n} satisfies ExportedHandler<Env>;"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "When we test the response, we should expect to see the following response"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "csv",
"plaintext": "Bluesky Handle,Attending ATmosphere Conf?,alex.mcroberts.me,TRUE"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 2,
"facets": [],
"plaintext": "Adding Caching with Cloudflare Workers KV"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "We'll likely want to cache our response, so we don't hit the Google Sheet, and max out any rate limits there."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Make a Cloudflare Workers KV Namespace"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "In their own words, Cloudflare describes Workers KV as a data storage that allows you to store and retrieve data globally. KV as a Key Value store, and is currently available on the "
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "shellscript",
"plaintext": "npx wrangler kv namespace create handles"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": "Accept the default options to let Wrangler add the namespacing binding to the Worker configuration, the default binding name, and allow the local development environment to use a local resource."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Hooking up the KV as a cache"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 49,
"byteStart": 44
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "We'll break this into a few sections in the fetch function. Note that we've also replaced the plain JSON response with a couple of functions that generate HTML as a response."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Extract the handle from the URL"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "const url = new URL(request.url);\nconst handle = url.pathname.replace(/^\\/+|\\/+$/g, '');\nconsole.log(`Looking up handle: ${handle}`);\n\n// Ignore favicon and other non-handle requests\nif (!handle || handle === 'favicon.ico') {\n\treturn new Response(generate404HTML(handle), {\n\t\tstatus: 404,\n\t\theaders: { 'Content-Type': 'text/html' }\n\t});\n}"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Check if the handle is in our KV cache"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "let handleData = null;\nif (env.handles) {\n\ttry {\n\t\tconst cachedData = await env.handles.get(`handle:${handle}`);\n\t\tif (cachedData) {\n\t\t\thandleData = JSON.parse(cachedData);\n\t\t\tconsole.log(`Cache hit for handle: ${handle}`);\n\t\t}\n\t} catch (cacheError) {\n\t\tconsole.error('Cache read error:', cacheError);\n\t\t// Continue without cache\n\t}\n}"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "If it's not in the KV Cache, pull from Google Sheets"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "if (!handleData) {\n\tconsole.log(`Cache miss for handle: ${handle}, fetching from Google Sheets`);\n\n\tconst serviceAccountKey = JSON.parse(env.GOOGLE_SERVICE_ACCOUNT_KEY);\n\tconst accessToken = await getGoogleAccessToken(serviceAccountKey);\n\n\t// Fetch handle data from the Google Sheet\n\tconst handleData = await lookupHandleInHandles(accessToken, handle);\n\tif (!handleData) {\n\t\treturn new Response(JSON.stringify({error: 'Handle not found'}), {\n\t\t\tstatus: 404,\n\t\t\theaders: { 'Content-Type': 'application/json' }\n\t\t});\n\t}\n\tconsole.log(`Found handle: ${handleData} for handle: ${handle}`);\n\n\t// Cache the result (if KV is configured)\n\tif (env.handles) {\n\t\ttry {\n\t\t\tawait env.handles.put(\n\t\t\t\t`handle:${handle}`,\n\t\t\t\tJSON.stringify(handleData),\n\t\t\t\t{ expirationTtl: SHEET_CACHE_TTL }\n\t\t\t);\n\t\t\tconsole.log(`Cached data for handle: ${handle}`);\n\t\t} catch (cacheError) {\n\t\t\tconsole.error('Cache write error:', cacheError);\n\t\t\t// Continue responding to the request without caching\n\t\t}\n\t}\n}"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Then send our response"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.code",
"language": "typescript",
"plaintext": "return new Response(generateHandleHTML(handleData), {\n\theaders: { 'Content-Type': 'text/html' }\n});"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [],
"plaintext": ""
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Screenshots"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 162,
"byteStart": 153
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "Two screenshots showing the end result of our test, and the Google Sheet that served as our data source. Note, I've replaced some parts of the URLs with [deleted]."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"alt": "The Cloudflare worker page. Text reads \"alex.mcroberts.me. Is handle attending ATmosphere Conf 2026? Yes\"",
"$type": "pub.leaflet.blocks.image",
"image": {
"$type": "blob",
"ref": {
"$link": "bafkreielm7higcqbyfa6cejtbg6wej4kxour2yyii55tee5uihu45jlpsa"
},
"mimeType": "image/png",
"size": 451868
},
"aspectRatio": {
"width": 2258,
"height": 1564
}
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"alt": "The Google Sheet source of the data. 2 Columns \"Bluesky Handle\" and \"Attending ATmosphere Conf?\". There's only one row with the data \"alex.mcroberts.me\" and \"true\".",
"$type": "pub.leaflet.blocks.image",
"image": {
"$type": "blob",
"ref": {
"$link": "bafkreiaqhrmbtlchqy2yq2jlhstyji2e2weiqwigfkwmg3xcmheh6ug6qa"
},
"mimeType": "image/png",
"size": 579653
},
"aspectRatio": {
"width": 2258,
"height": 1564
}
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.header",
"level": 3,
"facets": [],
"plaintext": "Notes"
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 80,
"byteStart": 50
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
},
{
"index": {
"byteEnd": 108,
"byteStart": 84
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
}
],
"plaintext": "For brevity's sake, I've haven't talked about the generateHandleHTML(handleData) or generate404HTML(handle) functions."
}
},
{
"$type": "pub.leaflet.pages.linearDocument#block",
"block": {
"$type": "pub.leaflet.blocks.text",
"facets": [
{
"index": {
"byteEnd": 104,
"byteStart": 96
},
"features": [
{
"$type": "pub.leaflet.richtext.facet#code"
}
]
},
{
"index": {
"byteEnd": 156,
"byteStart": 144
},
"features": [
{
"did": "did:plc:wshs7t2adsemcrrd4snkeqli",
"$type": "pub.leaflet.richtext.facet#didMention"
}
]
},
{
"index": {
"byteEnd": 167,
"byteStart": 157
},
"features": [
{
"uri": "https://tangled.org/did:plc:ml6hwrktvr7r5nhxs7f7eoz4/cloudflare-workers-google-sheets",
"$type": "pub.leaflet.richtext.facet#link"
}
]
}
],
"plaintext": "They each take a single argument, and use that to build an HTML payload which is then sent as a Response to the client. You can see them in the @tangled.org repository. "
}
}
]
}
]
},
"bskyPostRef": {
"cid": "bafyreibnmrlrncljinxzxfvmehsplx4wlji42ajkfmjmwrpknoaaoyhtwq",
"uri": "at://did:plc:ml6hwrktvr7r5nhxs7f7eoz4/app.bsky.feed.post/3ma5kuo77rc2a",
"commit": {
"cid": "bafyreiar4mxmfn7judfjol3r4em27i7dpcwuj37xegumrljsz5cl7thf24",
"rev": "3ma5kuobvts24"
},
"validationStatus": "valid"
},
"description": "",
"publishedAt": "2025-12-17T02:00:17.228Z"
}