Research data cleaning with a decision log

Once a day, the raw tab of your research spreadsheet is cleaned by fixed rules into an analysis-ready dataset, with a written record of how it got that way. Values are trimmed and normalised, blanks collapse to one token, numbers, dates and identifiers are parsed, and free text is mapped to your controlled vocabulary. Rows are excluded for duplicates, withheld consent, failed attention checks, speeding, straightlining, missing required values or timestamps outside your collection window, each logged with its reason.

You end up with a 'Clean' tab, a 'Cleaning log' with one line per decision, a 'Needs review' tab, and a 'Runs' tab holding your participant-flow history with a methods paragraph written by AI from the counts alone. Your raw tab is never written to. A rule that cannot decide does not decide: an ambiguous date or an unknown answer is left as it arrived and sent for a person to resolve. If a write fails partway, the run stops and marks that tab incomplete.

Run every day.Run every day.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set rawTab to Raw.Set rawTab to Raw.

Set cleanTab to Clean.Set cleanTab to Clean.

Set logTab to Cleaning log.Set logTab to Cleaning log.

Set reviewTab to Needs review.Set reviewTab to Needs review.

Set runsTab to Runs.Set runsTab to Runs.

Set rulesetVersion to v1.Set rulesetVersion to v1.

Set missingToken to NA.Set missingToken to NA.

Set maxRows to 500.Set maxRows to 500.

Set batchSize to 100.Set batchSize to 100.

Set writeMethodsNote to true.Set writeMethodsNote to true.

Set columnRules to {participant_id:{type:id,pattern:^P[0-9]{4}$,required:true},submitted_at:{type:datetime,formats:["%.Set columnRules to {participant_id:{type:id,pattern:^P[0-9]{4}$,required:true},submitted_at:{type:datetime,formats:["%.

Set rowRules to {consentColumn:consent,duplicateKey:[participant_id],duplicateKeep:first,deduplicateWholeRows:true,"atten.Set rowRules to {consentColumn:consent,duplicateKey:[participant_id],duplicateKeep:first,deduplicateWholeRows:true,"atten.

Generate a short ID code and save it as runId.Generate a short ID code and save it as runId.

Get the current date and time and save it as stampedAt.Get the current date and time and save it as stampedAt.

Google SheetsRead the rows of spreadsheetUrl. Save it as raw.Read the rows of spreadsheetUrl. Save it as raw.

Set rows to raw.rows.Set rows to raw.rows.

Set headers to raw.headers.Set headers to raw.headers.

Count the items in rows and save it as totalRows.Count the items in rows and save it as totalRows.

If totalRows is 0If totalRows is 0

Yes

Stop and report a problem: The raw tab has no data rows, so there is nothing to clean. Check that 'rawTab' names the right tab and that its first row is the column headers.Stop and report a problem: The raw tab has no data rows, so there is nothing to clean. Check that 'rawTab' names the right tab and that its first row is the column headers.

No

No steps

If totalRows is greater than maxRowsIf totalRows is greater than maxRows

Yes

Stop and report a problem: The raw tab has more rows than 'maxRows' allows. Raise 'maxRows' if the run has time for it, or clean the data in slices — this workflow will not write a partial dataset that would look complete.Stop and report a problem: The raw tab has more rows than 'maxRows' allows. Raise 'maxRows' if the run has time for it, or clean the data in slices — this workflow will not write a partial dataset that would look complete.

No

No steps

Count the characters in rows as text and save it as payloadChars.Count the characters in rows as text and save it as payloadChars.

If payloadChars is greater than 700000If payloadChars is greater than 700000

Yes

Stop and report a problem: The raw rows are too large to hand to the cleaning step in one piece (its input is capped at about 1 MB). Lower 'maxRows', or split the raw tab, and run again. Nothing was written.Stop and report a problem: The raw rows are too large to hand to the cleaning step in one piece (its input is capped at about 1 MB). Lower 'maxRows', or split the raw tab, and run again. Nothing was written.

No

No steps

Set config to a record of missingToken, columnRules, and rowRules.Set config to a record of missingToken, columnRules, and rowRules.

Set cleanRows to an empty list.Set cleanRows to an empty list.

Set logRows to an empty list.Set logRows to an empty list.

Set reviewRows to an empty list.Set reviewRows to an empty list.

Set configErrors to an empty list.Set configErrors to an empty list.

Set fatal to blank.Set fatal to blank.

Set offset to 0.Set offset to 0.

While offset is less than totalRows:While offset is less than totalRows:

Run Python code on rows, headers, config, window, run_id, ruleset_version, and stamped_at and save it as batch.Run Python code on rows, headers, config, window, run_id, ruleset_version, and stamped_at and save it as batch.

If batch.fatal isn't emptyIf batch.fatal isn't empty

Yes

Change fatal to batch.fatal.Change fatal to batch.fatal.

Stop repeating.Stop repeating.

No

No steps

Combine cleanRows and batch.clean into one list and save it as cleanRows.Combine cleanRows and batch.clean into one list and save it as cleanRows.

Combine logRows and batch.log into one list and save it as logRows.Combine logRows and batch.log into one list and save it as logRows.

Combine reviewRows and batch.review into one list and save it as reviewRows.Combine reviewRows and batch.review into one list and save it as reviewRows.

Change configErrors to batch.config_errors.Change configErrors to batch.config_errors.

Change offset to offset plus batchSize.Change offset to offset plus batchSize.

If fatal isn't emptyIf fatal isn't empty

Yes

Stop and report a problem: fatal.Stop and report a problem: fatal.

No

No steps

Run Python code on clean_count, log, review, total, and config_errors and save it as summary.Run Python code on clean_count, log, review, total, and config_errors and save it as summary.

For each tabName in [cleanTab, logTab, reviewTab]:For each tabName in [cleanTab, logTab, reviewTab]:

If a step below fails, save the error as tabErrorIf a step below fails, save the error as tabError

Try

Google SheetsCreate the tab tabName in spreadsheetUrl.Create the tab tabName in spreadsheetUrl.

On error

Show in the output: Reusing the existing tab: tabName.Show in the output: Reusing the existing tab: tabName.

If a step below fails, save the error as runsTabErrorIf a step below fails, save the error as runsTabError

Try

Google SheetsCreate the tab runsTab in spreadsheetUrl.Create the tab runsTab in spreadsheetUrl.

Google SheetsAdd a row to runsTab in spreadsheetUrl.Add a row to runsTab in spreadsheetUrl.

On error

Show in the output: Appending to the existing Runs tab..Show in the output: Appending to the existing Runs tab..

Set outputTabs to [a record of tab, header, and rows, a record of tab, header, and rows, a record of tab, header, and rows].Set outputTabs to [a record of tab, header, and rows, a record of tab, header, and rows, a record of tab, header, and rows].

Set writingTab to blank.Set writingTab to blank.

Set rowsWritten to 0.Set rowsWritten to 0.

If a step below fails, save the error as writeErrorIf a step below fails, save the error as writeError

Try

For each output in outputTabs:For each output in outputTabs:

Change writingTab to output.tab. Change writingTab to output.tab.

Change rowsWritten to 0. Change rowsWritten to 0.

Google Sheets Clear the tab output.tab in spreadsheetUrl. Clear the tab output.tab in spreadsheetUrl.

Google Sheets Add a row to output.tab in spreadsheetUrl. Add a row to output.tab in spreadsheetUrl.

For each outputRow in output.rows: For each outputRow in output.rows:

Google Sheets Add a row to output.tab in spreadsheetUrl. Add a row to output.tab in spreadsheetUrl.

Add 1 to rowsWritten. Add 1 to rowsWritten.

On error

Set incompleteNote to INCOMPLETE: run runId cleared this tab and then failed after writing rowsWritten as text data rows (writeError.message). Run the workflow again to rebuild it from the raw tab.Set incompleteNote to INCOMPLETE: run runId cleared this tab and then failed after writing rowsWritten as text data rows (writeError.message). Run the workflow again to rebuild it from the raw tab.

If a step below fails, save the error as markerErrorIf a step below fails, save the error as markerError

Try

Google SheetsAdd a row to writingTab in spreadsheetUrl.Add a row to writingTab in spreadsheetUrl.

On error

Show in the output: Could not mark the '@writingTab' tab as incomplete: markerError.message.Show in the output: Could not mark the '@writingTab' tab as incomplete: markerError.message.

Stop and report a problem: Writing the '@writingTab' tab failed after rowsWritten as text data rows: writeError.message. The Clean, Cleaning log and Needs review tabs are rebuilt in that order on every run, so tabs rebuilt before this one hold this run's output, tabs after it still hold the previous run's, and '@writingTab' is marked INCOMPLETE in its last row. Nothing was added to the Runs tab and the raw tab was not touched. Fix the cause and run again: a full run rebuilds all three tabs.Stop and report a problem: Writing the '@writingTab' tab failed after rowsWritten as text data rows: writeError.message. The Clean, Cleaning log and Needs review tabs are rebuilt in that order on every run, so tabs rebuilt before this one hold this run's output, tabs after it still hold the previous run's, and '@writingTab' is marked INCOMPLETE in its last row. Nothing was added to the Runs tab and the raw tab was not touched. Fix the cause and run again: a full run rebuilds all three tabs.

Set methodsNote to blank.Set methodsNote to blank.

If writeMethodsNote is yesIf writeMethodsNote is yes

Yes

If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteError

Try

ClaudeAsk Claude: 'You are writing the data-cleaning paragraph of a research methods section.Ask Claude: 'You are writing the data-cleaning paragraph of a research methods section. Here are the counts from one automated cleaning run. They are the only facts you have: rule names and totals, no participant data. summary as text Ruleset version: rulesetVersion Write one paragraph, 80-120 words, past tense, third person, no bullet points and no heading. State how many records were read, how many were excluded and under which rules, how many values were changed and how many were left for manual review. Name the ruleset version. Describe only what these numbers say: do not judge the data quality, do not speculate about causes, do not recommend anything, and do not mention any figure that is not above. If reconciles is false, say plainly that the participant counts do not reconcile and that the log needs checking.'. Save it as note. Maximum response length: 700

Trim note.response_text and save it as methodsNote.Trim note.response_text and save it as methodsNote.

On error

Change methodsNote to The methods note could not be written; the cleaned data and the log are unaffected. Reason: noteError.message.Change methodsNote to The methods note could not be written; the cleaned data and the log are unaffected. Reason: noteError.message.

No

No steps

Google SheetsAdd a row to runsTab in spreadsheetUrl.Add a row to runsTab in spreadsheetUrl.

Show in the output: summary.Show in the output: summary.