A website powered by Cloudflare Workers and Google Sheets

Yesterday I shared a post about creating a cloudflare worker to access data from a Google Sheet, to power a small website.

View post on Bluesky

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.

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.

While I could have hooked up the Google Sheets API in the browser executing JavaScript, this would have exposed the JSON Private Key.

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.

For this post, I've simplified the data quite a bit, but the concept remains the same.

Let's talk a little about what's involved here from a technical perspective.

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.

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.

Install Cloudflare Wrangler

We'll need to install Cloudflare's command line tool (CLI) for this.

npm i -D wrangler@latest

Make a Cloudflare worker

We'll need to start a new Cloudflare Worker project. In an empty directory, run the following command.

npx wrangler init

A Create Cloudflare workflow will present in terminal.

As you work through the workflow:

1. Select Hello World example

2. Select Worker only

3. Select TypeScript

Once all dependencies have all been installed, say yes to Git, and Deploy.

Once it's deployed, a new tab will open in your browser, pointing to your worker's URL – "Hello World!"

Make a Google Cloud Console Service Account

Go to https://console.cloud.google.com/

Search for Service Accounts

Give it a meaningful Service account name, and Service account ID (which becomes the email address).

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.

Click Create and continue.

Skip the Permissions and Principals with access, and click Done.

On the Service accounts page, click the email address of the new Service account that you just created.

Click the Keys tab.

From the Add key dropdown menu, select Create new key. Select JSON.

The Private Key should be downloaded automatically in your browser – we'll come back to the contents of this file later.

Enable Google Sheets API access

Still in https://console.cloud.google.com/, search for Google Sheets API and Enable the API access.

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!

Add the Service Account to the Google Sheet

Time to go to your Google Sheet. under Share, add the email address of the Service Account as a Viewer to the sheet.

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

https://imgs.xkcd.com/comics/exploits_of_a_mom_2x.png

Make a Cloudflare Secret

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!

To do that, it's time to put our Private Key as a Secret into Cloudflare.

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.

ℹ️ 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

Copy the contents to the clipboard.

Next, we'll run the following command:

npx wrangler secret put GOOGLE_SERVICE_ACCOUNT_KEY

Paste in the value of the (now in a single line) JSON Private Key from the Service Account.

You'll see a host of asterisks whizzing by in your terminal as the content of the clipboard is pasted in.

Updating the Cloudflare Worker to access Google Sheets

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.

// Google Sheets configuration
const SPREADSHEET_ID = ''; // This is the string between "https://docs.google.com/spreadsheets/d/" and "/edit?gid=0#gid=0"

/**
* Google Service Account JSON key structure
*/
interface GoogleServiceAccountKey {
type: string;
project_id: string;
private_key_id: string;
private_key: string;
client_email: string;
client_id: string;
auth_uri: string;
token_uri: string;
auth_provider_x509_cert_url: string;
client_x509_cert_url: string;
universe_domain: string;
}

Next, let's create a short lived access token from our JSON Private Key.

/**
* Get access token from Google OAuth
*/
async function getGoogleAccessToken(serviceAccountKey: GoogleServiceAccountKey): Promise<string> {
const jwt = await generateGoogleJWT(serviceAccountKey);

const response = await fetch('https://oauth2.googleapis.com/token', {
method: 'POST',
headers: {
'Content-Type': 'application/x-www-form-urlencoded'
},
body: `grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=${jwt}`
});

if (!response.ok) {
throw new Error(`Failed to get access token: ${response.statusText}`);
}

const data = await response.json() as { access_token: string };
return data.access_token;
}

/**
* Generate JWT token for Google Service Account authentication
*/
async function generateGoogleJWT(serviceAccountKey: GoogleServiceAccountKey): Promise<string> {
const header = {
alg: 'RS256',
typ: 'JWT',
kid: serviceAccountKey.private_key_id
};

const now = Math.floor(Date.now() / 1000);
const payload = {
iss: serviceAccountKey.client_email,
scope: 'https://www.googleapis.com/auth/spreadsheets.readonly',
aud: 'https://oauth2.googleapis.com/token',
exp: now + 60,
iat: now
};

// Base64url encode header and payload
const base64urlEncode = (obj: object): string => {
return btoa(JSON.stringify(obj))
.replace(/\+/g, '-')
.replace(/\//g, '_')
.replace(/=/g, '');
};

const encodedHeader = base64urlEncode(header);
const encodedPayload = base64urlEncode(payload);
const unsignedToken = `${encodedHeader}.${encodedPayload}`;

// Import private key and sign
const privateKey = serviceAccountKey.private_key;
const pemHeader = '-----BEGIN PRIVATE KEY-----';
const pemFooter = '-----END PRIVATE KEY-----';

// Extract and clean the base64 content
const pemContents = privateKey
.replace(pemHeader, '')
.replace(pemFooter, '')
.replace(/\\n/g, '')
.replace(/\n/g, '')
.replace(/\s/g, '');

const binaryKey = Uint8Array.from(atob(pemContents), c => c.charCodeAt(0));

const cryptoKey = await crypto.subtle.importKey(
'pkcs8',
binaryKey,
{
name: 'RSASSA-PKCS1-v1_5',
hash: 'SHA-256'
},
false,
['sign']
);

const signature = await crypto.subtle.sign(
'RSASSA-PKCS1-v1_5',
cryptoKey,
new TextEncoder().encode(unsignedToken)
);

const base64urlSignature = btoa(String.fromCharCode(...new Uint8Array(signature)))
.replace(/\+/g, '-')
.replace(/\//g, '_')
.replace(/=/g, '');

return `${unsignedToken}.${base64urlSignature}`;
}

Next up, let's fetch some data from the sheets – we're almost at the good parts!

/**
* Fetch data from Google Sheets
*/
async function fetchSheetData(accessToken: string, range: string): Promise<string[][]> {
const url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/${encodeURIComponent(range)}`;

const response = await fetch(url, {
headers: {
'Authorization': `Bearer ${accessToken}`
}
});

if (!response.ok) {
const errorBody = await response.text();
console.error('Google Sheets API error:', response.status, errorBody);
throw new Error(`Failed to fetch sheet data: ${response.statusText} - ${errorBody}`);
}

const data = await response.json() as { values?: string[][] };
return data.values || [];
}

Now we've got all the functions there, time to stitch it together:

export default {
async fetch(request, env, ctx): Promise<Response> {
// Parse service account key
const serviceAccountKey = JSON.parse(env.GOOGLE_SERVICE_ACCOUNT_KEY);

// Get access token
const accessToken = await getGoogleAccessToken(serviceAccountKey);

const data = await fetchSheetData(accessToken, 'Handles!A1:B');

return new Response(data.toString());
},
} satisfies ExportedHandler<Env>;

When we test the response, we should expect to see the following response

Bluesky Handle,Attending ATmosphere Conf?,alex.mcroberts.me,TRUE

Adding Caching with Cloudflare Workers KV

We'll likely want to cache our response, so we don't hit the Google Sheet, and max out any rate limits there.

Make a Cloudflare Workers KV Namespace

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

npx wrangler kv namespace create handles

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.

Hooking up the KV as a cache

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.

Extract the handle from the URL

const url = new URL(request.url);
const handle = url.pathname.replace(/^\/+|\/+$/g, '');
console.log(`Looking up handle: ${handle}`);

// Ignore favicon and other non-handle requests
if (!handle || handle === 'favicon.ico') {
return new Response(generate404HTML(handle), {
status: 404,
headers: { 'Content-Type': 'text/html' }
});
}

Check if the handle is in our KV cache

let handleData = null;
if (env.handles) {
try {
const cachedData = await env.handles.get(`handle:${handle}`);
if (cachedData) {
handleData = JSON.parse(cachedData);
console.log(`Cache hit for handle: ${handle}`);
}
} catch (cacheError) {
console.error('Cache read error:', cacheError);
// Continue without cache
}
}

If it's not in the KV Cache, pull from Google Sheets

if (!handleData) {
console.log(`Cache miss for handle: ${handle}, fetching from Google Sheets`);

const serviceAccountKey = JSON.parse(env.GOOGLE_SERVICE_ACCOUNT_KEY);
const accessToken = await getGoogleAccessToken(serviceAccountKey);

// Fetch handle data from the Google Sheet
const handleData = await lookupHandleInHandles(accessToken, handle);
if (!handleData) {
return new Response(JSON.stringify({error: 'Handle not found'}), {
status: 404,
headers: { 'Content-Type': 'application/json' }
});
}
console.log(`Found handle: ${handleData} for handle: ${handle}`);

// Cache the result (if KV is configured)
if (env.handles) {
try {
await env.handles.put(
`handle:${handle}`,
JSON.stringify(handleData),
{ expirationTtl: SHEET_CACHE_TTL }
);
console.log(`Cached data for handle: ${handle}`);
} catch (cacheError) {
console.error('Cache write error:', cacheError);
// Continue responding to the request without caching
}
}
}

Then send our response

return new Response(generateHandleHTML(handleData), {
headers: { 'Content-Type': 'text/html' }
});

Screenshots

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].

Notes

For brevity's sake, I've haven't talked about the generateHandleHTML(handleData) or generate404HTML(handle) functions.

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.