The Console newsletter goes out every Thursday morning at 8am UK time, which is where we're based. The email is our product. Although we plan to build out our website to include an archive of the developer tools we've featured in the past, it's some months away.

However, it is quite important for potential subscribers to be able to see what they're going to get before they choose to give us their email. If you look at any other newsletter - for example Serverless Status, The Diff or Stratechery - there is always the option to read past editions on the web first.

We have a similar thing with our "latest" page. This shows the interesting tools and new betas & early release programs we featured in our most recent newsletter. It is exactly the same content that goes out via email, minus a standard intro/outro. It's auto-generated using GitHub Actions every Thursday morning at the same time the email goes out, then deployed to Cloudflare Workers Sites as a static site built using Hugo.

This post explains how that page is generated.

Why we can't use Mailchimp's web archive

We're using Mailchimp to manage our email list. The main reason is that Mailchimp has a good reputation for deliverability, something which is very important with all the algorithmic filtering that happens on inboxes run by the likes of GMail.

However, Mailchimp is not so good when it comes to default privacy. We have had to make changes to our configuration to remove things like open tracking.

Mailchimp provides web versions for newsletters which makes it easy to give out links to past emails. Unfortunately, they also embed Google Analytics and don't provide an option to disable it. Our Privacy Policy states that whilst we do want to measure some metrics, we are against mass tracking systems like Google Analytics. We chose to use Plausible Analytics for our website for this reason.

We also wanted to set up the web version entirely within our design, layout and brand, so we decided to build our own page.

Using Hugo data files

Our editorial process is backed by Google Sheets. We have one sheet for the interesting tools we're tracking, and another for all the beta programs. This provides a feature-rich UI that is maintained and developed by Google as part of our Google Workspace subscription. We use some Google Apps Scripts to run custom actions, like generating the mailing list HTML. At some point we might develop our own content management system, but for now it's not a good use of our time.

It's important that our website is easy to maintain and fast-loading. For these reasons we use a static site builder that outputs plain HTML which is deployed Cloudflare's global CDN. We use Hugo as a static site framework to allow us to write all our content in Markdown, then build the HTML based on our custom website theme.

We use Hugo's support for loading arbitrary data from JSON files. The build step reads the JSON data, then creates the content from those files. The website build and deploy process is automated using GitHub Actions. This runs the Hugo build script and then pushes the static HTML to Cloudflare.

But with all the content in Google Sheets, how do we get it into JSON files for Hugo to parse?

Google Sheets to JSON in GitHub Actions

There is a third-party GitHub Action called gsheet.action which allows you to query Google Sheets, returning the result as JSON. The build step gets the full contents of the Google Sheet, converts it to JSON, then dumps it to a file. Thanks to Johan for releasing it as an open source tool!

This gets the raw data out of the Google Sheet, but it still needs some manipulation before it's ready for Hugo to parse.

Filtering the JSON with Python

Our Google Sheets contain all the tools / betas we've featured. There is a column which specifies which newsletter date it was included in, however the gsheet.action returns the full contents. Either we need to filter the JSON so it only contains the tools we we Hugo to load, or we need some filtering logic in Hugo itself.

I prefer the former option. Hugo does have the option to include logic in templates, but I know Python better. In particular, I need to generate a date filter based on "get the last Thursday". Implementing this is different in every language, but the third-party dateutil.relativedelta Python library makes it easy.

In less than 100 lines of code, the resulting Python script reads the raw JSON, filters the items we want to include, then writes the new JSON files to the Hugo data/ directory. This is called in the build step just before the Hugo action.

Conclusions

The result is a dynamically generated, but still static HTML, "latest email" page that is automatically built from our Google Sheets content management backend.