Playback speed
Share post
Share post at current time

Freak in the Google Sheets: Building the Toilet Timeliner

Who among us hath not investigated on the shitter?

Timelines. Timelines. Timelines.

I am going to keep beating the drum that every good story, financial due diligence project and wrongful conviction should be built around one element: time.

The trouble is that I found my favourite timelining tools weren’t flexible enough, required too much effort in setup or didn’t enable myself and my colleagues to securely collaborate together.

So I ended up building timelines in Google Sheets, then more. Pretty soon it became the standard at Permanent Record Research, everything needs a timeline and timelines are built in Google Sheets.

However, the user interface of Google Sheets is of course not designed for investigative timelining, even with great features like a Timeline visualization (something new!) out of the box. This is where Google AppSheets comes in, allowing you to easily convert your spreadsheets into powerful applications that you can use and customize for your own investigations and team.

Follow along in this extended video tutorial on on how to take the simplicity of a 4-column Google Sheet, turn it into a visual timeline and then into a powerful collaborative timelining tool that you and your team can use from your phone, tablet or desktop. Resources, screenshots and downloads are contained below.

No programming experience required.

All from the comfort of the can.

— Justin

ps. We put a ton of effort into these posts and we hope you enjoy them! They’ll always be free but if ya like what we do here at please consider becoming a subscriber.


During the following timestamps I refer to external links or offer up a couple of downloads.

Toilet Timeliner CSV Example File

  1. In Google Sheets: File → Import

  2. The CSV file does not include screenshots or the documents uploaded during the video, but can serve as a starting point for you.


18:49 - Google Link

26:36 - Logo Download (Imgur Download)

26:50 - Splash Screen (ImgGur Download)

AppSheet Functions Used

CONTAINS (Thing You Searching Through, Thing You Are Looking For) - checks the field or string (the first parameter) for a string or keyword you’re searching for (the second parameter).

OCRTEXT (Image) - reads an IMAGE column and attempts to extract the text that is found in the image. Extremely useful for uploading screenshots and having all of the text be searchable.

UNIQUEID() - generates a random string of characters used for uniquely identifying rows in your Google Sheet. It doesn’t take any parameters.

Additional Reference

Google AppSheet Function Reference
Google Sheets Function Reference

Final Screenshots of AppSheet Settings

For your reference, or if something isn’t working correctly, have a look at the following screenshots which capture the final settings I have.

Toilet Timeliner - Data View - Click to Enlarge
Toilet Timeliner - Timeline View Component Settings - Click to Enlarge
Toilet Timeliner - Timeline Form View - Click to Enlarge
Toilet Timeliner - Format Rules - Highlight Incidents Red - Click to Enlarge
Toilet Timeliner - Format Rules - Police Activity Blue - Click to Enlarge

Toilet Timeliner - Format Rules - Highlight News Green - Click to Enlarge
Toilet Timeliner - Theme and Styling - Click to Enlarge

Bullsh*t Hunting is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

Bullsh*t Hunting
Bullsh*t Hunting