GPT + Google Sheets for Bulk Data Cleanup

Messy spreadsheets are a fact of life for small and mid-sized businesses. Customer records with inconsistent name casing, product descriptions typed differently by three different employees, address fields crammed with extra commas — this kind of noise slows down every downstream process that depends on reliable data. If you've been trying to clean data in Google Sheets with GPT, you're asking exactly the right question. Pairing the language reasoning capabilities of a large language model with the ubiquity of Sheets lets you tackle bulk formatting, normalization, and categorization jobs that would otherwise take a VA days to complete manually.

This article walks through the practical mechanics: how the connection between GPT and Sheets actually works, where it genuinely helps, and the pitfalls to avoid.

---

Why Spreadsheet Data Gets Dirty in the First Place

Before reaching for AI, it helps to understand the failure modes. Most data quality problems in small business spreadsheets fall into a handful of categories:

• Inconsistent formatting — "New York", "new york", "NY", "N.Y." all mean the same thing but break filters and pivot tables.
• Mixed schema in one column — A "Notes" field that sometimes contains a job title, sometimes a phone number, sometimes nothing.
• Freeform entries that should be categorical — A "Lead Source" column where reps typed whatever they remembered: "Google Ad", "google ads", "Paid Search", "PPC".
• Trailing whitespace and invisible characters — Imported from a CRM or CSV export, these fields look fine on screen but fail exact-match lookups.
• Merged or split data — Full names in one cell when you need first/last separated, or addresses joined when you need city and state in their own columns.

Standard Sheets formulas handle some of these — TRIM, PROPER, SPLIT, REGEXREPLACE — but they break down fast when the variation is semantic rather than structural. That's where GPT earns its place.

---

How GPT Connects to Google Sheets

There are three practical paths, ranging from no-code to light scripting.

1. Third-Party Add-ons (No-Code)

Several Google Workspace Marketplace add-ons expose GPT-style completions as native Sheets functions. After installing and connecting your OpenAI API key, you get a formula like:

=GPT("Standardize this US state name to its two-letter abbreviation: " & A2)

This approach requires zero coding and works well for one-off cleanup projects on smaller datasets. The tradeoff is cost visibility: each formula call hits the API, so a sheet with 5,000 rows will make 5,000 separate requests. Watch your token usage carefully.

2. Google Apps Script with the OpenAI API

Apps Script is JavaScript that runs natively inside Google Workspace. You write a function that reads a range, sends each cell value to the OpenAI Chat Completions endpoint via UrlFetchApp, and writes the result back to an output column. This is the most flexible approach and the one most teams move to once they outgrow add-ons.

A minimal example (pseudocode structure):

function cleanColumn() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange("A2:A500").getValues();
  const apiKey = PropertiesService.getScriptProperties().getProperty("OPENAI_KEY");

  data.forEach(function(row, i) {
    if (!row[0]) return;
    const prompt = `Normalize this company name to title case and remove legal suffixes like LLC or Inc: "${row[0]}"`;
    const response = callOpenAI(prompt, apiKey);
    sheet.getRange(i + 2, 2).setValue(response);
  });
}

Store your API key in Script Properties, never hard-coded in the script itself. Batch your requests with a short Utilities.sleep() call between them to stay within OpenAI's rate limits.

3. Make or Zapier with a Sheets Trigger

If your data arrives in Sheets via an automated pipeline — form submissions, CRM exports, e-commerce orders — you can set up a workflow tool like Make (formerly Integromat) or Zapier to watch for new rows, pass the values through an OpenAI action, and write cleaned output back to a separate column or sheet. This is particularly useful for ongoing normalization rather than a single batch cleanup.

---

Practical Tasks Where GPT Adds Real Value

Standardizing Freeform Category Fields

Consider a retail business that collected "Product Category" from customers over two years via a free-text form field. The column contains entries like "women's shoes", "Womens Footwear", "Ladies Shoes", "shoes - women", and a dozen other variations. A prompt like:

"Map this product category to one of these standard labels: Men's Apparel, Women's Apparel, Footwear, Accessories, Home Goods. Return only the label. Input: {value}"

...will categorize rows with AI in Sheets reliably enough to replace the manual review step for the majority of entries. You'll still want to spot-check a sample, but it reduces the manual effort considerably.

Parsing Unstructured Address Data

Imagine a service business that imported 800 customer records from a legacy system, and the full mailing address landed in a single column. GPT can extract structured fields — street, city, state, ZIP — from each blob of text. A prompt asking for JSON output that you then parse with JSON.parse() in Apps Script gives you clean, split data without writing a fragile regex.

Inferring Missing or Ambiguous Values

A column of job titles scraped from LinkedIn might include "VP", "Vice President of Sales", "V.P., Sales & Marketing", and "VP Sales". Rather than writing thirty IF statements, a single GPT prompt asking for the canonical title from a defined list handles the ambiguity in natural language.

Rewriting or Standardizing Text Descriptions

For product catalogs, a prompt like "Rewrite this product description in 30 words or fewer, using sentence case" can normalize hundreds of inconsistent entries. This is squarely in the territory of bulk data formatting with GPT.

---

What GPT Cannot Reliably Do in Sheets

Being clear about limitations saves time and prevents bad data from getting worse.

GPT is not deterministic. The same input can produce slightly different outputs across runs, especially for ambiguous cases. For exact-match normalization — like country codes — deterministic lookups (VLOOKUP, a reference table) are more reliable.

GPT hallucinates. If you ask it to verify whether a phone number is valid or whether a ZIP code belongs to a real city, it may confidently return a wrong answer. Use GPT for language tasks; use authoritative APIs or lookup tables for factual validation.

Latency and cost at scale. A 10,000-row sheet processed cell by cell will take significant time and incur API costs proportional to your token usage. Batch processing and prompt compression — keeping prompts as short as possible without losing instruction clarity — are essential for larger datasets.

It reduces errors, not eliminates them. Any AI-assisted cleanup pipeline should include a human review step for a statistically meaningful sample before the cleaned data flows into a production system.

---

A Practical Workflow for One-Time Bulk Cleanup

If you're cleaning a dataset for the first time rather than setting up an ongoing pipeline, this sequence works well:

1. Make a copy of the sheet. Never run bulk transformations on your only copy.
2. Identify the five most painful column types — the ones causing the most downstream problems — rather than trying to fix everything at once.
3. Write one focused prompt per column. Vague prompts produce vague results. "Standardize this" is less effective than "Return only the two-letter US state abbreviation for this input. If the input is not a US state, return UNKNOWN."
4. Test on 20–30 rows manually before running the full column. Adjust the prompt until edge cases resolve correctly.
5. Run the full column and write results to a new column — never overwrite your source data until you've reviewed the output.
6. Spot-check a random sample of 50–100 rows across the output. Pay attention to the rows your prompt struggled with (long values, non-English text, nulls).
7. Paste results as values into your final sheet to freeze them. Formulas that call an API re-run on every recalculation, which is both costly and unstable.

---

Setting Up the OpenAI API Key Securely in Apps Script

A quick note on security because this comes up constantly: never paste your OpenAI API key directly into Apps Script code. Anyone with edit access to the script can read it. Instead, use Script Properties:

1. In the Apps Script editor, go to Project Settings > Script Properties.
2. Add a property named OPENAI_KEY with your key as the value.
3. In your code, retrieve it with PropertiesService.getScriptProperties().getProperty("OPENAI_KEY").

This keeps the key out of version history and out of view for collaborators who have sheet access but not script property access.

---

When to Automate vs. When to Clean Manually

GPT-assisted cleanup makes most sense when:

• You have more than a few hundred rows and the variation is semantic (not purely structural).
• The same cleanup logic will need to run repeatedly as new data arrives.
• The manual effort would require sustained human judgment at scale.

Stick with formulas or manual review when:

• The dataset is small enough that a person can review every row.
• The transformation is purely structural (trimming whitespace, splitting on a delimiter).
• Accuracy requirements are extremely high and no error is acceptable without human sign-off.

---

Conclusion

Combining GPT with Google Sheets gives small and mid-sized businesses a practical, accessible way to tackle data quality problems that formulas alone can't solve. The setup requires some initial investment — writing precise prompts, connecting the API, building a review step — but the return on that investment compounds every time a new batch of messy data lands in your spreadsheet.

Intuitional helps businesses design and implement AI workflow automations like these, including data cleanup pipelines that connect your existing tools without requiring a developer on staff. If your team is spending hours normalizing spreadsheets that should take minutes, schedule a conversation about your workflow and we'll show you what an automated approach looks like for your specific data.