Cleans a research dataset by fixed rules and writes down every change, exclusion and unresolved value as it makes it.
Uses
Claude
Google Sheets
Research data cleaning with a decision log
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 0Yes
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 maxRowsYes
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 700000Yes
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 emptyYes
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 emptyYes
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 tabErrorTry
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 runsTabErrorTry
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 writeErrorTry
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 markerErrorTry
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 yesYes
If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteErrorTry
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: 700Trim 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.