Google Sheets Automation Cleanup

A practical field guide from Automation Ace.

Automating Google Sheets Cleanup, Formatting, and Deduplication

Google Sheets is where operational data goes to slowly become unreliable. When the data is clean enough to migrate out of spreadsheets, see using Airtable as an operations database for the proper relational alternative. For the same normalization and deduplication patterns applied to CRM records, see CRM data hygiene automation. Columns with inconsistent capitalization, blank rows left by deleted entries, duplicated records from multiple form submissions, and date values stored as text strings — these are the silent quality problems that make every downstream report and automation less trustworthy over time. Automating the cleanup removes the problem at the source.

The cleanup automations I build for clients fall into two categories: real-time normalization (clean the data as it arrives from a form or integration) and scheduled batch cleanup (run every Sunday morning and fix anything that slipped through the real-time filter). Both are necessary because data enters from multiple sources with different quality characteristics.

Real-Time Normalization as Data Enters the Sheet

The highest-leverage intervention is normalizing data before it writes to the sheet. In a Zapier workflow that creates a new Google Sheet row from a Typeform submission, add a Formatter step between the trigger and the Google Sheets action. The Formatter can: capitalize names properly (John Smith, not john smith or JOHN SMITH), convert emails to lowercase, strip whitespace from text fields, and parse date strings into a consistent ISO format (2024-03-15, not "March 15th" or "3/15/24").

In Make, the same work happens in a Tools module with Set Variable steps that apply text transformations. The critical ones are trim() for whitespace, lower() for emails, toDate() for date parsing, and replace() for removing unwanted characters like extra commas or parentheses from phone numbers.

Scheduled Deduplication with Apps Script or Make

Google Apps Script is the right tool for sheet-level deduplication because it runs inside the sheet and can read, compare, and delete rows natively. A simple script that runs on a time trigger (every Sunday at 6am) can find all rows where column B (Email) appears more than once, keep the most recent row by comparing column A (Timestamp), and delete the older duplicates. The script runs without anyone logging in and produces a log entry showing how many duplicates were removed.

• Identifying duplicates: Use a helper column with a formula like =COUNTIF($B$2:$B, B2) to flag any row where the email appears more than once. Filter on values greater than 1 to see all duplicates.
• Removing duplicates via Apps Script: A script using sheet.getDataRange().getValues() to read all rows, build a Map keyed on email address, then write back only the unique rows is fast and reliable for sheets under 10,000 rows.
• Make-based dedup: A weekly Make scenario can list all rows, use an array aggregator to group by email, identify records with group size greater than 1, and call the Google Sheets API to delete the older row IDs.
• Preventing future duplicates: Add a Google Apps Script onFormSubmit trigger that checks for the submitted email in the sheet before writing and updates the existing row rather than appending a new one.

Formatting Cleanup: Dates, Phone Numbers, and Case

Date columns stored as text strings ("March 15, 2024") break every formula that tries to calculate days since submission or filter by date range. A batch reformatter can run through the column, detect values that are text strings (using typeof checks in Apps Script), convert them to proper date values using new Date(cellValue), and write them back as formatted dates. Once the column contains actual date values, Zapier and Make will parse them correctly in every automation that reads the sheet.

The Google Sheet that "everyone uses" is the one that causes the most automation failures. Clean it once properly, protect it with real-time normalization, and every tool that reads it will work correctly.

Automating Column Formatting and Conditional Highlighting

Apps Script can also automate formatting rules that Google Sheets' built-in conditional formatting cannot handle dynamically. A script triggered daily can: highlight rows where "Status" column is "Overdue" in red, bold the most recently added 5 rows, clear formatting from rows where "Archived" checkbox is checked, and resize column widths to fit content. These cosmetic automations sound minor but significantly reduce the time team members spend scanning a busy sheet for the records that need attention.

Exporting Cleaned Data to Other Systems

A Google Sheet that has been cleaned and normalized is a reliable data source for other automations. A daily Make scenario or Zapier scheduled Zap can read all rows added in the past 24 hours (by filtering on the Timestamp column for "greater than yesterday"), normalize any remaining inconsistencies, and write clean records to Airtable, your CRM, or a BigQuery table. The sheet becomes a reliable staging layer rather than a messy dead-end.

1. Add Formatter steps to every Zapier workflow that writes to Google Sheets to normalize names, emails, phones, and dates before they are stored.
2. Write a Google Apps Script deduplication function and set it on a weekly time trigger.
3. Add a helper column with a COUNTIF formula to make existing duplicates visible immediately.
4. Convert text date columns to actual date values using a batch Apps Script that runs once on the existing data.
5. Build a scheduled Make scenario that reads the sheet daily and writes normalized records to your primary data store (Airtable or CRM).
6. Set up column-level data validation (dropdown lists, date format enforcement) so new entries cannot violate the format rules without an error.