Data Processing Functions for Google Sheets
Data Processing Functions are accessed through the Flookup sidebar at Extensions > Flookup Data Wrangler > Data Processing Functions . Select a function, configure its parameters and click Run. Results are written directly into your active range. You can cancel at any time and partial results are preserved.
Quick Overview
| Function | What it does | Best for |
|---|---|---|
| Fuzzy Match Data | Searches a table column for the best match and returns a value with its similarity score or row position | Reconciling data by token variations across two datasets |
| Fuzzy Merge Rows | Finds the best match and returns the entire matching row with the similarity score appended | Enriching data by pulling all related columns from a master table |
| Standardize Data | Cleans text by removing diacritics, stop words, punctuation, extracting URL components or learning patterns from examples | Preparing inconsistent text for analysis or matching |
| Compare Similarity | Calculates a percentage similarity score for each row pair between two columns | Spotting near-duplicates or measuring how much values differ |
| Deduplicate Data | Removes duplicate rows by percentage similarity with control over which occurrence to keep | Cleaning datasets with inconsistent spelling or formatting |
| Extract Unique Rows | Returns distinct rows by percentage similarity, keeping one representative entry per group | Building clean reference lists from messy duplicates |
| Fuzzy Conditional Formatting | Colour-codes duplicate groups in a range without modifying cell values | Visually identifying near-duplicates across large ranges |
| Profile Column | Scans a column and returns a data quality report with row counts, distinct values, nulls and warnings for inconsistent capitalisation and near-duplicates | Exploring data quality before deciding which cleaning operation to run |
| Enrich Data | Matches your column against built-in reference tables for countries, US states, company suffixes and TLDs, then adds enrichment columns | Adding ISO codes, capitals, regions or full company names from abbreviations |
Choosing a similarity threshold
The similarity threshold determines how closely two values must match to be considered related. It ranges from
0
(anything matches) to
1
(exact match only). The default is
0.75
.
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 such as
0.65
to
0.75
and inspect the candidate matches.
|
| Iterative refinement | Test on a representative sample, review the results and adjust the threshold until you get the right balance of coverage and accuracy. |
Fuzzy Match Data
The Fuzzy Match Data function searches a specified column in your table for the best fuzzy match to each lookup value. You can choose whether the output includes the similarity score (default) or the row position of the match. It is ideal when you need to reconcile two datasets with slight variations in naming, spelling or formatting.
Parameters
| Parameter | Description |
|---|---|
| Lookup values |
The range containing the values you want to search for, for example
'Sheet1'!A1:A100
.
|
| Table values |
The range containing the data to search and return from, for example
'Sheet1'!B1:E500
.
|
| Lookup column |
The column index within 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 within the table range from which to return matched values. |
| Output mode |
value_score
(default) returns the matched value alongside its similarity score.
position
returns the matched value alongside its 1-based row number in the table range.
|
| Threshold |
Minimum similarity required for a match, from
0
to
1
. Default is
0.75
.
|
Example
You have a list of vendor names in column A and a master supplier database in columns D (Name) and E (Category). Select your lookup range (
'Sheet1'!A2:A50
) and table range (
'Sheet1'!D2:E500
), set the lookup column to
1
, return column to
2
and threshold to
0.85
. The function writes each matched category and its similarity score into your active range, so you immediately see both the match and how confident it is.
Fuzzy Merge Rows
The Fuzzy Merge Rows function finds the best fuzzy match for each lookup value and returns the entire matching row from the table, with the similarity score appended as the final column. This is ideal for data enrichment where you need to pull all related fields for each matched item.
Parameters
| Parameter | Description |
|---|---|
| Lookup values |
The range containing values to search for, for example
'Sheet1'!A1:A100
.
|
| Table values |
The range containing the data to search and return from, for example
'Sheet1'!B1:E500
.
|
| Lookup column | The column index in the table range to search for matches. |
| Columns to return |
The number of columns to return from the start of the matching row, defaults to
5
. If you set this higher than the number of columns in your table range, it will return all available columns.
|
| Threshold |
Minimum similarity required for a match, from
0
to
1
. Default is
0.75
.
|
Example
You have a list of product names in column A and a detailed product database in columns C through G (Name, SKU, Category, Price, Stock). Select your lookup range (
'Sheet1'!A2:A50
) and table range (
'Sheet1'!C2:G500
), set the lookup column to
1
, columns to return to
5
and threshold to
0.85
. The function writes the full matching record plus the similarity score into your active range.
Standardize Data
The Standardize Data function cleans text values by applying one of six operations. You can remove diacritics, strip punctuation, filter out custom stop words, extract the domain or path from a URL or use pattern learning to teach the system your transformation rules from example pairs.
Parameters
| Parameter | Description |
|---|---|
| Operation |
The type of cleaning to apply:
|
| Input range |
The range of text values to clean, for example
'Sheet1'!A1:A100
.
|
| Stop words or marks |
A range containing the words or punctuation characters you want to remove. Required for the
text
and
punctuations
operations. Hidden for other operations.
|
| Dirty values range |
A range of example messy values for the
pattern
operation. Visible only when Learn from Examples is selected.
|
| Clean values range |
A range of example clean values (one per dirty row) for the
pattern
operation. Visible only when Learn from Examples is selected.
|
| Threshold |
For
text
operations, controls how tolerant the system is when matching stop words. Defaults to
0.75
.
|
Using Learn from Examples
When you select the
pattern
operation, additional fields appear for training the system. Create two columns in your spreadsheet: one with dirty values and one with the clean versions you want. Enter both ranges in the Dirty values and Clean values fields.
Click Test pattern to preview the transformations before running. The sidebar shows a table comparing each dirty value with its expected clean result. Matching rows appear in green; mismatches appear in red and indicate areas where you may need to add more examples. For detailed guidance, see the Learn from Examples guide .
Example
You have a column of URLs in
'Sheet1'!A2:A100
and want to extract just the domain names. Select the range as your Input range, set the operation to
domain
and click Run. The function writes the cleaned domain names starting from your active cell.
Compare Similarity
The
Compare Similarity
function calculates a percentage similarity score for each row pair between two columns placed side by side. Each row in the left range is compared against the corresponding row in the right range, producing a score from
0
(completely different) to
1
(identical).
Parameters
| Parameter | Description |
|---|---|
| Left string range |
The first column of values to compare, for example
'Sheet1'!A2:A100
.
|
| Right string range |
The second column of values to compare, for example
'Sheet1'!B2:B100
.
|
| Compare mode |
by_word
(default): compares strings based on character pairs, effective for spotting typographical differences.
by_phrase
: compares strings word-by-word, which is better when words may appear in a different order.
|
Example
You have product descriptions in columns A and B that were entered by different teams. Select both ranges, choose
by_word
mode and click Run. The function writes a similarity score for each row pair into your active range. Rows with scores near
1
are consistent; rows with low scores need attention.
Deduplicate Data
The Deduplicate Data function removes duplicate rows from a range using percentage similarity. You select which column to use as the key for comparison and whether to keep the first or last occurrence of each duplicate group. Your original data is not modified; results are written to your active range.
Parameters
| Parameter | Description |
|---|---|
| Data range |
The range of data to deduplicate, for example
'Sheet1'!A1:C100
.
|
| Key column |
The column index within the data range to use for comparing duplicates. Defaults to
1
.
|
| Keep mode |
first
(default): keeps the first occurrence of each duplicate group.
last
: keeps the last occurrence and replaces earlier ones.
|
| Threshold |
The similarity threshold. Defaults to
0.75
.
|
Example
You have a customer dataset in
'Sheet1'!A2:C100
where column B contains company names with inconsistent formatting. Select the range, set the key column to
2
, keep mode to
first
and threshold to
0.85
. The function writes a deduplicated copy of your data into the active range, keeping only the first occurrence of each near-duplicate group.
Extract Unique Rows
The Extract Unique Rows function returns distinct rows from a data range based on percentage similarity in a key column. Unlike exact deduplication, it recognises near-duplicates as belonging to the same group and returns only one representative entry per group.
Parameters
| Parameter | Description |
|---|---|
| Data range |
The range of values to filter, for example
'Sheet1'!A1:C100
.
|
| Key column |
The column index within the data range to analyse for uniqueness. Defaults to
1
.
|
| Threshold |
The similarity threshold. 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
and threshold to
0.75
. The function writes a clean list with one entry per similarity group. Empty cells are skipped, so blank rows in your data range do not appear in the output.
Fuzzy Conditional Formatting
The Fuzzy Conditional Formatting function applies colour-coded highlighting to groups of similar rows in a range. It compares key columns using percentage similarity, assigns each group a colour and applies the colour across the entire row. Your cell values are not changed; only the background colour is updated.
Parameters
| Parameter | Description |
|---|---|
| Range | The range to format. Click Select Range to populate it from your active selection. |
| Key columns |
Comma-separated column numbers to use for grouping (e.g.
1,2
). Values from these columns are concatenated and compared against all other rows. Rows with combined key values that meet the threshold receive the same colour.
|
| Threshold |
The similarity threshold. Defaults to
0.75
.
|
| Colour scheme |
Choose from four palettes:
Playful
(vibrant),
Professional
(subdued),
Accessible
(colour-blind friendly) and
Pastels
(soft). A colour preview renders in the sidebar so you can see what each scheme looks like before applying.
|
Example
You have a list of products in columns A (Name) and B (Category) over 500 rows. Select the full range, set key columns to
1
(to group by product name only) and threshold to
0.80
. The function colours each group of similar product names with a distinct colour, making it easy to spot clusters of near-duplicates at a glance. Rows that have no similar match remain white.
To clear the formatting, you can use Google Sheets' native "Reset background" option on the formatted range. Fuzzy Conditional Formatting does not create conditional format rules; it applies static background colours directly to cells.
Profile Column
Profile Column scans any column and returns a data quality report. It detects inconsistent capitalisation, identifies near-duplicates with high similarity and shows the value distribution at a glance. No data is modified and no credits are consumed.
Parameters
| Parameter | Required | Notes |
|---|---|---|
| Column to profile | Yes | Any single-column range. Up to 5,000 rows are scanned per run. |
Example
You have 483 rows in a Country column. Click
Profile Column
, select
'Sheet1'!A1:A483
and click Run. The report shows:
| Metric | Value |
|---|---|
| Rows | 483 |
| Distinct Values | 6 |
| Nulls | 3 |
| Duplicates | 477 |
The report also shows warnings such as mixed capitalisation across 4 groups and suggestions like Standardise 58 inconsistent values . Click a suggestion to jump directly to the relevant tool with the parameters pre-filled.
Enrich Data
Enrich Data matches values in your column against built-in reference tables and writes enrichment columns next to your input. No external API calls, no setup and no credits are consumed for the lookup itself.
Entity Types
| Type | Available fields | Example |
|---|---|---|
| Country | ISO code, capital, region |
uganda
→
UG / Kampala / East Africa
|
| US State | Abbreviation, capital, region |
california
→
CA / Sacramento / West
|
| Company Suffix | Full name |
ltd
→
Limited
|
| Domain TLD | Description |
io
→
Tech / Startup
|
Parameters
| Parameter | Required | Notes |
|---|---|---|
| Column to enrich | Yes | The column containing values to match against reference data |
| Entity type | Yes | Choose from Country, US State, Company Suffix or Domain TLD |
| Fields to import | At least one | Check the attributes you want added as new columns |
Example
You have a column of country names in
'Sheet1'!A1:A100
. Click
Enrich Data
, select the range, choose
Country
as the entity type, check
ISO Code
,
Capital
and
Region
, then click Run. Three new columns appear next to your input:
ISO
,
Capital
and
Region
, populated for every matched row. Unmatched rows show empty cells.
Managing Running Jobs
While a function is running, the sidebar shows a progress indicator and a Cancel button becomes available.
- Cancelling: Click Cancel to stop the current job. The system checks for cancellation requests frequently. Once detected, it writes all results processed up to that point to your active range and clears the remaining cells.
- Long-running jobs: Every function has a built-in processing window. If a job takes longer than the available time, the system writes what it has processed so far and stops. This ensures you always get partial results rather than nothing at all.
- Retrying: If a job timed out, adjust your parameters (reduce the range size or increase the threshold) and run again. Your original data is never modified.
Common Messages
- !No match found No row in the table range met the similarity threshold for that lookup value. Lower the threshold or widen the table range.
- Range exceeds 1,000 rows. Data Nova handles unlimited rows or use Schedule Functions for large datasets. The Free Forever plan limits each run to 1,000 rows. Upgrade to Data Nova for unlimited rows or use Schedule Functions for large batch processing.
- You have used all free runs today. Data Nova gives you unlimited daily runs. Your daily free run limit has been reached. The limit resets each day. Data Nova removes this cap entirely.
- Timed out before completion. The job ran longer than the processing window. Partial results have been written to the sheet. Try a smaller range or a higher threshold.
- Cancelled. X rows written. Y of Z runs remaining. You clicked Cancel. Partial results have been written to the sheet.
- No data found in the selected range. The active range is empty. Select a range that contains data before running.
Frequently Asked Questions
When should I use Data Processing Functions instead of Schedule Functions?
Use Data Processing Functions for interactive one-off processing where you want to see results immediately and stay in control. Use Schedule Functions for recurring jobs, large datasets and background processing.
What happens if a run is interrupted or cancelled?
You can cancel a running job at any time. Partial results are written to the sheet so you do not lose everything. If a job runs long, the system writes what it has processed so far.
How many times can I run these functions?
The Free Forever plan includes a set number of runs per day. Data Nova unlocks unlimited daily runs and unlimited rows per run, with no restrictions on dataset size or frequency of use.
Does running a function modify my original data?
No. Results are always written to your active range, starting from the active cell. Your source data remains untouched. You choose where the output goes by selecting the appropriate cell before running.