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