Clean a messy Google Form sheet with Python

Form responses arrive messy: names in random capitals, phone numbers written five different ways, the same person twice and rows with blanks.

This program reads a messy sheet, cleans every row, removes duplicates by phone number and drops incomplete rows. Type a city below to keep only that city, or leave it blank for everyone.

Clean a messy Google Form sheet

Fix names, phone numbers and duplicates in one go, then filter by city.

Keep only this city (blank = all)

→ press Run. The first run downloads Python (~4 MB, cached after that).

The Python behind it · 36 lines

1import csv, io, re
2
3city = ""  # keep only this city ("" = all)
4
5# A messy sheet, exactly how it comes from a Google Form
6messy = """Name, Phone ,City,Amount
7  rahul SHARMA ,+91 98200 12345,mumbai,"1,200"
8Priya Nair,9820011111,Mumbai ,850
9rahul sharma,98200-12345,MUMBAI,"1,200"
10ANJALI rao,  080 4123 9876,bengaluru,
11Vikram Singh,+91-99300-55555,Delhi,2300
12priya nair,09820011111,mumbai,850
13"""
14
15rows, seen, skipped = [], set(), 0
16for r in csv.DictReader(io.StringIO(messy), skipinitialspace=True):
17    r = {k.strip(): (v or "").strip() for k, v in r.items()}
18    phone = re.sub(r"\D", "", r["Phone"])[-10:]  # keep the last 10 digits
19    if phone in seen or not r["Amount"]:
20        skipped += 1
21        continue
22    seen.add(phone)
23    rows.append({"Name": r["Name"].title(), "Phone": phone,
24                 "City": r["City"].title(), "Amount": int(r["Amount"].replace(",", ""))})
25
26if city.strip():
27    rows = [r for r in rows if r["City"].lower() == city.strip().lower()]
28
29if not rows:
30    print(f"No one from {city.strip()} in this sheet. Try Mumbai or Delhi.")
31else:
32    print(f"{'Name':<14}{'Phone':<12}{'City':<11}{'Amount':>7}")
33    for r in rows:
34        print(f"{r['Name']:<14}{r['Phone']:<12}{r['City']:<11}{r['Amount']:>7,}")
35    print(f"\n✓ {len(rows)} clean rows · {skipped} duplicates/blanks removed")
36    print(f"  Total: ₹{sum(r['Amount'] for r in rows):,}")

How the code works, step by step

1.Start from the raw sheet

The sheet is a CSV string, exactly as you would get by downloading a Google Form's responses. Note the stray spaces, the capitals and the same phone number written three ways.

5# A messy sheet, exactly how it comes from a Google Form
6messy = """Name, Phone ,City,Amount
7  rahul SHARMA ,+91 98200 12345,mumbai,"1,200"
8Priya Nair,9820011111,Mumbai ,850
9rahul sharma,98200-12345,MUMBAI,"1,200"
10ANJALI rao,  080 4123 9876,bengaluru,
11Vikram Singh,+91-99300-55555,Delhi,2300
12priya nair,09820011111,mumbai,850
13"""

2.Read it with csv.DictReader

DictReader turns each line into a dictionary keyed by the column names, and the dictionary comprehension strips spaces from every key and value.

15rows, seen, skipped = [], set(), 0
16for r in csv.DictReader(io.StringIO(messy), skipinitialspace=True):
17    r = {k.strip(): (v or "").strip() for k, v in r.items()}

3.Normalise phones and skip duplicates

re.sub(r"\D", "", ...) deletes everything that is not a digit, and [-10:] keeps the last ten digits, so +91 98200 12345 and 98200-12345 become the same number. A set remembers numbers already seen, and .title() fixes the names.

18    phone = re.sub(r"\D", "", r["Phone"])[-10:]  # keep the last 10 digits
19    if phone in seen or not r["Amount"]:
20        skipped += 1
21        continue
22    seen.add(phone)
23    rows.append({"Name": r["Name"].title(), "Phone": phone,
24                 "City": r["City"].title(), "Amount": int(r["Amount"].replace(",", ""))})

4.Filter and print

An optional filter keeps one city. The f-string widths line the table up, and the last lines report how many rows were removed.

26if city.strip():
27    rows = [r for r in rows if r["City"].lower() == city.strip().lower()]
28 
29if not rows:
30    print(f"No one from {city.strip()} in this sheet. Try Mumbai or Delhi.")
31else:
32    print(f"{'Name':<14}{'Phone':<12}{'City':<11}{'Amount':>7}")
33    for r in rows:
34        print(f"{r['Name']:<14}{r['Phone']:<12}{r['City']:<11}{r['Amount']:>7,}")
35    print(f"\n✓ {len(rows)} clean rows · {skipped} duplicates/blanks removed")
36    print(f"  Total: ₹{sum(r['Amount'] for r in rows):,}")

Make it yours

Read a real downloaded CSV with open() and write the clean rows back with csv.DictWriter.
Flag phone numbers that do not have 10 digits instead of trimming them.
Move to pandas once the sheet has thousands of rows.

The lessons that teach this, in order

1. Files & CSV in memory 2. Regex data extraction 3. Strings

Build it yourself, step by step Start with Files & CSV in memory

Questions

Can Python clean an Excel file, not just CSV?: Yes. Save the sheet as CSV and use this code as it is, or read the .xlsx directly with pandas or openpyxl and apply the same cleaning steps.
How does it decide two rows are the same person?: By phone number, after reducing it to its last ten digits. Names are too easy to spell differently to use as the key.

Clean a messy Google Form sheet with Python

Form responses arrive messy: names in random capitals, phone numbers written five different ways, the same person twice and rows with blanks.

This program reads a messy sheet, cleans every row, removes duplicates by phone number and drops incomplete rows. Type a city below to keep only that city, or leave it blank for everyone.

1import csv, io, re

3city = "" # keep only this city ("" = all)

5# A messy sheet, exactly how it comes from a Google Form

6messy = """Name, Phone ,City,Amount

7 rahul SHARMA ,+91 98200 12345,mumbai,"1,200"

8Priya Nair,9820011111,Mumbai ,850

9rahul sharma,98200-12345,MUMBAI,"1,200"

10ANJALI rao, 080 4123 9876,bengaluru,

11Vikram Singh,+91-99300-55555,Delhi,2300

12priya nair,09820011111,mumbai,850

13"""

15rows, seen, skipped = [], set(), 0

16for r in csv.DictReader(io.StringIO(messy), skipinitialspace=True):

17 r = {k.strip(): (v or "").strip() for k, v in r.items()}

18 phone = re.sub(r"\D", "", r["Phone"])[-10:] # keep the last 10 digits

19 if phone in seen or not r["Amount"]:

20 skipped += 1

21 continue

22 seen.add(phone)

23 rows.append({"Name": r["Name"].title(), "Phone": phone,

24 "City": r["City"].title(), "Amount": int(r["Amount"].replace(",", ""))})

26if city.strip():

27 rows = [r for r in rows if r["City"].lower() == city.strip().lower()]

29if not rows:

30 print(f"No one from {city.strip()} in this sheet. Try Mumbai or Delhi.")

31else:

32 print(f"{'Name':<14}{'Phone':<12}{'City':<11}{'Amount':>7}")

33 for r in rows:

34 print(f"{r['Name']:<14}{r['Phone']:<12}{r['City']:<11}{r['Amount']:>7,}")

35 print(f"\n✓ {len(rows)} clean rows · {skipped} duplicates/blanks removed")

36 print(f" Total: ₹{sum(r['Amount'] for r in rows):,}")

How the code works, step by step

1.Start from the raw sheet

The sheet is a CSV string, exactly as you would get by downloading a Google Form's responses. Note the stray spaces, the capitals and the same phone number written three ways.

5# A messy sheet, exactly how it comes from a Google Form
6messy = """Name, Phone ,City,Amount
7  rahul SHARMA ,+91 98200 12345,mumbai,"1,200"
8Priya Nair,9820011111,Mumbai ,850
9rahul sharma,98200-12345,MUMBAI,"1,200"
10ANJALI rao,  080 4123 9876,bengaluru,
11Vikram Singh,+91-99300-55555,Delhi,2300
12priya nair,09820011111,mumbai,850
13"""

2.Read it with csv.DictReader

DictReader turns each line into a dictionary keyed by the column names, and the dictionary comprehension strips spaces from every key and value.

15rows, seen, skipped = [], set(), 0
16for r in csv.DictReader(io.StringIO(messy), skipinitialspace=True):
17    r = {k.strip(): (v or "").strip() for k, v in r.items()}

3.Normalise phones and skip duplicates

18    phone = re.sub(r"\D", "", r["Phone"])[-10:]  # keep the last 10 digits
19    if phone in seen or not r["Amount"]:
20        skipped += 1
21        continue
22    seen.add(phone)
23    rows.append({"Name": r["Name"].title(), "Phone": phone,
24                 "City": r["City"].title(), "Amount": int(r["Amount"].replace(",", ""))})

4.Filter and print

An optional filter keeps one city. The f-string widths line the table up, and the last lines report how many rows were removed.

26if city.strip():
27    rows = [r for r in rows if r["City"].lower() == city.strip().lower()]
28 
29if not rows:
30    print(f"No one from {city.strip()} in this sheet. Try Mumbai or Delhi.")
31else:
32    print(f"{'Name':<14}{'Phone':<12}{'City':<11}{'Amount':>7}")
33    for r in rows:
34        print(f"{r['Name']:<14}{r['Phone']:<12}{r['City']:<11}{r['Amount']:>7,}")
35    print(f"\n✓ {len(rows)} clean rows · {skipped} duplicates/blanks removed")
36    print(f"  Total: ₹{sum(r['Amount'] for r in rows):,}")

Make it yours

Read a real downloaded CSV with open() and write the clean rows back with csv.DictWriter.

Flag phone numbers that do not have 10 digits instead of trimming them.

Move to pandas once the sheet has thousands of rows.

The lessons that teach this, in order

Questions

Can Python clean an Excel file, not just CSV?

Yes. Save the sheet as CSV and use this code as it is, or read the .xlsx directly with pandas or openpyxl and apply the same cleaning steps.

How does it decide two rows are the same person?

By phone number, after reducing it to its last ten digits. Names are too easy to spell differently to use as the key.