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 Newsletter Archive, and subscribe!

Now that I know how this works, I might start to look into how I could use this for other content, where I now use another third-party (like SpreadSimple).

In fact I went ahead and implemented the NSW EV Rebate (which often doesn’t work, I find) into https://blog.revolution.guide/nsw-ev-rebates/.