Key Takeaways
- Pull a sheet range into the desktop app and clean it as guided steps with no formulas.
- Standardise text first so fuzzy matching groups variants that exact lookups miss.
- Preview the write back and confirm only reviewed changes to the same cells.
- Reconnect Google Sheets once, because writing needs the full spreadsheet scope.
Why formulas fail on messy sheets
Built in dedupe and lookup formulas assume tidy values. Real sheets mix Acme Corp with Acme Corporation, pad cells with stray spaces and vary phone formats across rows, so exact comparisons miss pairs that a reader sees as obvious. Each helper column then adds upkeep, and one edited formula can silently change the result for the whole tab. The technique guides in remove duplicates and cleaning tips cover the formula path well. This post covers the other path: pull the range out, clean it locally and write reviewed values back.
Pull the range into a local table
Enter the spreadsheet id from the spreadsheet url and a range in A1 notation in the Google Sheets source. The default range of
A1:Z10000
reads the used area of the first sheet, and a named sheet or a narrower range pulls exactly that area instead. The fetched table opens as an ordinary table in the workbook, so every other module works on it as it would on an opened file. The pull remembers its source and refills the id and range on return, so a write back does not need them typed a second time. The control list is in
Import from a Source
.
Sign-in uses OAuth with PKCE in your own browser, so no secret lives in the build. A Google Cloud project with the Sheets API enabled and a Desktop app client id is needed in Settings first, because Google requires every application to be registered before it can sign in. Until a client id is entered the connector reports that it is not configured. Tokens are written to your Windows user profile beside the rest of the app data and never leave the machine.
Standardise, match and dedupe locally
Standardise text before matching. Suffixes, punctuation and case otherwise split groups that belong together, and the same threshold advice from fuzzy matching in Google Sheets applies here: start at 0.85 for names, raise it toward 0.90 when false positives appear and lower it toward 0.80 when expected pairs are missed. Match across one column or a whole table with exact, normalised, phonetic and fuzzy measures, then dedupe to one master row per entity and keep a note beside each merged row as the audit trail.
Run Find duplicates before the threshold is fixed. It reports the groups and changes nothing, so the size of the problem is known before anything commits. Take a surprising pair and run Explain on it to see the nearest matches with the score for each, then let Golden records show the surviving choice for each group without writing it yet.
Write reviewed values back to the same cells
Write back sends cleaned values to the source again, one call per matched sheet row. Name the column that identifies a record and tick the columns to write. Only rows that match a remote record are written. The write addresses the cells the pulled table actually occupies, so a table from a range that does not begin at column A or from a named sheet returns to that sheet and those columns rather than to the top left of the first sheet. Columns that are not adjacent are written as separate requests, so a column between two written ones is never overwritten.
The first press is always Preview write back. It reports how many rows matched and how many were skipped and shows the first ten changes without sending anything. Apply is only live after a preview and the button then reads Confirm write back. Every write is recorded on the activity list in My Account, and Undo last write back restores the prior values captured during the read.
Reconnect once for the write scope
Writing needs a broader permission than reading. The values update path accepts only the full spreadsheet scope, so a token issued earlier for reading cannot write. Reconnect Google Sheets once and the new sign-in carries the write scope. That sign-in can change any spreadsheet the account can open, which is worth knowing before it is granted, and Disconnect removes it at any time.
Sheets add-on or desktop
Flookup Data Wrangler exists in both places. The Google Sheets add-on cleans where the data already sits, which suits teams whose workflow lives in Sheets. The desktop pulls a sheet into a local workbook, handles Excel scale files offline and writes reviewed values back to the same cells. One licence covers both, so start where the data sits and move to desktop when the job needs a pull, a write back or an undo.
Start from the desktop page at Desktop when the round trip is the goal. The installer is signed and the page lists the current version and file size for verification.
Frequently Asked Questions
Do I need formulas to clean the sheet?
No. The desktop pulls the range into a local table and the cleaning runs as guided steps. Standardise names, match variants and dedupe rows without writing a single formula.
Where do cleaned values go?
Back to the cells the table came from. Write back addresses the sheet and columns the pull occupied, so a table from a named sheet or a range beyond column A returns to that place rather than to the top left of the first sheet.
Why must Google Sheets be reconnected?
Writing needs a broader permission than reading. The values update path accepts only the full spreadsheet scope, so a token issued earlier for reading cannot write. Reconnect once and the new sign-in carries the write scope.
Can a write back be undone?
Yes. Prior values are captured while the records are read and held on your machine. Undo last write back restores those values, asks for a second press and drops the applied undo from the list.