Keeping goshuin in a spreadsheet: the columns to use, and three places it breaks

This article covers two things only: which columns to use when you build a list of your goshuin in a spreadsheet, and the three places it breaks once you have built it. How to drive the spreadsheet itself, and what any particular shrine or temple offers, are outside it.

Which columns do you use?

Ten: row number, book, page, visit date (Western), visit date (Japanese era), shrine or temple, reading, how sure the date is, where the photo is, and your note. That is the six things any record of a goshuin needs — visit date, shrine or temple, photo, note, book, page — with four more on top: the reading, the era date, a row number, and how sure the date is.

Which fields are worth keeping at all is in the fields you can add later and the ones you cannot, and what a list lets you look back at is in how to think about holding your records as one list. Here it is only the decisions you make when those fields become spreadsheet columns.

Those are the eight columns a list needs, plus two — the row number and how sure the date is — that carry a value on every single row. The habit of pushing anything you write on some days and not others into the note does not change.

You do not have to fill all ten from the first row. The two that belong to the spreadsheet rather than to the record, the row number and how sure the date is, get filled the moment you add the row: a number assigned after a sort no longer records the order you entered things in, and how sure a date is can only be marked while you still remember where the date came from. Book and page can wait, on the same rough guide the article on choosing fields gives, until your goshuin book reaches its second volume. The era date, the reading and the note can all be added later.

The column for how sure the date is exists to record where the date came from. A date you confirmed against the page and a date you entered provisionally from when the photo was taken look identical in a spreadsheet, and a few years on you cannot tell them apart. Keep three values — certain (confirmed against the date on the goshuin), estimated (taken from the capture date or the photos either side), unknown — and you can narrow the sheet down to exactly the rows worth checking again. Every row carries one, which is why it is a column rather than something buried in the note. How to gather the clues for an estimate is in tracing the visit date of a goshuin that carries no date.

Where does a goshuin spreadsheet break?

In three places: the photos stop matching their rows, sorting on the era date puts the pages in the wrong order, and after a sort you cannot get back to the order of the book and its pages. None of them shows while the sheet is short, and by the time you notice there are more rows to fix.

Breakage 1: the photo and the row come apart

The sheet and the photos live in different places, so unless the sheet carries a way of getting from a row to its photo, you will be hunting one photo at a time. You know perfectly well that row 12 is the goshuin from a particular shrine, and you still open photo after photo from around that date in your photo app — once for every row.

Matching by position — "twelfth from the top in the photo app" — is the easy way, and it drifts the moment you delete one photo or insert one row, with nothing to tell you where the drift started. What you want as a handle is something that survives a sort, and survives deleting another row or another photo.

Some spreadsheets let you put an image inside a cell, but at a few hundred photos the spreadsheet file itself tends to get heavy. Keeping the photos outside the sheet and only their whereabouts inside it lasts longer.

Breakage 2: sorting on the era date scrambles the order

Sort an era date as text and you do not get the order you visited in. Where the program compares text one character at a time from the start, year 10 lands before year 9, and month 12 before month 4.

Reiwa does the same: 令和2年 comes before 令和元年, the first year of an era being written 元年 rather than with a numeral. Some programs can read an era date as a date, but which ways of writing it they accept differs from program to program.

The way round it is to sort on the Western-calendar column only, and treat the era column as the one you hold up against the page. Written as 2019-05-01, with a four-digit year and two digits each for month and day, that column sorts correctly whether the program treats it as dates or as plain text.

To convert an era year, count 元年 as year 1 and add 2018 for Reiwa, 1988 for Heisei, 1925 for Showa. The changeover years are the ones addition alone cannot settle: 2019 is Heisei 31 up to April 30 and Reiwa 1 from May 1, so if 平成31年5月 or 令和元年4月 turns up in your sheet, suspect a misreading or a bad conversion. The days the eras changed, and why records slip there sets those dates out as a table, the Showa-to-Heisei boundary in 1989 included.

Breakage 3: you cannot get back to the book-and-page order after a sort

Getting a sheet you sorted by visit date back into the order of the pages needs two things to be true: book and page are in as numbers, and you had the whole table selected when you sorted. Miss either one and it goes like this:

1. The book is written out as text — "book 1", "book 2", "book 10". Compared a character at a time, book 10 lands before book 1. Put book and page in as numbers alone.
2. You selected a single column and sorted that. Depending on the program and on what you selected, only the column you picked moves, and the shrine name and the photo beside it stay where they were. The contents of one row end up belonging to different goshuin while the sheet still looks perfectly tidy, which is why this is the one noticed late. Always select the whole table before you sort.

To get back, give the sort two keys in order: book, then page. Sorting on the book alone does not order the pages within a book. And if you have that column of row numbers assigned in entry order, sorting on it returns the sheet to the way it first stood. If you only want to look at something, a filter leaves the order of the sheet itself alone.

Who is a spreadsheet the better fit for?

Anyone who wants counts and totals, and anyone who wants to take the whole list in on a big screen. Counting entries per year, or how many times you have been to the same shrine or temple, is exactly what a spreadsheet's totals are for, and you can add any column you want to keep for yourself — an itinerary, say. If you already have years of it in a sheet and have not tripped over the three above, there is no reason to change tools.

What this app does with it

Goshuin Ledger, the app I make, holds the fields that match eight of the ten columns above, and ties each photo to a single record from the start. Inside a book the records sit in page order, and a search on the shrine or temple name, its reading, your note, the Japanese-era visit date or the type comes back newest visit first, each result carrying which book and which page it is on. Searching does not move the order inside a book.

The bridge to a spreadsheet is the CSV export, which comes with Ledger Full. One row per goshuin, with the shrine or temple name, the visit date in both its Japanese era and the Western calendar, the book and page, and your note, in a form Excel opens without garbled characters. It differs from the ten columns above in three ways.

• The book column holds the name you gave the book in the app, not a book number. To sort by book, add a numeric column on the spreadsheet side.
• There is no row number column and none for how sure the date is. Put the certainty in your note and it stays in the CSV.
• The photos themselves are not in it, and you cannot open a photo from the spreadsheet. To look back with the photos, use the PDF export.

The columns the CSV holds, and what goes into the PDF and into the copy you make for a phone change, are in the three kinds of file you can export.

A spreadsheet you built cannot be read into this app. If you already have one, there are three ways to go.

1. Carry on with the sheet. If the three breakages are not troubling you, this is the least work by far.
2. Leave what you have in the sheet, and record what comes next in the app. Copy across from the exported CSV whichever rows the sheet does not have yet.
3. Import from the photos again and move over. The names and readings have to be typed in again.

Recording in your first book needs no purchase and has no limit on how many entries it holds. A second book and beyond, adding imported photos to a book all at once, and PDF / CSV export come with the one-time Ledger Full. The App Store shows the current price. What the purchase covers is set out in the pricing section of this site. It runs on iPhone with iOS 16.4 or later. It does not sync between devices, so to read your records on a computer you open an exported CSV or PDF.

One thing left over: why records are held in units of books and pages at all is written up separately, as keeping your books from running together as they multiply.