# Hugo with Google Sheets

EDIT 2025: Stopped the newsletter itself, and **rebranded** revolution.guide and moved its content to [EV Life](https://electricvehicle.life). No more revolution.guide. [EV News Link Blog]https://electricvehicle.life/news/

----

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

I wanted to create an archive on the [blog](https://blog.revolution.guide) 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](https://blog.revolution.guide/newsletter-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](https://blog.strangelove-ai.com/news). I was using my <del>Twitter</del> [Bluesky account](https://bsky.app/profile/strangelove-ai.com) 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).