"Invalid value for Lead Source" is the import error that eats an afternoon. Your CSV says "linkedin", "LinkedIn Ads", and "LI" while your CRM only accepts "LinkedIn". Same story for deal stage, industry, country, and record owner. These five Google Sheets fixes map every messy value to an allowed one before you upload. Assume the messy value is in column F.
1. See every distinct value you actually have
=SORT(UNIQUE(TRIM(F2:F)))
This short list is your cleanup to-do, usually 20 to 40 variants instead of thousands of rows.
2. Build a mapping tab
Make a tab called map with the messy value in column A and the CRM-allowed value in column B. Then:
=IFERROR(VLOOKUP(LOWER(TRIM(F2)),map!A:B,2,FALSE),"UNMAPPED")
Put lowercase values in map!A so "LinkedIn " and "linkedin" hit the same row.
3. Flag values the CRM will reject
Paste the CRM's allowed picklist into allowed!A:A:
=IF(COUNTIF(allowed!A:A,G2)=0,"REJECT","")
4. Map owners by email, not name
Importers match owners on user email. Turn "Sam K." into the login:
=IFERROR(VLOOKUP(TRIM(H2),owners!A:B,2,FALSE),"default-owner@yourco.com")
Pick a real default owner so nothing lands unassigned.
5. Count what's left before you import
=COUNTIF(G2:G,"UNMAPPED")+COUNTIF(I2:I,"REJECT")
Import when this reads 0.
Run these on a copy, fix the UNMAPPED and REJECT rows, then import only the mapped columns.
Rather hand off the cleanup? Send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours. No CRM login needed: email us your CSV (reply to your receipt or send it to slrescue@mail.grokbot.com). Bigger list? Intro $99 covers up to 500 rows, and Growth ($299, up to 2,000) and Bulk ($499, up to 5,000) are on pureservices.store. Want to check the file first? The free CSV preflight checker runs in your browser.
Top comments (0)