Hugo with Google Sheets

I run an EV newsletter over on https://revolution.guide, which uses a Google Sheets spreadsheet to collect links for each edition. It has a date, title and URL column, which I publish in the newsletter.

I wanted to create an archive on the blog portion of the newsletter site. The blog is generated in Hugo (like this blog), and I looked around if it was possible to grab the Sheets content semi-automagically. And Hugo can!

You grab the URL to your Google Sheets document and publish it as CSV. You use Hugo’s getCSV function passing in a separator and the url. And voila, you get a list of your sheet’s content.

{{- $url := "https://docs.google.com/spreadsheets/<your_ID>/...&output=csv" }}
{{- $sep := "," }}
{{- $links := getCSV $sep $url}}

I then created a dictionary to hold the contents, with an index, date, title, link, month and year.

{{- $entry := dict "index" "" "date" "" "title" "" "link" "" "month" "" "year" "" }}
{{- $entries := slice $entry }}

I iterate over the data, while skipping the first line (the headers). I grabbed the date portion, and parsed it into month and year parts, and recreated a display date, by using an annoying concatination workaround. Then also grabbed the URL and title and populated the dictionary. I also created an index value.

{{- range $index, $smt := after 1 $links }}
    {{- if index . 2 }}
      {{- $linkDate := (split (index . 0) " ") }}
      {{- $parseDate := (split (index $linkDate 0) "/") }}
      {{- $displayMonth := index $parseDate 0}}
      {{- $displayYear := index $parseDate 2}}
      {{- $displayDate :=  printf "%s" (index $parseDate 2) | printf "%s/%s" (index $parseDate 0) | printf "%s/%s" (index $parseDate 1) | printf "%s" }}
      {{- $displayLink :=  index . 2 }}
      {{- $displayTitle :=  index . 1 }}
      
      {{- $entries = $entries | append (dict
          "index" $index
          "date" $displayDate
          "title" $displayTitle
          "link" $displayLink
          "month" $displayMonth
          "year" $displayYear
        ) }}

    {{- end }}
{{- end }}

I then sorted the entries based on the index, latest first.

{{- $entries := sort $entries ".index" "desc" }}

Then created a UL, with each entry a list item, writing out the month and year, and title with a link to the article’s URL. To make it more consistent in the display, I normalized the month part to two numbers, prepending with a 0. I also check if there is a link, else it’s not really useful to list anything.

{{- range $row := $entries }}
    {{- if $row.link}}
    <li class="y{{ $row.year }}">{{ if eq (len $row.month) 1 }}0{{end}}{{ $row.month }}/{{ $row.year }} <a href="{{ $row.link }}" rel="noopener noreferrer">{{ truncate 110 $row.title }}</a></li>
    {{- end }}
{{- end }}

Everytime I publish an update to the blog, the list will get updated. It’s not realtime, but it doesn’t have to be. Check out the EV News Archive.

EDIT: Applied the same principle (and Hugo blog template) for a Strangelove-AI Link Blog. There I exported my (StrangeloveAI twitter)[https://twitter.com/StrangeloveAI], which come in a JSON format; flattened the JSON, converted it to CSV, and imported it into Google Sheets. Then used the same Hugo template to display the previous tweeted links in a news archive format. Check out the Strangelove-AI link blog news article section. I was using my Twitter Bluesky account as a bookmarking tool. But going forward, I use an iOS/macOS Shortcut to bookmark new AI news links to the Google Sheet (going through a Google Form), and it will automatically appear on Bluesky and the link blog’s News Articles (when published at the end of the month).

Grow Your Audience AI Prompts
Older post

Grow Your Audience AI Prompts

50 Social Media AI Prompts to ask ChatGPT

Newer post

AI Social Recommendator

AI Social Recommendator using ChatGPT

AI Social Recommendator