I'm Jerry Wohlgemuth.

I've recently been working on a site to try to spread awareness around public access to a local canyon. It's using the Astro framework to build and deploy a static site to Cloudflare's Pages. The site is mainly static, except for one component that display results from a Google Form.

To display the data on the site, I decided to write the aggregations to Cloudflare's Workers Key Value store. Then, I could have a very simple worker that reads this key and returns it as JSON.

I decided on this solution because I can't directly access the sheet from the frontend - I want to keep those credentials private. Workers and KV store have a very generous free tier. The worker can be invoked 100_000 times for free. I don't anticipate traffic will ever get close to that. Additionally, even if we could pull from a Google Sheet directly, like from the Worker, it's very slow. Google also requires OAuth to authenitcate, versus Cloudflare's Workers that only require an API key.

So the data flow ended up something like this: Google Form -> Google Sheet -> Apps Script Trigger -> Apps Script Function -> Write Cloudflare KV. The worker then simply reads from the KV store and serves the JSON that was written.

Here's App Script code to write to Cloudflare's KV:

function writeToCloudflare() {
  const token = 'cloudflare_token';
  const key = 'key';
  const namespace = 'namespace_uuid'
  const account = 'cloudflare_account_id'
  // Munge your data from the sheet
  const data = JSON.stringify({
    key: 'value'
  })

  var options = {
    method : 'put',
    contentType: 'application/json',
    payload : data,
    headers: {
      'Authorization': 'Bearer ' + token
    }
  };
  const url = 'https://api.cloudflare.com/client/v4/accounts/' +
    account +
    '/storage/kv/namespaces/' +
    namespace +
    '/values/' +
    key; 
  UrlFetchApp.fetch(url, options);

Then an Apps Script Trigger can be configured. For mine, I need the "Form spreadsheet - On form submit" event. This will fire the configured function every time the Google Form is submitted.

From there, the worker code is very simple to return the response:

export interface Env {
	KV_NAMESPACE: KVNamespace;
}

export default {
	async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
		const value = await env.KV_NAMESPACE.get('surveyresults');
		const json = JSON.parse(value);
		return Response.json(json);
	},
};

Don't forget to configure your KV_NAMEPACE binding in the wranger.toml file.

[[kv_namespaces]]
binding = "KV_NAMESPACE"
id = "namespace_uuid"

And there you have a pretty straightforward way of setting up an API endpoint that can serve some aggregated data from Google Sheets.