Key Takeaways
- Google Apps Script lets you verify emails directly inside Google Sheets, so you clean a list where it already lives without exporting a CSV.
- A custom VERIFYEMAIL function checks a single cell, which is convenient but fires one API call per cell and is best for small sheets.
- For real lists, a menu-driven batch run with UrlFetchApp verifies all rows, writes results back per row, and handles rate limits and caching.
- Store the API key in Script Properties, never in the sheet or the code, and throttle requests so a large sheet does not trip rate limits.
A huge amount of real-world email data lives in spreadsheets: lead lists from a trade show, exports from a CRM, signup forms piped into a sheet. Cleaning that data usually means exporting a CSV, running it through a tool, and importing it back, which is friction nobody enjoys. Verifying emails in Google Sheets with Apps Script removes that friction by cleaning the list where it already lives. This guide builds two things: a custom VERIFYEMAIL function for quick checks and a menu-driven batch cleaner for real lists, both calling the v2 API. For other environments, see the email verification integrations hub.
Apps Script is JavaScript that runs inside Google Workspace, and it can make outbound HTTP calls with UrlFetchApp, which is all we need to reach the verification API.
Store the API Key Safely First
Before any code, put your API key in Script Properties, not in a cell and not hardcoded in the script. Open Extensions, then Apps Script, then Project Settings, and add a script property named EMAILVERIFIER_API_KEY. This keeps the key out of the spreadsheet, so sharing the sheet never leaks it.
The Custom VERIFYEMAIL Function
A custom function lets you type =VERIFYEMAIL(A2) in a cell and get a status back. It is the most convenient option for small sheets, with one caveat we will cover: it fires a call per cell.
The CacheService call is what makes this usable. Google recalculates custom functions often, and without caching every recalculation would fire a fresh API call and burn credits on addresses you already checked. Caching the result for six hours means each address costs one call, not one per recalculation.
The Menu-Driven Batch Cleaner
For a real list of hundreds or thousands of rows, a custom function per cell is slow and fragile. A batch run is better: add a menu, verify every row in one pass, and write the results back. The onOpen trigger adds the menu, and the batch function does the work with throttling.
Three choices make this production-ready. It reads all emails in one getValues call and writes results in one setValues call, which is far faster than touching cells individually. It handles a 429 rate-limit response by waiting and retrying. And the small sleep between calls keeps a large sheet from tripping limits. Apps Script has a six-minute execution cap, so for very large sheets, process in chunks across multiple runs rather than one long pass.
From Sheet to Clean List
Once the status column is populated, filter the sheet by result: keep the passed rows, delete or suppress the failed ones, and review the risky and role-labeled rows. You now have a cleaned list in the same place you started, ready to import into your email platform.
New developers can get an API key and test both patterns on a real sheet with 100 free email verification credits, and the email verification API documentation covers the full response schema and rate limits so you can extend the script to surface disposable, role, and catch-all flags per row.
Frequently Asked Questions
Can I verify emails directly in Google Sheets?
Yes, with Google Apps Script. You can build a custom VERIFYEMAIL function that checks a single cell, or a menu-driven batch script that verifies an entire column and writes the results back. Both call the verification API with UrlFetchApp, so you clean the list where it lives without exporting a CSV.
Why should I cache results in the custom function?
Because Google recalculates custom functions frequently, and without caching, every recalculation fires a fresh API call and bills you again for addresses you already checked. Using CacheService to store each result for a few hours means every address costs a single call rather than one per recalculation.
How do I handle large sheets within the Apps Script time limit?
Apps Script caps execution at about six minutes, so for large sheets process in chunks. Store a cursor in Script Properties marking the last verified row, verify a batch under the limit, and use a time-driven trigger to continue across multiple runs until the whole sheet is clean.
Where should I store my API key in Apps Script?
In Script Properties, accessed with PropertiesService, never in a spreadsheet cell or hardcoded in the script body. Script Properties keeps the key inside your Apps Script project where collaborators who can see the sheet cannot read it, which protects the key when you share the spreadsheet.