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).

Grow Your Audience AI Prompts
Older post

Grow Your Audience AI Prompts

Social Media AI Prompts https://socialmedia.prompt.cards Australia Day long weekend. I was subscribed to BetterSheets.co, intrigued how people use Google Sheets …

Newer post

AI Social Recommendator

AI Social Recommendator using ChatGPT https://ai.socialrecommendator.com A recommendations generator for social and business network endorsements now using …

AI Social Recommendator