How I use javascript in Pelican to make a static website dynamic

And with Google Sheets as a database!!

What Pelican is

In case you’re unaware of it, or SSG’s in general, I’ll mention those briefly. If you know this already, just skip to the dynamic part!

Basically, SSGs, or Static Site Generators, are programs which generate websites based on content, with the person making the website not needing to write any code. So, you can write some posts and articles in markdown or reStructured text, run one CLI command, and you get the source code for an entire (very simple) website. You can then deploy however you like, whether it is a docker container, an apache webserver, or somthing like github pages.

This type of website is suitable for static content, meaning things that don’t change. A blog is a perfect example of a static website, especially if you don’t want comments.

The most popular SSG is Jekyll, written in ruby. There are alternatives written in other programming langauges, such as Hugo for Go, pelican for Python, and more. I went with Pelican since I already had Python set up on my laptop.

What I need an SSG for

As an avid reader, I am constantly looking for new things to read. For years, every recommendation I received was saved in a Google Keep note. When I read a book from the list, I would move it to a different note. When friends asked for recommendations, I would copy/paste from the list.

Then, after learning a bit about databases, I put all the books into an Oracle database, and created a no-code frontend using Oracle Apex. This creates a website which displays the data from my books table as a nice HTML page. The added benefit was that it was very easy to create another page, this one with a user input form, where my readers could suggest their favorite books and add them to my reading list!

After a few years though, maintaining a VPS and managing an Oracle database became more effort than it was worth, so I dowloaded my lists and took it down. Since then, I had been keeping an eye out for a suitably easy replacement.

Finally, I read about SSGs and realized it was a perfect fit for a website which would just hold a list of books.

I could have just stored the list as a markdown table in a git repo or snippet, but I wanted my readers to be able to suggest their own books to my list.

How I use Pelican

I simply created 2 markdown files, each containing a list of books as a markdown formatted table. One file is my “ratings” file, which includes the books I’ve read, and my (very subjective) rating, based only on how much I enjoyed them. The second file is the “suggestions’ file, where books I haven’t yet read live. ̶ ̶I̶ ̶r̶u̶n̶ ̶`̶p̶e̶l̶i̶c̶a̶n̶ ̶c̶o̶n̶t̶e̶n̶t̶`̶ ̶t̶o̶ ̶g̶e̶n̶e̶r̶a̶t̶e̶ ̶t̶h̶e̶ ̶h̶t̶m̶l̶ ̶a̶n̶d̶ ̶c̶s̶s̶ ̶f̶i̶l̶e̶s̶ ̶f̶o̶r̶ ̶t̶h̶e̶ ̶s̶i̶t̶e̶,̶ ̶t̶h̶e̶n̶ ̶I̶ ̶c̶r̶e̶a̶t̶e̶ ̶a̶ ̶z̶i̶p̶ ̶f̶i̶l̶e̶ ̶i̶n̶c̶l̶u̶d̶i̶n̶g̶ ̶t̶h̶e̶ ̶e̶n̶t̶i̶r̶e̶ ̶o̶u̶t̶p̶u̶t̶ ̶d̶i̶r̶e̶c̶t̶o̶r̶y̶ ̶a̶n̶d̶ ̶u̶p̶l̶o̶a̶d̶ ̶i̶t̶ ̶t̶o̶ ̶C̶l̶o̶u̶d̶f̶l̶a̶r̶e̶ ̶p̶a̶g̶e̶s̶.̶

EDIT: I’ve set up a private Gitlab project where I save the source markdown files. The benefits of this are 1) I now benefit from Git’s versioning, 2) I can edit my site from any device, and 3) Pages automatically detects any changes to my Main branch, and redeploys my site. So all I have to do now is edit my markdown and commit.

I went with Cloudflare as it is free and I already use Cloudflare for my DNS management.

The website is then available at my own domain, at https://books.nokoro.net.

Start to finish, from installing Pelican, rewriting the content as a markdown table, writing a oneliner to edit the resulting HTML a bit, all the way to the upload and seeing the site live — takes about 10 minutes on my Debian laptop. It took a little bit longer on a mac, but just because python hadn’t already been installed.

Using Javascript to put dynamic sections in a static site

I wanted my users to be able to suggest me new things to read. But my site is static, it is written solely in markdown and has no moving parts. To allow users to add their own data would require having an input form, as well as someplace to actually store the data, as well as then needing to develop a way to bring that data into the website automatically.

My first concern was a database, as I knew that if worst came to worst, I could just add some manually written HTML to the generated output I had. I looked into some free PostgreSQL hosting options, which let you store tiny amounts of data (typically between 5 and 20 MB) at no cost. This would work, but I would be back to managing a database, or at least SQL. (or pgrest if that could be installed on a managed DB.)

Then I had the brilliant idea to simply use a Google Form as the data entry device, and Google Sheets as a stupid-simple, and free, “database”.

Markdown, in case you didn’t know, directly supports HTML tags. So, to put the Form into my page, I added a collapsible markdown/html segment to the bottom of the page, and just embedded the google Form with an iframe:

The form stays nicely out of the way until the user clicks the “Add recommendation” link:

Now, to query the data and add it into the “Suggestions” page dynamically..

At first, I tried using the gviz (Google Visualizer, I think) API to query my spreadsheet, as described here: https://ai2.metricrat.co.uk/guides/use-gviz-to-get-and-query-google-sheet-data

but this did not work in my javascript, as it returns invalid JSON on purpose.

My next step was to create a Google Cloud account for free, (up to 12 projects at time of writing) and create an API token for my spreadsheet. Then, I could directly uery my spreasheet with simple GET requests! Perfect for this use.

Note that this API key grants unlimited acces to your google APIs! I restricted it to only work on the Sheets API and I also limited it to work only from my domain name. This way, it’s safe to post online and people can’t steal it by clicking “View Source”. I would have like to limit it to read-only on the individual spreadsheet, but at the time of writing this is all I can do short of creating a google account with just one spreadsheet.

The final step was automatically getting the data from the spreadsheet to appear on the site’s “Suggestions” page. I had to learn some javascript, and about promises, but after some trial and error and help from a friend, I created a JS script, inside `<script>` tags right in my markdown, which runs on page load, gets the data from the spreadsheet via sheets API, and adds rows to the “Suggestions” table that already exists in the DOM.

You can see the exact code I used on my github.

I also wrote oneliner that deletes HTML relating to pelican’s default theme, and a redirection from the site’s homepage, since I only want users to be able to see my content pages. I run this oneliner after generating the output with Pelican.

All in all, this was a very fun little project that just took a couple hours and taught me a bunch of new things. I highly recommend using an SSG if you just want to create a simple website quickly and for free. Hopefully, my examle helps a bit if you want to add any dynamic parts to your own static website!