Runbook · autoSubscriLab

Lab Booking Automation

How the scheduled task claims the measurement-room slots on 實驗室場所借用表單: the weekend straight away, Tuesday and Friday only after asking the lab whether anyone needs them. It also fills in the equipment and room, and takes a slot back when somebody overwrites it.

Spreadsheet

實驗室場所借用表單

Writes as

autosubscrilab-sheet-writer@​protean-topic-445507-v7

Booking name

Sat/Sun 馬嘉鴻 · Tue/Fri by replies

Schedule

1 19 * * 5  (Fri 19:01)

Retry

every 600s until Fri 18:56

Reply window

3 days, or until the slot starts

Document date

2026-10-31

The task books the measurement room for the coming week. Saturday and Sunday are claimed at once under 馬嘉鴻. Tuesday and Friday are asked about first: everyone on the lab list gets a mail, and for each day the first answer decides it: a "no" books the day at once under that person's name, a "yes" keeps it free. With no "no" within three days, the day isn't booked.

The task starts from cron once a week, but it doesn't run only once. It keeps checking every ten minutes for the whole week. A slot held by somebody else today can be free tomorrow, the week tab it needs often doesn't exist yet when the task first fires, and the Tuesday/Friday answers come in over three days.

Everything below describes the system as it actually stands, verified against the live sheet and mail server rather than inferred from the code.

Read it top to bottom. Each ten-minute pass runs the weekend block and the Tuesday/Friday column, then folds their results into one exit code. Blue blocks are outside the machine. The ask goes out once, the decision is made once, and only the writes repeat until every slot is settled. The sections below go through each block in detail.

1What each requirement maps to

2Tuesday and Friday: ask first

On its first attempt each run, the wrapper has ask_slots.py mail every person in people.json, one mail each. The ask is recorded in asks.json, keyed by the week's Monday, so retries never send it twice. If the send fails, nothing is recorded and the next attempt tries again.

The ask mail

It is written to be polite and to say plainly what a reply leads to: a day answered "no" may be booked under that person's name, the slot is actually used by Carlos Ma (馬嘉鴻), that the first answer for a day decides it, and that not replying keeps their name off. Each day shows its own reply deadline. It also says the system is still being tested, and asks forgiveness for any booking it gets wrong. Chinese first, the same in English below. Dates and deadline are filled in per week; this is the text as the code generates it.

Subject: [lab-slot 〈Monday〉] 想請問下週 〈Tue〉(二)、〈Fri〉(五) 實驗室時段的使用意願 / Lab slots next week

〈name〉 你好，

不好意思打擾了。想請問你下週是否會用到實驗室的以下時段（0800~1200 與 1200~1600）：
  〈Tue〉(二)　請於 〈deadline〉 前回覆
  〈Fri〉(五)　請於 〈deadline〉 前回覆

方便的話，請直接回覆這封信，例如：
  二: 要
  五: 不要

說明一下這些回覆會怎麼使用（每一天各自以最先收到的回覆為準）：
  - 如果最先收到的是「要」，那天就會保留下來，請需要的人自行登記。
  - 如果最先收到的是你的「不要」，系統會立刻以你的名字登記這個時段，實際上這個時段會由馬嘉鴻 (Carlos Ma) 使用。
  - 在期限前都沒有人回覆「不要」的話，那天就不會登記。沒有回覆不列入計算，也不會用你的名字登記；所以如果不希望用你的名字登記，不回覆就可以了。

另外，這套自動登記系統目前還在測試階段，如果出了差錯而登記錯誤，還請多多包涵，也歡迎直接告訴我，我會儘快修正。

謝謝你撥空回覆！
馬嘉鴻

---
Hi 〈name〉,

Sorry to bother you. Could you let me know whether you will need the lab
next week on these days (0800~1200 and 1200~1600)?
  Tue 〈Tue〉   please reply by 〈deadline〉
  Fri 〈Fri〉   please reply by 〈deadline〉

If you can, please just reply to this mail, e.g.
  Tue: yes
  Fri: no

How the replies are used (for each day, the first answer received
decides it):
  - If the first answer is yes, that day is kept free for them to book.
  - If the first answer is your no, the system books that slot under
    your name right away, and the slot will actually be used by
    Carlos Ma (馬嘉鴻).
  - If nobody answers no by the deadline, the day is not booked. No
    reply is not counted, and your name will not be used. So if you
    would rather not have your name on a booking, simply don't reply.

Please note this booking system is still being tested. If something
goes wrong and a slot is booked by mistake, please forgive me, and do
let me know so I can put it right.

Thank you for taking the time!
Carlos (馬嘉鴻)

From then on, every pass reads the replies and looks at each undecided day on its own. The first reply that answers a day decides it, at once and for good. Each day has its own window: three days from the ask, but never past 08:00 on that day, since a slot that has started isn't worth asking about any more. While a day is undecided it counts as not yet booked (exit 6), which keeps the retry loop alive.

Booked days go through the same planner as the weekend, with --ref set to the run's start date so they land on the week that was asked about, never whichever tab happens to be newest. The name written is the person who said no first. If the cell's dropdown doesn't contain that name (exit 5), the write fails on every pass and the day stays unbooked, so names in people.json must match the sheet's.

Which replies count

3How the sheet is shaped

Almost nothing about the grid is hardcoded, and that is deliberate: the sheet is edited by a couple of dozen people and its shape moves. Only the spreadsheet id is fixed. Everything else is located by reading the sheet each run.

Each week lives on its own tab, titled with its date range, like 11/2~11/8. Within a tab, column E carries the time-slot labels and row 3 carries the day headers. A slot occupies a twelve-row block holding two independent booking groups of six rows each, and inside a group the rows have fixed roles:

Why the day columns are matched, never counted

The day columns are not in calendar order. On the current tab they run Mon, Tue, Wed, Fri, Sat, Sun across F to K, with a 特殊時段 column at L and Thursday stranded out at M. Counting columns would land on the wrong day silently and book somebody else's afternoon. So the script finds a day by searching row 3 for the (二), (五), (六) or (日) marker instead.

The same reasoning applies to values. Every value written is read back out of the target cell's own dropdown before use, so a near-miss like 馬家鴻 for 馬嘉鴻 fails loudly instead of being written and silently ignored by whoever reads the sheet. The dropdown holds sheet names, which aren't always the names on the mail list: it has Chinu and MD, for instance.

4What happens to each target cell

Every pass considers the four weekend cells and, once decided, the booked Tuesday/Friday cells. The rule that decides each one is deliberately narrower than a blanket overwrite: a slot is only taken from somebody if it was ours first.

The proxy row exists so that a person who booked a slot themselves before the decision doesn't have their equipment selection rewritten, or their slot "taken back" in their own name later. The wrapper turns it on with BOOK_WEEK_PROXY=1 for Tuesday/Friday calls only.

A cell that holds neither the booked name nor No one is never assumed to be available. A blank cell in particular is ambiguous: it may be a slot nobody has touched, or one somebody cleared on their way to filling it in. So it is left alone. The --force flag overrides all of this, and the scheduled task never passes it.

5One pass, step by step

Each set_week.sh call is itself the same pipeline as before: service-account token, choose the tab, fetch A1:M100 plus the library tab with dropdown rules, plan, one batchUpdate, and promote the claims only after the write returns 200.

Exit 6 deserves emphasis: it is the normal state, not a fault. It means the task did everything it could this minute and intends to come back. Two days of exit 6 while the lab answers, followed by a single exit 0, is the system working exactly as designed.

6The state files

Telling "somebody took my slot" apart from "this slot was never mine" needs memory, and claims.json is it. It records, per tab, the cells that have been observed holding the booked name:

{
 "11/2~11/8": [ "G16", "I16", "I28", "J16", "J28", "K16", "K28" ]
}

asks.json is the memory for the Tuesday/Friday question. It holds one entry per week: when the ask went out, the deadline, who was asked, the Message-IDs replies are matched against, and, once made, the parsed answers and the decision. Both it and people.json hold personal email addresses and are gitignored.

{
 "2026-11-09": {
  "ref": "2026-11-06", "sent_at": …, "deadline": …,
  "asked": [ … ], "message_ids": [ … ],
  "decision": {
   "Tue": {"action": "book", "names": ["鍾毓星"], "answered": "no",
           "at": "2026-11-07 09:12:40"},
   "Fri": {"action": "skip", "names": [], "answered": null,
           "at": "2026-11-09 19:01:00"} }
 }
}

7Which tab each part targets

The weekend uses --latest, the newest week tab. Next week's tab is often created only a few days ahead, so the current week keeps being checked until it appears, and then the retries move onto it without anyone intervening.

Tuesday and Friday use --ref instead: the week starting the Monday after the run began, the same week the mail asked about. If that tab doesn't exist yet the pass exits 2 and retries. A decision about 11/10 can never be written onto 11/3.

8What arrives in your inbox

9The week, end to end

The first run with the ask step is Fri 6 Nov 19:01, asking about Tue 11/10 and Fri 11/13, with replies counted until Mon 9 Nov 19:01. The run started 30 Oct still uses the old flow, booking Tue/Fri under 馬嘉鴻, until it ends 6 Nov 18:56.

10Operating it by hand

--dry-run is safe in the strongest sense: it prints the exact request body it would have posted, and touches neither the sheet nor the claims file. Passed to book_week.sh, it reaches every set_week.sh call, but not the ask mail. Preview that with ask --dry-run.

11Where things stood on 31 Oct

This week was booked by the old flow. G28 is the case the reclaim rule deliberately does not cover: it has never been ours, so it is left alone, and the run keeps exiting 6 on it until the week ends.

Ask and reply, tested live

Run from a sandbox copy with 馬嘉鴻 as the only person asked, so the real asks.json and the Friday run were untouched.

12Open items

13File reference