Skip to content

Verify the email addresses in a Google Sheets list

Integration CRM, support and community

Google Sheets email verification in three steps: export the address column, verify it in PricklyMails, then filter out the rows to remove.

There is no native Google Sheets connector: you export the list, PricklyMails verifies it, and you bring the result back into Google Sheets. New addresses can go through the API before they ever reach Google Sheets.

Verify for free

Step by step

Clean your list in three steps

  1. In Google Sheets

    Export your addresses from Google Sheets

    1. Open your spreadsheet in Google Sheets.
    2. On the tab of the sheet that holds your addresses, click the Down arrow, then Duplicate.
    3. On the copy, right-click each column other than the addresses and choose Delete column.
    4. If the first row holds a header, right-click it and choose Delete row.
    5. Click File then Download, then choose the CSV file type.
    6. Delete the copy: click the Down arrow on its tab, then Delete, then OK.

    Tip

    Rather skip the file? Select the column of addresses, copy it, and paste it into the batch form in the next step.

    SourceGoogle Sheets's official help center
  2. In PricklyMails

    Verify the file in PricklyMails

    1. In the dashboard, open Batches and click New batch.
    2. Drop the file you exported from Google Sheets: a .csv or .txt with one address per line, no header row and no quotes. A header row would count as an address in the estimate, though it is never billed, and a quoted address would not be verified: the report lists both as malformed. You can also paste the list.
    3. The dashboard counts the addresses and shows the estimated cost in tokens before anything is sent, then you click Submit batch. A batch holds at least 20 addresses, and your plan sets the largest list it can hold.
    4. The list is verified in the background. Once the batch is done, open it and click Download report: you get a CSV with a row for each address of your file, giving its verdict, its reason, its score and its recommendation, with the dashboard's labels.

    Note

    Download the report from the browser that sent the list: that browser is what keeps the addresses, for the 16 most recent batches at most. Our servers, in Germany, only keep a one-way fingerprint of each one.
    What the report says

    Every address gets a recommendation: the action to take. The report writes it with the dashboard's label; the API returns a short form of it.

    Send
    The address is valid, or hosted by a large provider that accepts every address without confirming any. Send as usual.
    Review
    A domain that accepts any address (catch-all), a generic address such as contact@, a full mailbox, or a server that never answered, or refused to. Take a look before you send.
    Remove
    The address does not exist, its domain cannot receive mail, or it is disposable, mistyped or malformed. Take it out.

    Note

    A malformed address is never verified: the batch counts it under "Malformed addresses", and the report still lists it, with the verdict "Invalid", the reason "Malformed address" and the recommendation "Remove", so you take it out of Google Sheets with the others. A duplicate, counted under "Duplicates removed", appears only once.

    Behind each recommendation sit a verdict (Valid, Risky, Invalid or Not verifiable), a reason and a score from 0 to 100, each in its own column of the report. When a server never answers, the address comes back "Not verifiable" and the checks that got no answer are refunded.

  3. In Google Sheets

    Bring the result back into Google Sheets

    Open the report in a spreadsheet and filter the Recommendation column.

    Remove

    A new column gives each row the recommendation of its address, and a filter hides the rows to remove.

    Google Sheets imports the report as a new sheet of your spreadsheet, and the XLOOKUP function copies each address's recommendation from it onto the row of that address.

    1. In your spreadsheet, click File then Import, then upload the report.
    2. Select Insert new sheets, then click Import.
    3. Double-click the tab of the new sheet and rename it Report.
    4. Back on the sheet of your list, right-click the column of addresses and select Insert 1 column right.
    5. In the new column, on the row of your first address, enter =XLOOKUP(A2, Report!A:A, Report!E:E, ""), with A2 replaced by the cell of that address.
    6. Copy that cell down to the row of your last address.
    7. Select the new column, then click Data then Create a filter.
    8. Click Filter at the top of the column, uncheck Remove under Filter by values, then click OK.
    Review

    They deserve a look before you use the list. To go through them, filter the same column on Review alone.

    Send

    Their rows stay as they are: nothing to do.

    Note

    If the locale of your spreadsheet, set in File then Settings, writes decimals with a comma, as in France or Germany, separate the arguments of the formula with semicolons.

    Tip

    To hide the rows for yourself alone, without changing what the other people on the spreadsheet see, click Data then Create filter view instead of creating a filter.

    SourceGoogle Sheets's official help center

Verify each address before it reaches Google Sheets

Does your form handler or script add rows to a sheet through the Google Sheets API, with the spreadsheets.values.append (opens in a new tab) method? Ask PricklyMails first. If the recommendation is remove, don't append the row.

One call starts the verification and already flags a likely typo, a second one reads the verdict. Depending on your plan, a signed webhook can also send it to you as soon as it's ready.

Request
curl -s https://api.pricklymails.com/api/verify \
  -H "Authorization: Bearer pm_live_9f3a..." \
  -H "Content-Type: application/json" \
  -d '{ "email": "ana.silva@gmail.con" }' \
  | jq '{job_uuid, suggestion}'
Response
{
  "job_uuid": "8f1c2a9e-4b7d-4e2a-9c61-3d5f0b7a2e18",
  "suggestion": "ana.silva@gmail.com"
}
Request
curl -s https://api.pricklymails.com/api/verify/$JOB_UUID \
  -H "Authorization: Bearer pm_live_9f3a..." \
  | jq '.verifications[0] | {status, reason, score, recommendation}'
Response
{
  "status": "invalid",
  "reason": "no_mx_records",
  "score": 0,
  "recommendation": "remove"
}
Read the API docs

Frequently asked questions

Does the filter delete the rows to remove?

No, a filter only hides rows. Google Sheets' help center adds that a filter applies to everyone with access to the spreadsheet, and that anyone allowed to edit it can change the filter, so the hidden rows can come back. To take a row out for good, right-click it and choose Delete row.

SourceGoogle Sheets's official help center

Why does the new column stay empty on some rows?

XLOOKUP returns its missing value, here an empty text, when it finds no match for the address in the report's first column. Either the address was not in the batch, or the report was downloaded from a browser other than the one that sent the list: that report gives a reference in place of each address, which matches no row.

SourceGoogle Sheets's official help center

Which import option keeps my list safe?

Insert new sheets adds the report as new sheets beside your data. Replace spreadsheet and Replace current sheet would replace your list with the report, Append rows to current sheet would add the report under your last row, and Replace data starting at selected cell would overwrite the cells from the one you selected.

SourceGoogle Sheets's official help center

Does PricklyMails connect to Google Sheets directly?

No, there is no native connector: you go through a file export, as above. You can also call the PricklyMails API from your own code before an address reaches Google Sheets.

These trademarks belong to their respective owners. Naming them implies no partnership.

Check your list before your next send.

1,000 tokens free when you sign up, no credit card. After that, you only pay for answers.

Verify for free See pricing