Data Cleaning Automation in Google Sheets
Introduction
Data Cleaning Automation automates Flookup's core data cleaning operations so it runs in the background without your supervision. This is ideal for keeping large datasets consistently clean as new data arrives. You can schedule tasks at intervals ranging from every 15 minutes to once a day, with intervals up to 7 days.
If you only need a one-off lookup or spot check without scheduling, the Data Cleaning Spreadsheet Formulas may be simpler.
To open the scheduling sidebar, navigate to Extensions > Flookup Data Wrangler > Data Cleaning Automation in your Google Sheets menu.
Data Nova required. Data Cleaning Automation is available on the Data Nova plan. It does not consume AI credits. See pricing for details.
How to Schedule a Function
- Select the function mode. Choose the operation you want to automate from the top dropdown. The form updates to show only the relevant options for that function.
- Choose the processing mode. Select whether to process data to the end once or loop continuously. See Processing Modes below.
- Configure the data ranges. Define your data sources. Highlight the range in your sheet and click the corresponding Grab selected range button to populate each field.
- Set the output position. Results are written starting from the active cell at the moment you click Schedule. Select the correct starting cell before scheduling.
- Adjust parameters. Set the threshold, column indexes and operation type. See Function Modes for details on each.
- Choose a frequency mode. Select how often the task should run. See Frequency Modes.
- Click Schedule. A status indicator at the top of the sidebar confirms the schedule has been created.
Tip: Before scheduling, run the function manually on a small sample of your data using Data Cleaning Tools to identify the best parameters.
Function Modes
Four function modes are available for scheduling. Each requires different inputs.
Fuzzy Match by Percentage
Performs percentage-based fuzzy lookups on a schedule. Each lookup value is compared against a table column and the best match is returned.
Required inputs: Lookup values range, Table values range, Lookup column, Return column and a Threshold.
Get Unique Values by Percentage
Extracts unique rows from a data range across scheduled runs by grouping near-duplicates using percentage similarity. Only one representative entry per group is returned.
Required inputs: Data range and a Threshold.
Standardize Text Entries
Cleans text on a schedule using the same operations available in Data Cleaning Tools: remove diacritics, filter stop words, strip punctuation, extract URL domains or paths or learn transformation patterns from examples.
Required inputs: Input range and an Operation type. For text and punctuation operations, provide a Stop array range. For pattern learning, provide dirty and clean example ranges. See Pattern Training for Standardize.
Compare String Similarity
Computes percentage similarity scores between two string columns on a scheduled interval.
Required inputs: Left range, Right range and Comparison mode (by word or by phrase).
Processing Modes
Processing mode determines what happens after the function finishes a full pass over your data.
- Process data to the end: The task processes every row once and then stops automatically. Best for one-off cleaning jobs where you want the function to complete and notify you.
- Process data in a loop: After reaching the last row, the task restarts from the beginning. Best for ongoing data maintenance where new rows are added regularly. You must manually stop the chain when it is no longer needed.
Frequency Modes
Three frequency modes control how often your scheduled tasks execute.
- Run every few minutes (Minutes mode): Choose an interval of 15, 30, 45 or 60 minutes. Each execution processes as many rows as possible within its time window and then schedules the next run automatically. This is the recommended mode for large datasets because it chains executions until the job is complete. See How Auto-Chaining Works.
- Run every few hours (Hourly mode): Set the number of hours between runs (1 to 24). Suitable for datasets that receive periodic updates throughout the day.
- Run daily at specific time (Daily mode): Set a specific time of day and the number of days between runs. When a time is specified, the maximum interval is 7 days. Ideal for end-of-day or weekly batch cleaning.
How Auto-Chaining Works
Minutes mode uses auto-chaining to handle datasets larger than what can be processed in a single execution. Instead of trying to process everything at once, the system works in manageable chunks.
Here is what happens during each chain link:
- The scheduled function wakes up at its appointed interval.
- It picks up from the last row it completed, using saved progress from the previous run.
- It processes as many rows as it can within the available time.
- It writes the results to the sheet so you can see progress.
- It schedules a one-time trigger for the next link in the chain.
This cycle repeats until one of the following occurs:
- All rows have been processed (chain completes).
- The daily execution quota is exhausted (chain pauses and resumes the next day if rescheduled).
- You manually stop the chain using the Stop Chain button.
- An error occurs that the system cannot recover from after repeated attempts.
How processing modes interact with auto-chaining:
Process to the end + Minutes mode:
The chain runs until every row has been processed once, then stops automatically. This is the most common combination for large one-off jobs.
Loop + Minutes mode:
The chain runs indefinitely, restarting from the beginning after each full pass. Use this for datasets that are continuously updated.
Pattern Training for Standardize
When scheduling a
Standardize Text Entries
function with the
pattern
operation, you can teach the system transformation rules by providing example pairs instead of choosing from predefined operations.
Select Learn from examples from the operation dropdown. Two additional fields appear: Dirty values range and Clean values range. These are your example pairs.
Click Test pattern to preview the detected transformation. The sidebar shows a table comparing each dirty value with its expected clean result. Matching rows appear in green; mismatches indicate you may need to provide more diverse examples.
For detailed guidance on preparing example pairs and best practices, see the Learn from Examples guide.
Managing Schedules
- Stop Chain: Halts an active chain immediately while preserving all results written so far. You can resume later by scheduling the same function mode again with the same parameters. The Stop Chain button is visible in the sidebar while a chain is active.
- Reset: Completely removes a scheduled task and clears all saved progress. Use this when you want to start fresh or stop an Hourly or Daily schedule.
- Updating a schedule: There is no edit function. To change settings, first Reset the existing task, then create a new schedule with your updated parameters.
- Independent schedules: Each function mode maintains its own independent schedule. You can have a Fuzzy Match chain and a Standardize chain running simultaneously on the same sheet. The status bar in the sidebar shows information about the currently selected function mode.
Email Notifications
Flookup sends email notifications for key events so you are always aware of your scheduled tasks:
- Task completed: Sent when a chain in "Process data to the end" mode finishes processing all rows. The email includes the number of rows processed and the function type.
- Task stopped: Sent when a chain stops for any reason other than successful completion, such as quota exhaustion, subscription issues or errors. The email includes the reason for stopping and the number of rows processed so far.
Notifications are sent to the email address associated with your Google account. If you do not receive emails, check that messages from our support address are not being filtered by your email provider.
Important Notes
- Daily execution quota: All scheduled tasks share your Google account's daily execution limits. Consumer accounts receive a generous allocation that is sufficient for most workflows. Workspace accounts receive a larger allocation. If the quota is exhausted, the chain stops automatically and you receive an email. You can reschedule the next day.
- Sheet renaming: Renaming a sheet after scheduling will not break an existing schedule. The system identifies your target sheet by more than just its name.
- Sheet deletion: Do not delete the target sheet after scheduling. If the sheet is deleted, the task will fail on its next run.
- Authorization: Schedules run under the authority of the user who created them. If you revoke the add-on's authorization, all your scheduled tasks will stop. Re-authorizing the add-on restores functionality.
- Closing the sidebar: The sidebar can be closed safely after scheduling. All processing happens in the background. Reopen the sidebar at any time to check status, stop a chain or reset a schedule.
Frequently Asked Questions
When should I use Data Cleaning Automation instead of Data Cleaning Tools?
Use Data Cleaning Automation for recurring jobs, large datasets and long-running operations. Use Data Cleaning Tools for quick interactive processing where you want to see results immediately and stay in control.
Can I stop a running schedule without losing progress?
Yes. Click Stop Chain to halt processing while preserving progress. You can resume later by scheduling the same function mode again with the same parameters.
How often can I run scheduled tasks?
You can schedule tasks from every 15 minutes up to daily, with a maximum interval of 7 days. More frequent schedules process data faster but consume your daily execution quota more quickly.
What happens during auto-chaining if the system encounters an error?
The system retries automatically a few times. If the error persists after repeated attempts, the chain stops permanently and you receive an email with the details. You can then investigate and reschedule if needed.
Can I schedule multiple functions at the same time?
Yes. Each function mode runs independently. You can have Fuzzy Match, Standardize and Compare Similarity all running simultaneously on the same spreadsheet.