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 blog. There I exported my (StrangeloveAI tweets)[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 blog news section. I was using my (Twitter account)[https://twitter.com/StrangeloveAI] 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 the blog’s News Archive (when published at the end of the month).