Link building with Google Sheets: find guest post prospects and emails in 15 minutes

Executive overview

Manual link prospecting is slow. This tutorial builds a Google Sheet pipeline that pulls prospects from Ahrefs Content Explorer, parses author names, finds emails via Hunter, and validates them with NeverBounce — all from a single sheet.

Two core Sheets functions power the whole template: IFERROR (suppresses ugly errors) and ARRAYFORMULA (applies one formula to an entire column without dragging).

A well-structured import + four formulas replaces hours of manual prospecting work.

Finding and exporting prospects

• Open Ahrefs Content Explorer, search a niche keyword, set to title search
• Filter: one page per domain, English only, live pages, domain rating 50–60
• Export the maximum number of results as a CSV
• Content Explorer surfaces author names alongside URLs — critical for email finding

Setting up the Google Sheet

• Import the CSV (File → Import → Upload → Replace Current Sheet); rename tab to ceimport
• Create a second sheet (Master Guest Post) using the QUERY function to pull only needed columns: title, URL, author name, domain rating
• Add a WHERE clause to exclude rows with no author name (WHERE D is not null)
• Add AND D contains ' ' to drop single-word author names (email finders need first + last)

Parsing first and last names

• Create First and Last columns in the master sheet
• Use LEFT + FIND to extract the first name
• Use TRIM, RIGHT, SUBSTITUTE, REPT to grab the last word as the last name
• Wrap both formulas in ARRAYFORMULA and IFERROR to cover the full column cleanly

Finding emails with Hunter

• Install the Hunter add-on (Add-ons → Get Add-ons → search "Hunter"); free tier includes 50 lookups/month
• Open Hunter → Email Finder tab; map columns: first name, last name, domain (use the full URL — Hunter extracts the root domain automatically)
• Leave company name blank; run Find Email Address

Validating emails with NeverBounce

• Create a new sheet (Email Validation); use UNIQUE to extract a deduplicated email list
• Export as CSV; upload to NeverBounce → Add List → Clean My List
• Download the results CSV; re-import into the Email Validation sheet
• Back in the master sheet, add a Validation column; use VLOOKUP against the NeverBounce results (columns A:B, index 2, exact match)
• Wrap in ARRAYFORMULA + IFERROR; add a column filter to show only valid emails
• Run COUNTIF to tally the valid count

What to do next

• Export the filtered list to your outreach tool
• Still manually review each site for traffic, content relevance, and fit before pitching
• Emails not found automatically must be found manually