Data Connectors Case Study for Google Sheets

Operations teams rarely run on one system. The setup in this case study is the common shape: customers in Airtable, orders in a PostgreSQL database and sales contacts in Salesforce, with one analyst answering questions that span all three. The numbers below follow a fictional wholesaler through one month, but every step, limit and warning is the real behaviour of the connectors, as documented in Data Connectors. Open Extensions > Flookup Data Wrangler > Data Connectors in the spreadsheet and the sequence runs in the order shown.

Where the data lives in this scenario

The company is a forty staff wholesaler, and the trade customer table in Airtable holds about 2,400 records. It grew the way most bases grow: web form submissions, tradeshow imports and an automation that created a record whenever a quote was requested. The same company lands two or three times as Northridge Trading Ltd, NORTHRIDGE TRADING LIMITED and Northridge Trading. The pattern is the one described in the Airtable dedupe walkthrough: nothing warns at entry time, so the overlap accumulates quietly.

The orders live in a PostgreSQL database behind the online store. A saved query returns order rows, invoice numbers and payment state for the quarter, and the finance team needs corrections from that table to flow back, which a read only export cannot do. Sales contacts sit in Salesforce, around 1,800 records, and the sales team owns them: operations reads them for the monthly report and writes nothing back.

The monthly question is always the same one. Which trade accounts changed, which invoices are missing a purchase order, and which contacts moved since the last run? Answering it used to mean three exports, a morning of VLOOKUPs and a copy of the results nobody could reproduce later.

Why the CSV round trip fails this job

An export answers the question once and then goes stale. It loses the record identifiers that tie a cleaned row back to its source, it flattens values that the source system treats as types, and the corrections that come out of the cleaning never make it back into the live tables. Re-importing a cleaned CSV over the top of a live system is the fastest way to double every record the export already contained.

The connector pattern inverts the round trip. The pull writes into Flookup Staging with the source ids intact, the cleaning runs on that copy, and the write back sends only the differences after a preview. Nothing is exported to a file, nothing is re-imported and no record is deleted. The rest of this post is that loop, run three times.

Before the first pull

Three things decide whether a pull succeeds, and all three are worth checking before the first run.

• Pass. Connectors are included with the 1-Year Pass and the Team Pass. The 1-Month Pass, the free trial and the free tier do not include them, so this workflow needs the annual pass.
• Permission. The Airtable token, the database user and the Salesforce profile each need read access to everything the report covers, and write access only for the sources that will receive corrections.
• Destination. Each pull replaces Flookup Staging. Analysis and reconciliation belong on a separate tab, because the next pull clears anything left in staging.

Pull the Airtable accounts into staging

The Airtable leg starts with a personal access token carrying data.records:read and schema.bases:read, plus data.records:write because corrections go back to the base. Paste the token into the Airtable card in the sidebar, click Load bases, then pick the base and the table. A view name is optional and used here on purpose: the Trade Accounts view holds the 2,400 rows the report covers, so the pull stays inside the 5,000 row cap.

Click Pull to staging and the connector clears the staging sheet, writes the field names as a bold header row and then the records, fetched a hundred at a time with a short pause to stay inside the Airtable limit of five requests per second. The hidden _flookup_record_id column keeps the source id for every row, which is what the write back matches on later. Edit the names in staging freely. Do not touch that column.

Pull the order rows with one SQL query

The SQL card takes a JDBC URL, a user and a password, and works with PostgreSQL, MySQL, Microsoft SQL Server and Oracle. The query in this scenario joins orders to invoices for the current quarter and filters to trade accounts, with the stable order id as the key column that write back will match on. The query narrows the table before the pull, which is the whole reason to reach for the SQL connector rather than an export: the database does the filtering and only the result arrives in the sheet.

The rows land in staging the same way the Airtable rows did, with a bold header and the key column carried through. Google charges JDBC connections against the account, at ten thousand per day for consumer accounts and fifty thousand for Workspace, which is worth knowing before scheduling anything frequent. The full walkthrough, including the transaction behaviour of the write back, is in import SQL query results into Google Sheets.

Pull Salesforce contacts read only

Salesforce connects through OAuth with a Connected App: pick the objects and fields, write a SOQL query and pull the records into staging. This leg is read only in the current version, which suits the scenario exactly. Operations needs the contact list for the monthly report, and corrections to Salesforce are handled by the sales team through their own process.

Pulling only the fields the report needs keeps the sheet small and the review fast. The Connected App setup, the object picker and the SOQL behaviour are covered in connect Salesforce to Google Sheets and the Salesforce connector documentation.

Run one cleaning pass across the three sheets

The three staging sheets now sit in one spreadsheet, and the cleaning is the same on each. Run Standard Data Cleaning over the columns that carry names, emails and phone numbers: trim the whitespace, fix the casing, standardise the suffixes, then fuzzy match so variants group together. Start at 0.85 for names, as set out in fuzzy matching in Google Sheets, and raise the threshold toward 0.90 when false positives creep in.

In the Airtable sheet this pass collapses the three Northridge spellings into one group and surfaces every other repeated account at the same time. In the SQL sheet it standardises the customer names sitting beside the order ids. In the Salesforce sheet it lines contact names up against the account names they will be matched to. One pass, three sheets, and the reconciliation tab can now work on standardised values rather than raw ones.

Reconcile the three sources in one tab

The analyst keeps a separate Month End tab and copies the cleaned results there, leaving staging untouched for the next pull. Three matches run on that tab: order rows against Airtable accounts on the standardised name, invoices against orders on the key column, and Salesforce contacts against accounts on the name again. Each match produces the same shape of table.

One formula sat between the copies and the matches, a lookup that returns two columns from the account table. It was described to the Sheets AI Assistant in Recommendation Mode, which returned the formula and an explanation of how it works, applied by hand rather than run on the sheet. The assistant runs on an active pass or the 7-day AI trial with the user's own key, and the AI assistant guide covers both of its modes.

The middle row is the band that decides whether a bulk run is precise. Below about 0.80 the matches stop being the same company, and above 0.90 the expected pairs go missing, so the borderline rows are reviewed by hand once rather than every month. The two deeper guides for the individual matches are vendor reconciliation and order reconciliation against shipping records.

Preview and write back the reviewed rows

The first press is always Preview changes. It re-reads the live source, compares every staged row cell by cell and reports three numbers: new, changed and unchanged, with a sample of the first rows underneath. Nothing is written and nothing is queued. The comparison is per field, so a cell that still matches the source is not collected into the plan.

Apply changes sends the plan. Airtable records go out as patches in batches of ten with typecast enabled, SQL updates run through the key column in a single transaction, and only the fields that differ are included, so untouched columns are never overwritten. Rows that exist in a source but are absent from staging are not referenced by the plan at all, which is why nothing is ever deleted. Each run applies at most 500 changes and says so when the cap stops the rest, which turned the Airtable corrections in this scenario into two passes rather than one.

The quiet run is the sanity check worth remembering. Preview the same data a second time immediately after a write and it reports zero changes, because the first run already brought the source in line with the sheet.

Read the change log at month end

Every applied change is appended to a Flookup Changes sheet with the timestamp, connector, target table, staging row, record id, action, field, the before value and the after value. The log is written after the source confirms the change, so a run that stops part way still records the batches that were applied.

For this team the log replaces the memory of what changed. When an account manager asks why a customer name changed on the twelfth, the row in the change sheet answers it with the field, the old value and the new one. It is also the audit trail that finance asked for when the corrections first started flowing back into the database.

Schedule the morning pull with Automatic Pull

The Automatic Pull card schedules the saved connector to refresh staging on its own. Choose a daily time on a quarter hour or an interval from one to twenty four hours, then save. Each run opens the saved spreadsheet, replaces the staging sheet with a fresh pull and records the result on the card, including the next run time.

Four properties of the schedule matter in this scenario.

• Each run replaces the staging sheet, so cleaned results stay on the Month End tab rather than in staging.
• Write back is never automatic. A scheduled pull only reads from the source, and the corrections still go out after a person has reviewed the preview.
• Each run checks the pass. If it has expired, the run is skipped and the card records why, and pulls resume when the pass is active again.
• A failed run records the error and emails once, with at most one notice every twelve hours until a run succeeds.

The schedule uses the connector settings saved at run time, so narrowing the Airtable view or editing the SQL query changes the next pull without touching the schedule. This card is separate from Data Cleaning Automation: automation cleans ranges inside a sheet, while Automatic Pull refreshes the staging sheet from the source. Together they cover a source that should arrive and then be cleaned without an export.

What the second month looked like

By the second month the routine was fixed. The 07:30 pull had already replaced staging when the analyst sat down, the cleaning pass ran over the three sheets, the reconciliation tab was rebuilt from the copies, and the preview reported the month of changes before anything was applied. The first write back of the quarter corrected 212 Airtable rows and created 46 new records, and the second preview a minute later reported zero.

Two habits from the first month stayed. Both are worth carrying over.

• Re-pull after records are removed in a source. A staged row whose Airtable record no longer exists is planned as a new record, so a stale staging sheet would recreate it on the next apply.
• Keep the reconciliation tab and the reviewed rows as the record of what was decided. The staging sheet belongs to the next pull.

The other number worth naming is the one this setup was bought for: 38 duplicate account groups were found in the Airtable base during the first cleaning pass, groups that the monthly report had been counting as separate customers. The merge itself happened in the staging sheet through Find Duplicates, and only the surviving values were written back.

Limits and credentials in this workflow

Tokens and database passwords are stored in the Apps Script user properties of the Google account that set them up, are never returned to the sidebar and are removed by Clear on the connector card. The pulled rows exist only in the spreadsheet: each connector calls the source directly, so Flookup never receives a copy of the data.

Frequently Asked Questions