AI CSV Search And Blog Features

Introduction

For some years I've used Google Sheets to create data tables. I've created quite a few specifically for my blog posts. It's really easy: any tab can be published as a web page and it comes with a unique URL for use in HTML hyperlinks.

I like using them because Sheets can both organise and maintain the data and, once linked to a blog post, everything else is automatic. They are particularly useful displaying tables that are too wide for a standard blog post (600 pixels in my case).

They have one drawback: searching is a bit clumsy. Readers need to know that Ctrl+F will find what they are looking for on most browsers. Even so it's not ideal not least because you can't filter the table.

Enter AI

Last week I did a lot of work indexing the illustrations in the WRG books resulting in a spreadsheet containing 1,322 rows. This was far to long to use without a search or filtering mechanism.

After a bit of research, I found that it's possible to display CSV (comma-separated values) files on web pages without a background database: an ideal solution for Blogger which doesn't offer (or allow) this functionality. All I needed was a code widget I could paste into Blogger as Google Sheets can export any tab as a CSV file..

Of late, I've been using claude.ai to write python scripts for me. Therefore I used it to explore how to display large CSV files on Blogger.

I started by embedding the data in a widget the AI had written. Then realised that Google Sheets will publish CSV files in the same way it does web pages. More importantly, I could link the published CSV file to the widget. Using data directly this way is so much more efficient: no more exporting, cutting and pasting.

Three or four days later I'd worked through various use cases and had a set of widgets that:

• took data directly from a tab in Google Sheets published as a CSV file,
• created hyperlinks from the CSV data (CSV files are strictly plain text),
• allowed me to set column widths,
• displayed multiple tables with and without a search box,
• matched my blog's theme (link colours etc).

The use of the widgets mean I am making more use of the data sources I have on Google Sheets, and in a way that, I hope, is much more accessible to readers.

Results

Overall I am pleased with the outcome and here are some examples of the widgets in action:

• I have an external blog index held in Google Sheets. Thanks to the basic widget I have a super duper new index page using this data. It has both hyperlinks and is searchable. I'm amazed how quickly it loads and processes all 466 rows. Previously I had three different views of the same data on that page.

• The same widget lists all 1,322 rows from my recent WRG indexing project. Again super quick, stylish and searchable.

• A multi-table variant of the widget displays my competition history & battle reports alongside other tables, all on one page. Very handy and well behaved.

• A non-search variant of the widget displays simple tables like that on the Northern League page. As the with the previous examples the data is in Google Sheets, so when I update the data at the end of the year the table will update automatically.

Bonus use

Whilst developing widgets for pasting into Blogger I realised I could create a general, search, format and fixed width export tool for a stand-alone web page. With this you can upload, format, filter and export any CSV file, but I made sure it can also convert this:

List,Period,Sub-period,Description,Date,Terrain
75,Classical,East & Steppes,Early Arab,312 BC to 630 AD,"Plain, Steppes"
75.1,Classical,East & Steppes,Early Arab (Ghassanid),312 BC to 630 AD,"Steppes, Desert"
75.2,Classical,East & Steppes,Early Arab (Kindah),312 BC to 630 AD,"Steppes, Desert"

to this which is ideal for posting into a forum:

List   Period     Sub-period      Description                    Date               Terrain
75     Classical  East & Steppes  Early Arab                     312 BC to 630 AD   Plain, Steppes           
75.1   Classical  East & Steppes  Early Arab (Ghassanid)         312 BC to 630 AD   Steppes, Desert          
75.2   Classical  East & Steppes  Early Arab (Kindah)            312 BC to 630 AD   Steppes, Desert

Of course to get the pretty display you have to use the code tags in the forum editor.

Closing remarks

There's a lot of negative noise about AI and so-called "vibe coding", but I found this exercise not dissimilar to solving a puzzle or a maze. A healthy mental exercise.

Of course, the better prepared you are the quicker the AI responds. Towards the end I had my specification well defined, and the final variants emerged after just two or three iterations.

I now have a set of widgets that will deal with multiple tables, and allow adjustable columns and justification. The latter were things I hadn't even thought of when I started, but they soon became necessary features. Of course the biggest feature was merging two columns to create a relative hyperlink: very neat and, most importantly, very useful for readers.

If you'd like copies of the widgets do get in touch. They should work on any web page but you may have to use claude.ai to get a match to your blog's theme if you're not au fait with CSS.