I do a lot of work with spreadsheets. Some are pretty simple, and I can inspect them by hand. Some are enormously complicated – hundreds of rows, dozens of columns, etc. It’s not practical to inspect/test them manually.

Enter ExceLint. It’s an add-in to Microsoft Excel that “lints” a spreadsheet. (A “linter” is a program that picks off ‘bits of fluff’.) The ExceLint team reports they regularly find errors, some material, in spreadsheets they test. (I was pretty lucky – there were a couple minor errors, but I was OK.)

ExceLint requires you to “side-load” a manifest.xml file that points to the resource files the add-in uses. The ExceLint home page describes the side-load process. It stopped working a while ago, so I just started to hope my good spreadsheet-fu continued. But then I decided to do a bit of troubleshooting, and found that ExceLint retrieved its code from a defunct server running on azure.com. I fixed it a couple ways:

1. I created a Dockerfile that built the resources for the add-in on the local computer. Side-loading a manifest file that points to localhost allowed the add-in to work again.
2. I realized that the resources – the files loaded by Excel – could be served by a static https server anywhere . So I created a new GitHub Action to build those files with each commit, and then to use Github Pages to serve those files.

You can now use ExceLint again, using publicly served resources from Github. You can even test at https://excelint.github.io/ExceLint-addin. The result isn’t terribly interesting – it displays the ExceLint logo, with a spinner that runs forever.

The PRQL project provides a simple, powerful, pipelined SQL replacement. To make it easier to share the development environment, there is a Dev Container that bundles the dozens of components into a single container. This makes it easier for new person to start using the project: they don’t have to collect the proper (sometimes conflicting) versions of the tools on their computer.

The Dev Container had been working fine for over a year. I recently noticed that there was a new version of Docker Desktop (old: 4.29.0; new: 4.30.0). After the upgrade, some of the services – the Playground and the “Book” (the documentation) – were not available from the Dev Container.

The fix was to force those services to bind to the address 0.0.0.0 (not “localhost”, not 127.0.0.1) in the task file that configures them. See the PRQL repo for details.

I regularly record public meetings in my town and post them on Youtube. This allows residents who could not attend the meeting in person to see what occurred. It also supports creation of accurate minutes.

I like to add a label (showing the committee name and date) and a timecode (a running clock showing the current time in the meeting). I found a free program Shutter Encoder that does this. It works well, but has a detailed (very fussy) user interface that requires a long set of steps for every new video.

Since I only deal with a couple variations of the recordings, I decided to write scripts that use the ffmpeg program directly to accomplish these repeated, well-defined tasks.

I now move the video/audio files into this directory and issue a command like the one below, and wait a few minutes. The resulting video is ready for uploading to Youtube.

 sh ./AddTimecode.sh "Lyme Committee Meeting-6Feb2024" 09:58:00 

Here are the notes I use to remind myself how to make the videos using my Mac. This information and the resulting scripts are also available on my github repo.

NB: You may need to install the ffmpeg program. Download a pre-built binary from ffmpeg.org, or read more on that site for getting a version for your computer.

Add image, label, and timecode to an audio track

To convert an audio-only recording into a video suitable for uploading to Youtube, this script combines a static JPEG photo with an audio track. The repo contains a photo of the Lyme Town Offices which is used by default. To use the script:

1. Open the audio .mp4 file with QuickTime Player, and use the Trim (Cmd-T) function to remove the dead air from the start and end of the recording, as necessary. Save as Meeting Name_ddMMMyyyy.mp4
2. Move the resulting audio file to this folder and run this script, where hh:mm:ss is the actual start time of the recording:

sh AddTimestamp.sh "Meeting Name and Date" hh:mm:ss

The script looks for a .mp4 file (and the image of Town Offices) to produce an output file named Meeting Name and Date-timecoded.mov

3. Cleanup: Move the original audio file and the resulting …-timecoded.mov file to the CompletedFiles folder so they won’t interfere with subsequent runs. Discard after they have been uploaded

Add label and timecode to AVCHD files

A Panasonic HDC-TM80 video camera produces a AVCHD meta-file that contains the video from a recording session. The files in an AVCHD file are within the AVCHD/BDMV/STREAM directory with filenames 00000.MTS, 00001.MTS, etc.

Drag the AVCHD file into this folder and run this script:

sh ./TimecodeAVCHD.sh "Meeting Name and Date" hh:mm:ss

The script looks for a file named AVCHD and outputs file named MeetingName-timecoded.mov

Add label and timecode to Zoom video recordings

Sometimes, a meeting is recorded by an Owl Camera. This results in a good video with good audio, but it’s still helpful to display the meeting name and timecode at the bottom.

Drag the Zoom file (a .mp4 file) into this folder and run this script:

sh ./TimecodeZoom.sh "Meeting Name and Date" hh:mm:ss

The script looks for any file with a .mp4 extension and outputs file named MeetingName-timecoded.mov

Background Information

StackOverflow/SuperUser and similar sites are your friends.

So is ChatGPT. I gave it this initial prompt, “give me a ffmpeg command to read a .mts file and add a label and a timecode and output a .mov file. Be sure the audio of the .mts file is preserved.” and iterated to get the final commands.

Experiments with ffmpeg From: https://superuser.com/questions/1041816/combine-one-image-one-audio-file-to-make-one-video-using-ffmpeg

1. Accepted answer: ffmpeg -loop 1 -i image.jpg -i audio.wav -c:v libx264 -tune stillimage -c:a aac -b:a 192k -pix_fmt yuv420p -shortest out.mp4

ffmpeg -loop 1 -i Lyme-Town-Hall-Offices-cropped-1024.jpeg -i SB-20231130.mp4 -c:v libx264 -tune stillimage -c:a aac -b:a 192k -pix_fmt yuv420p -shortest 11Nov2023-first.mp4

Takes a while to encode

2. PJBrunet and Kokizzu: ffmpeg -r 1 -loop 1 -y -i 1.jpg -i 1.m4a -c:a copy -r 1 -vcodec libx264 -shortest 1.avi

time ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest 11Nov2023-second.mp4

~5 seconds; result 18.6mbytes. Creates file that cannot be opened by QuickTime Player

3. Same as #2 with image that has odd number of pixels

ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024-1.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest 11Nov2023-three.mp4

Blows big chunks with “odd dimensions” error

4. Same as #2 with odd number of pixels, using -pix_fmt yuv444p right before name of output file.

time ffmpeg -r 1 -loop 1 -y -i Lyme-Town-Hall-Offices-cropped-1024-1.jpeg -i SB-20231130.mp4 -c:a copy -r 1 -vcodec libx264 -shortest -pix_fmt yuv444p 11Nov2023-four.mp4

Fast (takes 5-10seconds). Creates file that cannot be opened by QuickTime Player

I was playing with ChatGPT and Klavier.ai the other day. It purports to import documentation so that you can ask questions about it. I let it read in the PRQL Language Book, and you can see the rest of my experimentation at the PRQL site.