The problem#

I mostly use Gnumeric for my personal spreadsheets, which are generally quite simple. But recently I wanted do some analysis that required writing a formula more complicated than just using the SUM() function and found Gnumeric's function documentation quite lacking in sufficiently detailed explanations and examples to be able to figure out how to use any of the more complicated functions.

The specific problem I was trying to solve was that I had some data on payments over time labeled with categories and wanted to summarize payments by category for each time period.

The solution#

The short version is that Gnumeric's formula language is nearly the same as Microsoft Excel's formula language, so there's no need to look for Gnumeric-specific help on writing formulas. There's plenty of advice online on writing formulas for Excel, and the information transfers without modification to writing formulas for Gnumeric.

For my specific problem, my layout was that each column was a time period and each row was a payee with column A being the name of the payee and column B being the category. I made entries further down in column A of the categories and wanted the cells in those rows to be the sum of the payments for the corresponding column's time period but only for the payees matching the category for the row. The function to do that is called SUMIF(), but that documentation page from Gnumeric is very unhelpful except for the line

This function is Excel compatible.

The Excel help page on SUMIF() has examples and a lot more explanation. Here's what the formula looks like in cell P50:

=SUMIF($B$2:$B$40,$A50,P$2:P$40)

where B2:B40 is the range where the categories are given for each payee (named in A2:A40), A50 contains the category being summed, and P2:P40 contains the actual values being conditionally summed (i.e., the values for the time period named in P1).

The details#

The problem#

Previously, I figured out how to extract Tametsi's puzzles, but I wanted to make something user-friendly that made use of those puzzles, so I didn't want to require people to install something or run console commands. I had also figured out how to get Java programs running in a browser, so I figured it would be straightforward to combine the two. As you may have guessed from this paragraph being in the "the problem" section, it was not. Specifically, while the Java command-line will read tametsi.exe as a JAR file, Doppio gives the error

Invalid Zip file: Central directory record has invalid signature

The solution#

As a workaround, use a different library to unzip tametsi.exe. There's no need to present it as a JAR file instead of a directory, so no need to rezip it:

var fs = BrowserFS.BFSRequire("fs");

async function unzipToDirectory(zipfile, dir) {
  const z = new zip.fs.FS();
  await z.importBlob(zipfile);

  async function extract(z, dir) {
    fs.mkdir(dir, true)
    if (z.directory) {
      const childDir = z.name
        ? `${dir}/${z.name}`
        : dir;
      for (const child of z.children) {
        await extract(child, childDir);
      }
    } else {
      fs.writeFileSync(
        `${dir}/${z.name}`,
        new buffer(await (await z.getBlob()).arrayBuffer()));
    }
  }

  await extract(z.root, dir);
}

To call that, in the uploadFile() function, replace the reader.onload with

await unzipToDirectory(f, process.cwd() + '/tametsi');

The details#

The problem#

Slashdot recently made a change to their ad code that made the site completely fail to load for me, showing the message

Failed to load website properly since html-load.com is blocked. Please allow html-load.com

and then blaming ad blocking:

This page could not be loaded properly due to incorrect / bad filtering rule(s) of adblockers in use. Please disable all adblockers to continue using the website. (click OK if you'd like to learn more)

I could see the page loaded just fine behind those messages, so it was obviously a lie. But the page continually reloaded if I tried to dismiss those pop-ups, so the site was unusable.

(Actually, this appears to have been disabled in the time it took me to write this blog post, so I guess this is no longer needed. Although it may be applicable to other websites using the same or similar mechanisms.)

The solution#

Install this user script. I have only tested it with Greasemonkey on Firefox so it require modification to work on other user script managers or browsers.

The details#

The problem#

I run a Discord community for a local hobby-focused friends group. Since it's theoretically people I know in person, I don't want it to be an open invite; I want confirmation that everyone that joins has a real in-person connection to the group. I handle by gating access to most of the channels behind a role that I grant to new users once I've identified them. As the group has grown, I am often not directly connected to everyone joining, so I wanted an easy way to grant trusted users the ability to do the same. The straightforward way is to just use Discord permissions to let other people assign roles, but I wanted something smoother and easier for non-technical users.

The solution#

YAGPDB ("Yet Another General Purpose Discord Bot") is an extremely flexible and configurable Discord bot that can be set up to do pretty much anything. For this purpose, we're deep in its customization features which include a programming language for running custom scripts in response to various triggers. Since people in the community tend to 👋 react to the join message Discord generates when someone they know joins, I wanted to specifically make that the signal that a user is a real person. Here's a YAGPDB custom command that will do so when set up to be triggered by adding a reaction and configured with the desired allowed roles and filling in the configurable values at the top of the script:

{{/*
    Grant role to user when trusted user
    reacts to their join message.
    By Daniel Perelman <https://github.com/dperelman/>

    Loosely based on
    <https://yagpdb-cc.github.io/utilities/reaction-logs>
    by Satty9361 <https://github.com/Satty9361>
*/}}

{{/* Configurable values */}}
{{$logging_channel_id := }}
{{$roleName := }}
{{$adminUserId := }}
{{/* End of configurable values */}}

{{/* Actual CODE */}}
{{ if (and
        (eq .ReactionMessage.Type 7)
        (eq .Reaction.Emoji.APIName "👋")) }}
    {{$newUser := .ReactionMessage.Author}}
    {{$role := getRole $roleName}}
    {{$adminUserLink := (print "[" (userArg $adminUserId) "]"
        "(<https://discord.com/users/" $adminUserId ">)")}}
    {{$userLink := (print "[" .User "]"
        "(<https://discord.com/users/" .User.ID ">)")}}
    {{$newUserLink := (print "[" $newUser "]"
        "(<https://discord.com/users/" $newUser.ID ">)")}}
    {{ if targetHasRoleID $newUser.ID $role.ID }}
        {{/* sendMessage $logging_channel_id (print
            "DEBUG: " $userLink " would have granted `"
            $role.Name "` to " $newUserLink
            " but they already have that role.") */}}
    {{ else }}
        {{ giveRoleID $newUser.ID $role.ID }}
        {{ sendMessage $logging_channel_id (print
            $userLink " granted `" $role.Name "` to
            " $newUserLink " by reacting :wave: to their "
            "join message.") }}
        {{ sendDM (print
            "You have granted " $newUserLink " the role `"
            $role.Name "` by :wave: reacting to their "
            "join message. If this was in error, "
            "please contact " $adminUserLink ".") }}
    {{ end }}
{{ end }}

The details#

The problem#

Java and JavaScript are insufficiently easily confused. I had some code I wanted to run on the user's computer and not the server, and it specifically had to be in Java to reference a library written in Java. I found¹ Doppio, a JVM written in TypeScript, but it had bitrotted enough that I was having trouble getting it to run, despite confirming it did what I wanted using the official demo, which provides an in-browser console-like interface.

The solution#

I was able to piece together a working simple page using Doppio, modified from the official example. It's in a repository on GitHub and should be straightforward to modify for your needs.

The details#

The problem#

Tametsi (available on Steam¹) is a great logic puzzle game that is a collection of Minesweeper puzzles that can be solved without guessing. The game consists of 100 puzzles plus 60 "bonus" puzzles. The bonus puzzles are in the game directory in an XML-based format that another player has documented well enough that they have even created some puzzles of their own and a viewer for those files. But the base 100 puzzles are nowhere to be found in the puzzle directory, and I had ideas for doing something with them.

The solution#

Given the file DumpPuzzles.java:

public class DumpPuzzles {
    public static void main(String[] args) {
        // Loading puzzles sets the graph on
        //  Game's MouseHandler, so it has to exist.
        game.Game.mh = new io.MouseHandler(null);

        for (int i = 1; i <= 111; i++) {
            puzzle.PuzzleOut.writePuzzle(
                new puzzle.Puzzle(i),
                String.format("puzzle_%03d.puz", i));
        }
    }
}

put it in the same directory as tametsi.exe² and run

$ javac -classpath tametsi.exe DumpPuzzles.java 
$ java -classpath tametsi.exe:. DumpPuzzles

Then the puzzles/ directory will be full of files named puzzle_001.puz, etc.

The details#

The problem#

It's annoying to test boot sequences, either of flash drives or your computer. Rebooting every time you want to test a change is slow and loses your place. Which is especially frustrating if you're using a live USB to attempt to repair a broken bootloader and you have to try multiple times. For a bootable flash drive, at least you might have another computer to test it on, making the problem just mildly inconvenient. But what if you didn't have to boot your computer to test the boot sequence?

The solution#

Of course, you have to boot some computer. But it doesn't have to be a physical one: it can be a virtual machine. While virtual machines often use virtual hard disks backed by files, they can also use real disks. And QEMU's -snapshot flag lets you read from a real disk without actually writing back to it. All changes are stored in temporary storage unless you "commit" them, which you do not want to do for this purpose.

IMPORTANT: Always make sure to use -snapshot when running QEMU on a real disk, especially if that disk is in use by the host system. The following commands also have you set the file permissions so QEMU does not have write access to the disks to be extra careful.

The following will run a virtual machine booting off a flash drive (change /dev/sdc to the appropriate device):

# Grant current user read-only access to flash drive
sudo chgrp "$(id -gn)" /dev/sdc
sudo chmod g=r /dev/sdc
# Boot VM off flash drive
qemu-system-x86_64 -snapshot \
    -net none -machine q35 \
    -bios /usr/share/ovmf/OVMF.fd \
    -cpu host -m 8G -enable-kvm \
    /dev/sdc

To instead boot off your machine's internal drive (assuming it is an NVME SSD with device name /dev/nvme0n1):

uefivars -i efivarfs -o edk2 \
    -I /sys/firmware/efi/efivars -O OVMF_VARS.fd
# Grant current user read-only access to primary SSD
sudo chgrp "$(id -gn)" /dev/nvme0n1
sudo chmod g=r /dev/nvme0n1
# Boot VM off primary SSD
qemu-system-x86_64 -snapshot \
    -net none \
    -drive file=/dev/nvme0n1,if=none,id=nvm \
    -device nvme,serial=deadbeef,drive=nvm \
    -machine q35 \
    -drive if=pflash,format=raw,unit=0,readonly=on,\
file=/usr/share/OVMF/OVMF_CODE_4M.fd \
    -drive if=pflash,format=raw,unit=1,file=OVMF_VARS.fd \
    -cpu host -m 8G -enable-kvm

Those commands require QEMU, KVM, and OVMF installed as well as the Python package uefivars. The paths for OVMF are where Debian installed the files on my system, but they may be in a different place or have slightly different filenames on your system.

Note booting the VM may not be instant: that second command takes almost a minute to reach the GRUB menu on my computer.

The details#

The problem#

You know how to make a bootable flash drive, but you want to actually use it to install a permanent operating system (OS) onto your computer.

The solution#

Luckily, modern OS installs are very straightforward. Just download the installation image (e.g. Debian Linux or Windows 11), put it on a flash drive, boot off it, and click "Next" a few times and wait a bit. The defaults will usually erase all data on the computer, but if it's a new computer, there's nothing to erase.

Of course, you can make things more complicated if you don't like the defaults, have a special situation, or just want to know more precisely what's going on.

The details#

The problem#

Last week, I talked about making bootable flash drives, but didn't go in depth about why you might want to do so.

The solution#

Bootable flash drives have a lot of different uses. The most common ones are simply dealing with an operating system that fails to boot or installing an operating system on a computer that doesn't have one. But there's also some cases where it's useful to not be using the main OS even if it is functional.

The details#

The problem#

So you've built a new computer with fresh blank storage. How do you actually do anything with that computer that has no software? Navigating the BIOS menus can only hold your interest for so long.

The solution#

The old way of doing things was to have a bootable CD or DVD, but now that most computers don't even have an optical drive, the common way to handle this with bootable USB flash drives.

Most Linux distributions' default download is an image for a bootable "live" flash drive (or DVD) that runs the OS in addition to having an option to perform a permanent install. Some of the most popular ones are Debian, Ubuntu, Mint, and Fedora.

You can boot Windows off a flash drive using Hiren's BootCD, which also includes a lot of recovery and diagnostic tools.

While most boot drives will boot into Linux or Windows, there's a small set of specialized lower-level tools. One very useful one is Memtest86+ (included in many Linux distros), which will determine if your RAM is functional. As bad RAM can cause very weird and different to track down problems, you should always test new RAM.

The details#