Sidebar Data Cleaning Tools for Google Sheets
Sidebar tools are accessible from Extensions > Flookup Data Wrangler > Function Catalogue. Pick a scene, configure parameters and run. Results are written directly to your active range. Each run is limited to 500 rows and 5 executions per month on the free plan. Upgrade to Data Nova for unrestricted access.
Quick Overview
| Scene | What it does | What it is best for |
|---|---|---|
| Fuzzy Match | Searches a column for the best match and returns a value with its similarity score or row position | Reconciling data by token variations |
| Merge Row | Finds the best match and returns the entire matching row with the similarity score appended | Enriching data by pulling full matching records |
| Standardize | Cleans text by removing diacritics, punctuation, stop words or extracting URL components | Preparing data for analysis |
| Compare | Calculates the percentage similarity between two ranges side by side | Spotting near-duplicates between columns |
| Deduplicate | Removes duplicate rows by percentage or sound similarity | Cleaning datasets with inconsistent entries |
| Extract Unique | Returns unique values from a column by percentage or sound similarity | Building clean reference lists |
| Fuzzy Conditional Formatting | Highlights duplicate groups in a range using colour-coded conditional formatting | Finding near-duplicates across large datasets without modifying cell values |
Choosing a similarity threshold
A similarity threshold controls how broadly the functions match variants. Lower thresholds widen the net and increase recall: they will find more distant variants but also include more false positives. Higher thresholds increase precision: they return only close matches and reduce the need for manual review.
Practical guidance:
| Scenario | Guidance |
|---|---|
| Routine reconciliation | For names or addresses, begin at 0.75 and only lower the threshold if too few useful matches are returned. |
| Exploratory deduplication | When data is heavily inconsistent, try a lower threshold (for example 0.65 to 0.75) and inspect the candidate matches. |
| Iterative refinement | Test on a representative sample, review the results and refine the threshold until it fits your accuracy vs coverage requirements. |
Free plan vs Data Nova
| Feature | Free Plan | Data Nova |
|---|---|---|
| Monthly runs | 5 | Unlimited |
| Rows per run | 500 | Unlimited |
| Matching threshold | 0.9 and above | 0.75 and above |
| Catches typos | Yes, e.g. "Jonh" to "John" | Yes, plus broader variations like "Smyth" to "Smith" |
| Sound matching | Not available | Full access |
| Standardize (stop-words removal) | Not available | Full access |
Fuzzy Match
The Fuzzy Match scene searches a specified column for the best match and returns the corresponding value from your chosen return column. By default, it pairs the result with its similarity score, but you can also choose to see the row number of the match instead. It is ideal for detailed data reconciliation where you need to see the confidence or position of each match.
Parameters
| Parameter | Description |
|---|---|
| Lookup values | The range containing values to search for, for example A1:A100. |
| Table values | The range that contains the data to compare and return, for example B1:E500. |
| Lookup column | The column index in the table range to search for matches. Use 1 for the first column, 2 for the second and so on. |
| Return column | The column index in the table range from which to return values. |
| Match type | by_percentage (default) for fuzzy character similarity or sound for phonetic matching. |
| Output mode | value_score (default) to return the result with its similarity score or position to return the 1-based row number. |
| Threshold | Minimum similarity, from 0 to 1, for a match. Default is 0.75. |
Example
You have a list of company names in column A and a master list of companies and their industries in columns D and E. Select your lookup range (A2:A50) and table range (D2:E500), set the lookup column to 1, return column to 2 and threshold to 0.85. The scene writes each matched industry alongside its similarity score directly into your active range.
Merge Row
The Merge Row scene finds the best fuzzy match for a lookup value in a table and returns the entire matching row, with the similarity score appended as the last column. This is useful for data enrichment when you need to pull all related information for a matched item from another dataset.
Parameters
| Parameter | Description |
|---|---|
| Lookup values | The range containing values to search for, for example A1:A100. |
| Table values | The range that contains the data to compare and return, for example B1:E500. |
| Lookup column | The column index in the table range to search for matches. |
| Num columns | The number of columns to return from the start of the matching row. If omitted, all columns are returned. |
| Threshold | Minimum similarity, from 0 to 1, for a match. Default is 0.75. |
Example
You have a list of product names in column A and a detailed product database in columns C through E (Name, Category, Price). Select your lookup range (A2:A50) and table range (C2:E500), set the lookup column to 1 and threshold to 0.85. The scene writes the entire matching row plus the similarity score into your active range.
Standardize
The Standardize scene cleans text by removing diacritics, punctuation, stop words or by extracting URL components. It can also learn transformation patterns from examples you provide.
Parameters
| Parameter | Description |
|---|---|
| Lookup values | The range of text values to process, for example A1:A100. |
| Operation |
The type of cleaning to apply:
diacritics (default, removes accent marks), punctuations (removes punctuation), text (removes stop words), domain (extracts the domain from a URL), path (extracts the path from a URL) or pattern (learns a transformation from dirty/clean examples).
|
| Stop array | A range of words or punctuation to remove. Required for the text and punctuations operations. |
| Pattern pipeline | A JSON-encoded transformation pipeline generated by the pattern learning UI. Only used with the pattern operation. |
Example
You have a column of URLs in A2:A100 and want to extract just the domain names. Select the range, set the operation to domain and run. The scene writes the cleaned domain names into your active range.
Compare
The Compare scene calculates the percentage similarity between two ranges placed side by side. Each row in the left range is compared against the corresponding row in the right range, producing a similarity score from 0 to 1.
Parameters
| Parameter | Description |
|---|---|
| Left range | The first column of values to compare, for example A2:A100. |
| Right range | The second column of values to compare, for example B2:B100. |
| Compare mode | by_word (default) compares strings based on character pairs. by_phrase compares strings word-by-word, which is better for phrases with different word orders. |
Example
You have product descriptions in columns A and B that were sourced from different systems. Select both ranges and run with by_word mode. The scene writes a similarity score for each row pair into your active range, letting you quickly spot entries that differ.
Deduplicate
The Deduplicate scene removes duplicate rows from a range based on percentage or sound similarity. You can choose whether to keep the first or last occurrence of each duplicate group. The original data is not modified; results are written to your active range.
Parameters
| Parameter | Description |
|---|---|
| Data range | The range of data to process, for example A1:C100. |
| Key column | The column index within the data range to check for duplicates. Defaults to 1. |
| Keep mode | first to keep the first occurrence of a duplicate or last to keep the last. Defaults to first. |
| Match type | percentage for fuzzy matching or sound for phonetic matching. Defaults to percentage. |
| Threshold | The similarity threshold for percentage mode. Defaults to 0.75. |
Example
You have a dataset in A2:C100 where column B contains company names that might be duplicates. Select the range, set the key column to 2, keep mode to first, match type to percentage and threshold to 0.85. The scene writes a deduplicated copy of your data into the active range.
Extract Unique
The Extract Unique scene returns unique values from a column based on percentage or sound similarity. Unlike exact deduplication, it recognises near-duplicates and phonetic variations, returning a single representative entry for each group.
Parameters
| Parameter | Description |
|---|---|
| Data range | The column of values to process, for example A1:A100. |
| Key column | The column index within the data range to analyse for uniqueness. Defaults to 1. |
| Match type | percentage for fuzzy matching or sound for phonetic matching. Defaults to percentage. |
| Threshold | The similarity threshold for percentage mode. Defaults to 0.75. |
Example
You have a list of cities in column A with variations like "New York", "New York City" and "NYC". Select the range, set the key column to 1, match type to percentage and threshold to 0.85. The scene writes a clean list with one representative entry per group into your active range.
Fuzzy Conditional Formatting
The Fuzzy Conditional Formatting scene highlights duplicate groups in a range by applying colour-coded conditional formatting rules. Unlike the Deduplicate scene, it does not modify cell values. Instead, it groups near-duplicates or phonetic variations and assigns each group a distinct background colour so you can visually identify matches across large datasets.
Parameters
| Parameter | Description |
|---|---|
| Range | The data range to scan, for example A1:C500. Select the range in the sheet before clicking the card. |
| Key columns | Comma-separated column indices within the range to compare, for example 1 or 1,2. Up to 10 columns. Defaults to 1. |
| Similarity type | percentage for fuzzy matching or sound for phonetic matching. Defaults to percentage. |
| Keep mode | first or last. Determines which row in each duplicate group anchors the colour assignment. Defaults to first. |
| Threshold | The similarity threshold for percentage mode. Defaults to 0.75. Disabled when using sound matching. |
| Colour scheme | One of playful, professional, accessible or pastel. Defaults to playful. |
Example
You have a customer list in columns A through C with 500 rows. Some company names are near-duplicates like "Acme Corp", "ACME Corporation" and "Acme Co.". Select the range A1:C500, set the key column to 1, match type to percentage and threshold to 0.80. The scene colour-codes each duplicate group so you can spot related entries at a glance.
Possible Messages
- !No match found The Fuzzy Match or Merge Row scene found no row in the table range that met the similarity threshold. Lower the threshold or widen the table range.
- !Notice: No words removed. Try lowering threshold. The Standardize "text" operation found no stop words to remove at the current threshold. Lower the threshold to catch more variations.
- !Notice: Thresholds below 0.9 require an upgrade. Free users are limited to a minimum threshold of 0.9. Upgrade to use lower thresholds.
- !Notice: Phonetic matching requires an upgrade. Sound similarity matching requires a Data Nova purchase.
- !Notice: Stop word removal requires an upgrade. The Standardize "text" operation (stop word removal) requires a Data Nova purchase.
- !Error: No pattern pipeline found. The Standardize "pattern" operation was selected but no pattern rules were provided.
- !Error: Invalid operation. An unrecognized Standardize operation was specified.
- No data found in the selected range. The active range is empty. Select a range that contains data before running.
- Selected range is empty. The Fuzzy Conditional Formatting scene received an empty range. Select a range that contains data.
- No duplicates found above the threshold. The Fuzzy Conditional Formatting scene found no duplicate groups at the current threshold. Lower the threshold to widen the search.
- Operation timed out. The Fuzzy Conditional Formatting scene exceeded the processing time limit. Try a smaller range or a higher threshold.
- Range exceeds 500 rows. Free users are limited to 500 rows per run. Use Schedule Functions for larger datasets, or upgrade to Data Nova for unlimited rows.
- You have used all 5 runs this month. Free plan monthly limit reached. Purchase Data Nova for unlimited access.
- Timed out before completion. Processing exceeded the managed timeout. Partial results were written to the sheet. Try a smaller range or a higher threshold.
- Cancelled. You clicked Cancel. Partial results were written to the sheet.
Frequently Asked Questions
When should I use sidebar tools instead of Schedule Functions?
On the free plan, sidebar tools are limited to 500 rows per run. Data Nova users have no row limit. Use Schedule Functions for recurring jobs, larger datasets and background processing.
Can I use fuzzy matching on the free plan?
Free users can run exact and limited fuzzy workflows, but results below 0.9 similarity and some advanced features require an upgrade.
What happens if a run times out?
On the free plan, each run processes up to 500 rows within a managed timeout. Partial results are written to the sheet if processing is interrupted. Data Nova users have no row limit. For larger datasets, use Schedule Functions.
How many times can I run sidebar tools per month?
Free users get 5 runs per month. Data Nova users get unlimited runs.