Workflow Templates Case Study for Google Sheets

A workflow template turns a multi-step cleaning job into one run. Each template chains Standardize, Find Duplicates and Fuzzy Match in the order the job needs, then writes the result to a review sheet. This case study follows one retailer, a company of about forty staff with an online store and trade accounts, through the four recurring jobs the templates were built for. The company is a worked example, but every input, threshold, output column and warning below is the real behaviour documented in Workflow Templates. Open Extensions > Flookup Data Wrangler > Standard Data Cleaning > Templates and each job runs in the order shown.

The retailer behind this case study

Four teams, four recurring jobs, and each one had been done by hand in a different spreadsheet. Accounts payable reconciles an invoice list against a master vendor table every month. Fulfilment compares the weekly order export against the shipping log the third party warehouse emails over. RevOps is midway through a CRM migration and holds two user lists that overlap. The buying team maintains names for a small design collection catalogued with a partner museum, where each name needs its Wikidata identifier.

Every one of those jobs has the same shape underneath: two lists, a name column in each, and the question of which rows refer to the same thing. That is exactly what a template encodes, which is why the four jobs run from one sidebar rather than four spreadsheets of formulas.

Where templates live in the sidebar

Templates are the first card in the Standard Data Cleaning sidebar, above Fuzzy Match Data. Click it to open the browser, grouped by category: Accounting, E-Commerce, HR / RevOps and Research and GLAM. A click on any template opens its detail view, the inputs it needs and a Run button. Every template follows the same three moves: set the inputs, click Run, then read the review sheet. The template remembers the inputs from the last successful run, which matters by the third month as shown further down.

Month end, matching the vendor list

The invoice list this month holds 214 rows and the master vendor table holds 640. Most accounting software blocks a duplicate invoice number within the same vendor number, which is why a second record for the same supplier defeats the control: the same invoice booked against two vendor numbers reads as two legitimate bills. State auditors in Washington have put duplicate payments at 0.8 to 2 percent of total payments across organisations, and a duplicated vendor master is the cause they name first. The month end job exists to close that gap before the payment run.

The AP Vendor Reconciliation template lives in the Accounting category. It asks for the AP list range and the vendor name column in it, the master vendor table range and its vendor name column, and a match threshold that defaults to 0.82. Both name columns default to column one, which is the usual layout. The run chains Standardize, then Find Duplicates, then Fuzzy Match, and writes to the AP Vendor Recon review sheet, reused when the sheet already exists.

The Find Duplicates step earned its place on the first run. It flagged three pairs inside the AP list itself, where the same supplier had been entered twice in one month, which no match against the master table would have caught. The full manual method behind this template, including the survivorship decisions, is in vendor reconciliation with supplier name matching.

The weekly fulfilment audit

The online store exports orders weekly and the warehouse emails a shipping log for the same week. The two lists agree on most rows and disagree on the ones that matter: orders that never shipped, orders shipped against a mistyped customer name and shipping rows whose order id does not appear in the export at all. Operations had been reconciling the two by eye, counting rows in each column and chasing the difference.

The Order-to-Shipping Reconciliation template takes the orders list range with its order id and customer name columns, the shipping log range with the same two columns, and a threshold that defaults to 0.85. It runs Standardize, then Fuzzy Match, and writes the Order Ship Recon review sheet. The order ids sit side by side in the output, so the customer name score and the id check answer each other.

The unmatched row is the list that goes to the warehouse on Monday morning, and the medium confidence row is the one that gets a second look: 0.83 sits just under the default, the kind of case covered in order reconciliation with shipping records. Unfulfilled orders are exactly what the Status column answers, which is why the weekly run takes minutes rather than the morning it used to take.

Merging two user lists before a migration

The CRM migration left RevOps holding a legacy export of 2,940 names and a new system export of 1,120. The question is not only who is in both lists, but who the same person is in both lists, written differently. The legacy system holds Bob Smith where the new one holds Robert Smith, and the email spellings differ for about a tenth of the overlap.

The User List Consolidation template takes the two ranges, the name column in each, and a threshold that defaults to 0.85. Its run is the longest of the four: Standardize List A, Standardize List B, Find Duplicates List A, Fuzzy Match, Review, then the Consolidated Unique Users output. The User List Consolidation sheet carries List A Name, the Standardized Name, the Match in List B, the score, a status and a confidence label for every row.

People flagged as appearing in both systems migrate with their history attached, the unmatched List A rows become new records to review, and the duplicate groups the Find Duplicates step found inside List A are handled before the merge rather than after it. The wider discipline, including what to do with the leftovers, is in consolidating duplicate user lists.

The research category and Wikidata names

The fourth job belongs to the partner museum rather than the retailer, and it shows what the Research and GLAM category is for. A catalogue of 480 makers needs each name matched to its Wikidata QID before the records are uploaded. The Wikidata Reconciliation template asks for the name list range and its column, the reference table range with one QID column and one label column, and a threshold that defaults to 0.82.

The reference table is exported from Wikidata Query Service first, which is deliberate. Matching against a local table keeps each run repeatable, avoids query limits and leaves the data inside the Workspace. A table scoped to the domain being worked on, such as every museum in one country, matches faster and produces fewer false candidates than an export of the whole of Wikidata. The run standardises each name, then matches twice, once to return the QID and once to return the label, so a very large list takes longer than the other three templates.

The Wikidata Recon sheet returns Source Name, the Standardized Name with diacritics removed, the QID, the label, the score, a status and a confidence label. A match is a candidate and not a confirmed identity, so the score, the source name and the label are checked before a QID reaches a catalogue record. When a QID looks wrong the reference table is corrected or the threshold is raised and the template runs again. The wider Wikibase workflow is in preparing Wikibase data for QuickStatements.

When a template stops short

The four jobs above are recurring ones, which is what templates are for. The work around them is not recurring, and that is where the assistant on the same sidebar earns its place. The museum cataloguer needed one column that combined the QID and the label into a citation string, and RevOps needed a formula that flagged shared email domains between the two lists. Neither is a workflow, and neither was worth building a template for.

Both were described to the Sheets AI Assistant in Recommendation Mode, in the same plain language used to ask a colleague. Each answer returned a title, the formula to use and an explanation of how it works, applied by hand rather than run on the sheet. A follow up question in the same session, such as asking for the formula to be case insensitive, refers back to the earlier exchange.

The same gap shows up across data cleaning tools: the transformation syntax is the entry barrier for non-technical staff, and the request that comes up most is a contextual formula builder. Nearly half of that name matching aims at Wikidata, which is why the template above exists at all. Recommendation Mode runs on an active pass or the 7-day AI trial with your own Gemini or OpenAI key, and the full dataset never leaves the sheet: only column headers and a small sample of rows are sent with each question.

Tuning thresholds between runs

All four templates share the same lever. The threshold runs from 0 to 1 and a higher value returns fewer, more confident matches. The defaults, 0.82 for the vendor and Wikidata templates and 0.85 for the order and user list templates, are starting points rather than answers, and the loop that settles them takes one extra run.

1. Run at the default threshold and read the review sheet from the bottom up, where the scores are lowest.
2. Lower the threshold until the expected pairs appear, then note where the false positives start.
3. Raise it back to just above that point and rerun. The borderline band is reviewed by hand once, not every week.

!No match found means no record met the threshold at all, which is a signal to review the threshold and the source ranges rather than a final answer. The score and the confidence label summarise a comparison. They do not replace checking the underlying values, and duplicate groups depend on the first-column key that Find Duplicates used.

Rerunning a template with last run settings

By the second month none of the four jobs needed its ranges typed again. Open the same template and click Use last run settings to restore the ranges and column numbers from the last successful run, adjust anything that changed, such as a widened AP list or a new shipping log file, and run again. The review sheet is reused and its previous results are replaced, so the sheet always holds the latest run while the source ranges stay exactly as they were.

One habit kept the four jobs from colliding. Because each template writes to its own review sheet, AP Vendor Recon, Order Ship Recon, User List Consolidation and Wikidata Recon can all sit in the same spreadsheet, and each monthly or weekly rerun lands where the last one did.

Licences, limits and error messages

Templates are a 1-Month Pass or 1-Year Pass feature, included in every pass of either length. Cleaning, deduplication, standardisation and enrichment are unlimited and are not metered; every run is subject to the pass row limits, and AI assistant steps run on the API key set in the sidebar. The four errors worth recognising before the first run are listed below.

Frequently Asked Questions