Schedule Data Cleaning Functions in Google Sheets
Introduction
Schedule Functions let you automate Flookup's core operations, such as Fuzzy match, Deduplication and Standardise text, so they run on their own without any manual intervention. This is ideal for keeping large datasets consistently clean, especially when new data is added regularly.
If you want to review probable duplicate groups visually before any removal, start with Fuzzy Conditional Formatting. If you want non-destructive formula output, use DEDUPE in Custom Functions.
You can schedule tasks at intervals ranging from every 15 minutes to once a month. For large datasets, the Minutes mode is recommended as it handles long-running jobs seamlessly.
To open the scheduling sidebar, navigate to Extensions > Flookup Data Wrangler > Schedule Functions in your Google Sheets menu.
Tip: Before scheduling, run the function manually on a small sample of your data to identify the optimal parameters.
Power Feature: The Learn from Examples operation allows you to teach transformations by demonstrating patterns with example data. Instead of choosing from predefined operations, show the system what dirty data looks like and what the clean version should be. The pattern synthesis engine then automatically detects the transformation logic and applies it to your entire dataset. This is ideal for complex cleaning tasks that do not fit neatly into standard operations.
How to Schedule a Function
- Select the function mode
Choose the operation you want to automate from the top dropdown, such as Fuzzy match by percentage or Standardize text entries. The form will update to show only the relevant options for that function. -
Choose the processing mode
Select how you want the task to handle your data:- Process data to the end: The task processes every row once and then stops. Best for one-off cleaning jobs.
- Process data in a loop: After reaching the last row, the task restarts from the beginning. Best for maintaining ongoing data integrity.
- Configure the data ranges
Define your data sources, such as Lookup_values, Table_values or Input range. Highlight the range in your sheet and click the corresponding Grab selected range button to populate the field. - Set the output position
Results will be written starting from the active cell at the time you create the schedule. Make sure you have selected the correct starting cell in your spreadsheet before clicking Schedule. - Adjust parameters
Specify function-specific settings such as Threshold, column indexes or operation types. See Tool-Specific Configuration below for details on each function. -
Choose a frequency mode
Select how often the task should run from the Frequency mode dropdown. See the Frequency Modes section below for a detailed explanation of each option. - Schedule the function
Click Schedule to create the automated trigger. A status bar at the bottom of the sidebar will confirm the schedule was created.
Learn from Examples by Pattern Synthesis
The Learn from examples operation is a powerful pattern synthesis engine that automatically learns transformation rules from your data.
Instead of choosing from predefined operations, you show the system examples of what your dirty data looks like and what the clean version should be.
The engine then figures out the transformation logic and applies it to your entire dataset.
This feature is ideal when your data cleaning needs do not fit neatly into standard operations like "remove diacritics" or "extract domain".
For example, if you need to combine multiple transformations (remove special characters, collapse whitespace and convert to title case), you can demonstrate this with a few examples and the system will learn the pattern.
Real-World Examples
| Task | Dirty | Clean | Description |
|---|---|---|---|
| Phone number reformatting | +1-555-123-4567 | 555 123 4567 | The system learns to remove the country code, strip dashes and reformat with spaces. |
| Product code standardisation | PROD-2024-001 v2 | PROD2024001 | Removes dashes, version suffixes and extra spaces. |
| Name standardisation | SMITH, JOHN (Mr.) | John Smith | Removes titles and punctuation, then converts to proper title case. |
| URL cleanup | https://example.com/page?utm=1 | example.com | Removes protocol and query parameters, extracting the domain. |
How to Use It
-
Prepare your example data
Create two columns in your spreadsheet: one with dirty values and one with the corresponding clean values.
You need at least 2 example pairs, but 3-5 examples typically give better results. The more examples you provide, the more accurately the system can identify the transformation pattern. - Select the operation
In the scheduling sidebar, choose Standardize text entries as the function mode, then select Learn from examples from the operation dropdown. - Provide the example ranges
Two new fields will appear: Dirty_values and Clean_values. Use the Grab selected range buttons to select your example columns. These are just for teaching the system the pattern, they are not the data you want to clean. The ranges must have the same number of rows. - Test the pattern
Click the Test pattern button. The system will analyse your examples, synthesise a transformation pipeline and show you a preview table with the results. Each row displays the original dirty value, the expected clean value and the actual result. Matching results are shown in green, mismatches in red. - Review the detected rule
Below the preview table, you will see the detected transformation rule (for example: "removeSpecialChars, then collapseWhitespace, then toTitleCase"). This shows you exactly what operations the system identified. - Configure the Input range
In the Configuration card above, make sure you have set the Input range to the data you want to clean. This is separate from your example ranges — the examples teach the system the pattern and the Input range is where that pattern gets applied. - Schedule the transformation
If the preview looks correct, click Schedule to apply the learned pattern to your full dataset. The transformation will run on the Input range you specified, using the pattern learned from your examples.
What Transformations Can It Learn?
The pattern synthesis engine can learn combinations of up to 24 primitive transformations, including:
- Case conversions: to lowercase, uppercase, title case, sentence case
- Character removal: remove digits, letters, special characters, whitespace, diacritics
- Character extraction: keep only digits, letters or alphanumeric characters
- Whitespace handling: trim, collapse multiple spaces
- Punctuation replacement: replace commas, dots, hyphens, underscores with spaces or remove them
- Bracket removal: remove parentheses, square brackets, curly braces
The engine tries single transformations first, then combinations of two, then three, always selecting the simplest pipeline that satisfies all your examples.
Best Practices
- Provide diverse examples: Include different types of dirty data in your examples. If your data has various issues (extra spaces, wrong case, special characters), make sure your examples cover all of them.
- Start with 3-5 examples: Whilst the minimum is 2, providing 3-5 examples significantly improves pattern detection accuracy. More examples help the engine eliminate incorrect hypotheses.
- Test before scheduling: Always click Test pattern and review the preview. If any results show in red (mismatch), adjust your examples or add more until all results match.
- Keep examples representative: Your examples should be typical of the data you want to clean. Do not include edge cases that are very different from your main dataset.
- Check the detected rule: The detected rule tells you what transformations were identified. If it does not make sense for your use case, you may need to provide clearer examples.
Troubleshooting
| Issue | Explanation |
|---|---|
| "No pattern found" | This means the engine could not find a transformation that converts your dirty examples to the clean examples. Check that your examples are correct and that the transformation is one of the supported primitives listed above. |
| "No common pattern found across all examples" | Your examples require different transformations. For instance, if one example needs "remove digits" but another needs "remove letters", there is no single pattern that works for both. Provide more consistent examples. |
| "Ranges must have the same number of rows" | Your dirty and clean ranges must be the same length. Check that you selected the correct ranges. |
| "At least 2 complete pairs required" | You need at least 2 rows where both the dirty and clean values are filled in. Empty cells are skipped. |
| Preview shows mismatches | The detected pattern does not work for all your examples. Add more examples to help the engine identify the correct pattern or check if your examples are consistent. |
Frequency Modes
Flookup offers three frequency modes to suit different automation needs:
- Run every few minutes: Choose an interval of 15, 30, 45 or 60 minutes. This is the recommended mode for large datasets because it processes data continuously until the job is done. See the Minutes Mode for Large Datasets section below for full details.
- Run every few hours: 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: Set a specific time of day and the number of days between runs (1 to 30). Ideal for end-of-day or weekly batch cleaning. If a time is set, the frequency is capped at 7 days.
Minutes Mode for Large Datasets
Minutes mode is designed for large datasets and will continue processing until one of the following occurs:
- Process all the selected data: The chain stops and you receive a completion email.
- Your daily execution quota is exhausted: The chain pauses and you receive an email. It will resume the next day when you reschedule.
- An error occurs after multiple retries: The chain stops and you receive an email with the error details.
- You manually stop it: Click the "Stop Chain" button in the sidebar. Your progress is preserved.
Interaction with Processing Modes
- Process data to the end + Minutes mode: The chain runs until every row has been processed exactly once, then stops automatically. This is the most common combination for large one-off jobs.
- Process data in a loop + Minutes mode: The chain runs indefinitely, restarting from the beginning after each full pass. Use this for datasets that are continuously updated and need constant re-processing. You must manually stop the chain when it is no longer needed.
Status Indicator
While a chain is active, a status bar appears at the top of the sidebar showing that the chain is running. The Stop Chain button also becomes visible, allowing you to halt the chain at any time without losing your progress.
Tool-Specific Configuration
Each function mode requires slightly different inputs. Here is a summary:
- Fuzzy match by percentage: Requires Lookup_values, Table_values, Lookup_column, Return_column and a Threshold (0 to 1).
- Fuzzy match by sound: Same as above but without a Threshold setting. Matching is based on phonetic encoding rather than string similarity.
- Extract unique values by percentage: Requires Data range and a Threshold. Removes near-duplicate entries based on string similarity.
- Extract unique values by sound: Same as above but without a Threshold. Uses phonetic encoding to identify duplicates.
- Standardize text entries: Requires Input range and an operation type. Available operations include Remove diacritics, Remove stop words, Remove punctuations, Extract domain from URL, Extract path from URL and Learn from examples. The Learn from Examples operation uses pattern synthesis to automatically detect transformations from your example data — ideal for complex cleaning tasks. For stop words and punctuations, you can also provide a Stop_array range.
- Compare string similarity: Requires a Left_string range and a Right_string range. You can compare By word or By phrase. Outputs a similarity score for each row pair.
Managing Scheduled Tasks
- Stopping a Chain: If you have an active chain running in Minutes mode, a Stop Chain button will appear at the bottom of the sidebar. Click it to halt the chain immediately. Unlike Reset, this preserves your progress, so you can resume later by scheduling again with the same parameters.
- Resetting a Schedule: To completely remove an automated task, open the scheduling sidebar, select the relevant function mode and click Reset. This deletes the trigger and clears all saved parameters. Use this when you want to start fresh or stop an Hourly/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.
- Switching Function Modes: If you switch the function mode dropdown while a schedule is active, the status bar will reflect whether that mode has an active chain. Each function mode maintains its own independent schedule and state.
Email Notifications
Flookup sends email notifications for key events so you are never left guessing about the status of your scheduled tasks:
- Task completed: Sent when a chain in "Process data to the end" mode finishes processing all rows. Includes the number of rows processed.
- Task stopped: Sent when a chain stops for any reason other than successful completion, such as quota exhaustion, license issues or errors. 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, ensure that messages from our support team are not being filtered by your email provider.
Technical Notes and Troubleshooting
- Execution Time Limit: Google Apps Script enforces a maximum execution time of approximately 6 minutes per run. Flookup's scheduling engine handles this automatically by saving progress and resuming from where it left off in the next chained execution.
- Daily Quota: All triggers share your account's daily execution quota, which is approximately 90 minutes for consumer (@gmail.com) accounts and 6 hours for Google Workspace accounts. If the quota is exhausted, the chain stops automatically and you receive an email. You can reschedule the next day.
- Sheet Renaming: The scheduling engine identifies sheets by their internal ID, not their name. Renaming a sheet will not break an existing schedule. However, deleting a sheet will cause the task to fail on the next run.
- Trigger Limits: Google allows a maximum of 300 triggers per project. If you are running many scheduled tasks across multiple spreadsheets, keep this limit in mind.
- Authorization: Triggers run under the authority of the user who created them. If the sheet owner's authorization expires or is revoked, all their scheduled tasks will stop. Re-authorizing the add-on will restore functionality.
- Concurrent Chains: Each function mode (Fuzzy match, Dedupe, Standardize, etc.) maintains its own independent chain. You can run multiple chains simultaneously for different function modes on the same sheet.
Frequently Asked Questions
When should I use Schedule Functions instead of custom functions?
Use Schedule Functions for recurring jobs, large datasets and long-running operations. Use custom functions for quick in-cell checks and immediate formula output.
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.
How often can I run scheduled cleaning tasks?
You can schedule tasks from every 15 minutes up to monthly, depending on your data update cadence and available Apps Script quota.