Daily

Keep your sheet free of duplicates and bad data

Duplicate rows are removed, names and phone numbers are tidied, and invalid emails are flagged.

Runs: Daily
Apps: Google Sheets
AI: Not needed
Access: read+write

Send this to your assistant

Create a ClickRuntime automation: every night, remove duplicate rows in my Leads sheet by email address, tidy up names and phone numbers, flag invalid emails in a Flag column and email me how many rows changed.

Paste it into your assistant. It tells you what it will send or change before it runs live.

Add to ChatGPT Add to Claude

First time? Adding ClickRuntime to your assistant takes about two minutes.

Why use it

Why teams use this automation

A sales operations assistant maintains a contact sheet fed by imports, form submissions and manual entries. Over months it fills with the same person entered twice, emails with a typo like gmial.com, names in ALL CAPS, phone numbers in five formats and stray spaces. Mailings bounce, reps call the same person twice, and a VLOOKUP that should match two sheets fails because one has a trailing space.

Each day, ClickRuntime reads the range and normalizes the values: trimming spaces, fixing capitalization, standardizing phone numbers and checking whether emails look valid. It detects duplicate rows, writes the cleaned values back, adds a Flag column explaining what it found, and emails you a count of the changes it made.

Marketing lists, CRM exports, event registrations and membership sheets all benefit from regular cleanup.

What you get out of it

Fewer bounced emails
Catching invalid addresses and obvious typos before a campaign goes out keeps bounce rates down. Email providers watch these rates, and a clean list protects the reputation your messages depend on.
Contacts are not approached twice
Duplicate rows lead to two reps sending nearly the same message to one person. Finding them early avoids the embarrassment and the lost goodwill that come from it. Customers also notice when they are not emailed twice.
Lookups and pivot tables work again
Trailing spaces and inconsistent capitalization break matches between sheets. Once values are normalized, formulas that join data return the right results and totals add up. Reports then match reality, which helps every later analysis.
Every change is explained
The Flag column says what was fixed or what looks wrong, and the email gives counts. Nothing disappears silently, so someone can check a questionable change instead of trusting blindly.
Data stays clean instead of piling up
A short daily pass is easier than a heroic cleanup every year. Errors are handled while the person who entered them still remembers what they meant. Small fixes made daily rarely turn into a big project.

How it works

What happens, step by step

Every day ClickRuntime reads your leads Google Sheet.
It tidies names and email addresses, removes duplicate rows and drops rows with an email address that isn't valid.
The cleaned list is written back to the sheet.
You get an email saying how many rows were changed or removed and how many are left.

Before it runs live

Connect your Google Sheets account and pick the spreadsheets it may use

What you get

RunsDaily

Keep your sheet free of duplicates and bad datatoday, 9:00Succeeded

Email · Leads sheet tidied14 rows changed or removed, 1,206 rows left.

Build your own

Need it to work differently?

This template is a starting point. Copy the prompt and change it, or describe a different automation from scratch, with your own conditions, more apps or an AI step. Your assistant writes it, tests it and runs it on ClickRuntime.

Your own rules
Conditions, thresholds, filters and exceptions, written the way you'd explain them to a colleague.
Any number of apps
One automation can read from one app and update several others. Over 248 apps are ready, and anything with an API works too.
AI where it helps
Summaries, classification, extraction and drafts use ClickRuntime's built-in AI, with no extra key.
On a schedule or on each event
Runs as often as every 5 minutes, or the moment an app sends an event, and keeps track of what it already handled.

Changes you can ask for

Run it at a different time, more or less often, or only on weekdays
Add an AI step, for example to summarize, sort or draft a reply
Add another app, for example also send it to Slack
Combine it with other Google Sheets steps into one automation

Under the hood

The code that runs this automation

This is the Python script that runs each time the automation starts. Your AI assistant writes it for you when you send the prompt, and you allow each step in your assistant. To change how the automation works, describe the change to your assistant and it updates the script for you. For every function it can use, see the script reference.

clean_dedupe_and_validate_rows.pyexample

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
import re
from clickruntime import on_trigger, credentials, notify

SHEET_ID = "your-spreadsheet-id"
EMAIL = re.compile(r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$", re.I)

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    rows = sheets.values().get(spreadsheetId=SHEET_ID, range="Leads!A2:D").execute().get("values", [])
    seen, clean, changed = set(), [], 0
    for row in rows:
        name, email, phone, _ = (row + [""] * 4)[:4]
        key = email.strip().lower()
        if key and key in seen:
            changed += 1
            continue
        seen.add(key)
        new = [" ".join(name.split()).title(), key, re.sub(r"[^\d+]", "", phone), "" if EMAIL.match(key) else "Invalid email"]
        changed += new != (row + [""] * 4)[:4]
        clean.append(new)
    sheets.values().clear(spreadsheetId=SHEET_ID, range="Leads!A2:D").execute()
    sheets.values().update(spreadsheetId=SHEET_ID, range="Leads!A2", valueInputOption="RAW", body={"values": clean}).execute()
    notify.email_me("Leads sheet tidied", f"{changed} rows changed or removed, {len(clean)} rows left.")
    return {"changed": changed}

FAQ

Common questions

Does it delete duplicate rows?

It flags duplicates in the Flag column and does not remove them automatically, since deleting a row cannot be undone easily. You decide which row to keep. If you prefer, duplicates can be moved to a separate tab instead.

How does it decide two rows are duplicates?

You choose the matching key, usually the lowercase email address, or name plus phone number. Rows with the same key are grouped. A stricter or looser rule can be set, depending on how often your data contains different people with the same name.

Can it check whether an email address really exists?

It checks the format and common typos, such as a missing at sign or a misspelled domain. Confirming that a mailbox exists needs a verification service, which you can add as a further step if deliverability is critical.

What permissions does it need?

read+write. Nothing else in your accounts is used.

Does it need AI?

No. It runs without AI, so it doesn't use your plan's AI allowance.

How many runs does it use?

One run each time it runs (daily). Checks that find nothing to do and stop right away aren't counted.

Can I change how it works?

Yes. Describe the change to your AI assistant, for example a different schedule, channel or filter. It updates the automation, tests it and tells you what changed.

On a schedule