Research data cleaning with a decision log

Turns a raw collection sheet into an analysis-ready dataset without losing the record of how it got that way. It reads your raw tab, never writes to it, and produces four things: a Clean tab, a Cleaning log with one line per decision, a Needs review tab, and an append-only Runs tab that is your participant-flow history.

Every decision is made by a fixed rule in the Code step — no model, no network, no clock, no randomness — so the same raw data and the same ruleset produce the same dataset every time. Values are trimmed, Unicode-folded and stripped of invisible characters; the many spellings of a blank collapse to one token; numbers, dates, yes/no answers and identifiers are parsed; free text is mapped to your controlled vocabulary. Rows are excluded for duplicates, withheld consent, failed attention checks, speeding, straightlining, a missing required value, or a timestamp outside your collection window — each one logged with the rule and the reason, never silently dropped.

The rule that matters most: a rule that cannot decide does not decide. A date that could be 3 April or 4 March, a comma that could be a decimal or a thousands separator, an answer that is not in your vocabulary, an ID a spreadsheet has already mangled into scientific notation — the value is left exactly as it arrived and the question goes to the Needs review tab for a person to answer. A column named in your rules but missing from the sheet is reported rather than ignored, and a missing consent column stops the run outright.

Before you run it: paste your spreadsheet URL, then edit 'columnRules' and 'rowRules' to your own columns — what ships is a worked survey example. Set rowRules.collectionWindow to your study's start and end dates (YYYY-MM-DD): it ships open, so until you do no row is excluded as out of window, and the Runs tab says so under config_warnings. Bump 'rulesetVersion' whenever you change a rule; it is stamped on every cleaned row and every log line. Claude writes the methods-section paragraph on the Runs tab from the counts alone and never sees participant data or touches a value; set 'writeMethodsNote' to false to leave it out.

Connections needed: Google Sheets, and Anthropic for the methods note. The Clean, Cleaning log and Needs review tabs are rebuilt on every run; the Runs tab is only ever appended to. It writes one row at a time, so 'maxRows' defaults to 500 and the run stops rather than writing a partial dataset that would look complete. If a write fails partway through a rebuild, the run stops with a message naming the tab, writes INCOMPLETE into that tab's last row so it cannot pass for a finished dataset, and adds nothing to the Runs tab; running it again rebuilds all three tabs from the raw tab.