ZapBounce

ZapBounce and Google Sheets

Google Sheets is where a lot of email lists actually live, whatever system they came from. Apps Script can verify a column in place.

The thing that breaks a naive implementation is Apps Script's execution time limit. A loop calling the API once per row hits it partway through a list and leaves the sheet half done.

Apps Script stops mid-list and leaves no marker

Apps Script has a maximum execution time per run. A per-row loop on a few thousand addresses reaches it, the script stops, and the rows below stay empty with nothing recording where it got to.

Running it again then starts from the top and re-verifies everything above, which costs credits and time for answers you already have.

The fix is to submit a batch in one call and collect the results in a second run, using a trigger rather than a loop.

How the data moves

  1. Submit the whole column as one batch

    UrlFetchApp posting the array of addresses. One call, well inside the execution limit, whatever the row count.

  2. Store the batch id in script properties

    So the second run knows what to collect rather than starting over.

  3. Collect results on a time-driven trigger

    A trigger that runs a few minutes later, fetches the results page by page, and writes them back.

  4. Write back with setValues, not cell by cell

    One range write rather than a call per cell. Cell-by-cell writes are the other common way to hit the time limit.

Setting it up

  1. Open Extensions, then Apps Script, and store the API key in script properties rather than in the code.
  2. Write a function that reads the address column and posts it to the batches endpoint in one call.
  3. Save the returned batch id to script properties.
  4. Create a time-driven trigger that runs a collection function a few minutes later.
  5. Have the collection function page through the results and write them back with a single setValues call.
  6. Add a menu item so the sheet's owner can run it without opening the editor.

Google Sheets: common questions

Why does my script stop halfway?

The Apps Script execution time limit. Submit a batch in one call instead of looping per row.

Where should the key go?

Script properties. A key in the code is visible to anyone with edit access to the sheet.

Can I verify as rows are added?

With an onEdit trigger, yes, for occasional additions. For a column of thousands, batch it.

Reference

Check a Google Sheets export today

100 free checks a month, no card. Unknown results and duplicates are never billed.

Get an API keySee pricing