Scrub Phone Numbers in Google Sheets

If your call list lives in Google Sheets, you do not need to export it to scrub it. A short script adds a NumberBroom menu to your spreadsheet. Select the column of phone numbers, choose Scrub selected numbers, confirm the count and price, and the answers appear in the six columns to the right.

What it writes

If you keep landlines on purpose, because you dial by hand or call businesses, filter on Litigator alone and ignore Keep. That is the same choice as the Litigators only scrub on an upload.

Set it up

1. Get an API key and credits. Sign in, open Settings, create an API key and add credits ($10 minimum). Give the key a daily limit if you want a hard cap on what one day of scrubbing can spend.
2. Add the script. In your spreadsheet choose Extensions > Apps Script, delete the sample code, paste the script below and click Save.
3. Reload the spreadsheet. A NumberBroom menu appears next to Help.
4. Set your key. Choose NumberBroom > Set API key and paste it.
5. Scrub. Select the column of phone numbers, with or without its heading, and choose NumberBroom > Scrub selected numbers. The dialog shows how many numbers will be checked and the most it can cost.

The first time you run it, Google asks you to authorize the script. Because it is your own copy and not a published add-on, Google shows a screen saying it has not verified the app: choose Advanced and continue. The script asks for three things: this spreadsheet only, connecting to numberbroom.com, and showing its dialogs. It sends only the numbers you select.

What it costs

Each number is $0.20 from your API credits. A number that is not a US number is answered free, a lookup that fails is never charged, and a row that already has results is skipped. If your credits run out partway, the script stops, keeps every answer it wrote, and picks up where it left off the next time you run it. Google stops a script after six minutes, so a long column may take two or three runs; each run continues from the first row without results.

For a list of thousands, an upload is usually the better deal: list scrubs are priced in volume bands that get cheaper past 2,000 numbers, and you can preview 20 rows free before paying.

It will not overwrite your data

The script writes only into the six columns to the right of the phone column, and only when they are empty or hold its own earlier results. If they hold anything else, such as names or notes, it stops before checking a single number and asks you to insert six empty columns first.

The script

/**
 * NumberBroom for Google Sheets
 *
 * Select a column of phone numbers and choose NumberBroom > Scrub selected
 * numbers. Each number gets its line type, carrier, a TCPA litigator check and
 * NumberBroom's keep-or-remove outcome, written into the six columns to its
 * right. It costs $0.20 a number from your NumberBroom API credits; a number
 * that is not a valid US number is answered free.
 *
 * Results are written as plain values, never as a formula. A formula runs
 * again whenever its cell changes, and every run would be charged again.
 * Rows that already have results are skipped, so running it twice never
 * charges twice.
 *
 * Your API key is kept in your own Google account (user properties), not in
 * the spreadsheet, so people you share the sheet with cannot see or use it.
 * The script sends only the numbers you select to numberbroom.com.
 *
 * Setup: https://numberbroom.com/google-sheets-phone-scrub
 *
 * @OnlyCurrentDoc  Google asks for access to this spreadsheet only.
 */

var NB_API = "https://numberbroom.com/api/v1";
var NB_COLUMNS = ["Line type", "Carrier", "Litigator", "Outcome", "Keep", "Checked"];
var NB_RATE = 0.2;
var NB_BATCH = 10; // numbers sent at once
var NB_TIME_BUDGET_MS = 5 * 60 * 1000; // Apps Script stops a run at six minutes

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("NumberBroom")
    .addItem("Scrub selected numbers", "nbScrubSelection")
    .addItem("Check my credits", "nbShowCredits")
    .addSeparator()
    .addItem("Set API key", "nbSetKey")
    .addToUi();
}

function onInstall() {
  onOpen();
}

function nbSetKey() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.prompt(
    "NumberBroom API key",
    "Paste a key from numberbroom.com/settings. It starts with nb_live_ and is kept in your Google account, not in this sheet.",
    ui.ButtonSet.OK_CANCEL
  );
  if (answer.getSelectedButton() !== ui.Button.OK) return;
  var key = answer.getResponseText().trim();
  if (!/^nb_live_[A-Za-z0-9_-]{16,}$/.test(key)) {
    ui.alert("That does not look like a NumberBroom API key. Keys start with nb_live_.");
    return;
  }
  PropertiesService.getUserProperties().setProperty("NB_KEY", key);
  ui.alert("Saved. Select a column of phone numbers, then choose NumberBroom > Scrub selected numbers.");
}

function nbKey_() {
  var key = PropertiesService.getUserProperties().getProperty("NB_KEY");
  if (!key) {
    SpreadsheetApp.getUi().alert("Add your API key first: NumberBroom > Set API key.");
    return null;
  }
  return key;
}

function nbRequest_(key, path, payload) {
  var options = {
    method: payload ? "post" : "get",
    headers: { Authorization: "Bearer " + key },
    muteHttpExceptions: true,
  };
  if (payload) {
    options.contentType = "application/json";
    options.payload = JSON.stringify(payload);
  }
  return { url: NB_API + path, options: options };
}

function nbBody_(response) {
  try {
    return JSON.parse(response.getContentText());
  } catch (e) {
    return {};
  }
}

function nbShowCredits() {
  var key = nbKey_();
  if (!key) return;
  var req = nbRequest_(key, "/credits");
  var response = UrlFetchApp.fetch(req.url, req.options);
  var body = nbBody_(response);
  var ui = SpreadsheetApp.getUi();
  if (response.getResponseCode() !== 200) {
    ui.alert(nbErrorText_(response.getResponseCode(), body));
    return;
  }
  ui.alert(
    "NumberBroom credits",
    "$" + Number(body.credits).toFixed(2) + " left, enough for " + body.lookupsRemaining + " numbers. Top up at numberbroom.com/settings.",
    ui.ButtonSet.OK
  );
}

/** Something that could be a phone number: seven or more digits. */
function nbLooksLikePhone_(value) {
  var digits = String(value).replace(/\D/g, "");
  return digits.length >= 7 && digits.length <= 15;
}

function nbIsOurHeader_(row) {
  for (var i = 0; i < NB_COLUMNS.length; i++) if (String(row[i]) !== NB_COLUMNS[i]) return false;
  return true;
}

/** A row this script wrote: its Litigator and Keep cells hold our words. */
function nbIsOurRow_(row) {
  return ["yes", "no", "not_checked"].indexOf(String(row[2])) !== -1 && ["yes", "no"].indexOf(String(row[4])) !== -1;
}

function nbOccupied_() {
  return "The six columns to the right of your selection already hold other data. Insert six empty columns to the right of the phone column, then try again.";
}

function nbIsEmpty_(row) {
  for (var i = 0; i < row.length; i++) if (row[i] !== "" && row[i] !== null) return false;
  return true;
}

function nbErrorText_(code, body) {
  if (code === 401) return "NumberBroom did not accept the API key. Set a new one: NumberBroom > Set API key.";
  if (code === 402) return "Not enough NumberBroom API credits. Top up at numberbroom.com/settings, then run it again. Rows that already have results are skipped.";
  if (code === 429) return (body && body.message) || "A daily limit was reached. Run it again tomorrow; rows that already have results are skipped.";
  return (body && body.message) || "NumberBroom could not answer (HTTP " + code + "). Nothing was charged for the numbers left blank; run it again to retry them.";
}

/** One row of results for a /verify answer, in NB_COLUMNS order. */
function nbResultRow_(body) {
  return [
    body.lineType || "",
    body.carrier || "",
    body.litigator || "not_checked",
    body.outcome || "",
    body.keep ? "yes" : "no",
    new Date(),
  ];
}

function nbScrubSelection() {
  var ui = SpreadsheetApp.getUi();
  var key = nbKey_();
  if (!key) return;

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  if (!range || range.getNumColumns() !== 1) {
    ui.alert("Select one column of phone numbers, then try again.");
    return;
  }
  var top = range.getRow();
  var col = range.getColumn();
  var count = range.getNumRows();
  var phones = range.getValues().map(function (r) { return String(r[0]).trim(); });

  // Where the column headings go: the first selected row when it is a heading
  // rather than a number, else the row above the selection when there is one.
  var headerRow = null;
  if (!nbLooksLikePhone_(phones[0])) headerRow = top;
  else if (top > 1 && !nbLooksLikePhone_(sheet.getRange(top - 1, col).getValue())) headerRow = top - 1;
  var header = headerRow ? sheet.getRange(headerRow, col + 1, 1, NB_COLUMNS.length).getValues()[0] : null;
  var writeHeader = false;
  if (header && !nbIsOurHeader_(header)) {
    if (nbIsEmpty_(header)) writeHeader = true;
    else if (headerRow === top) {
      ui.alert(nbOccupied_());
      return;
    }
    // Otherwise the row above is data, ours or someone else's: left alone.
  }

  // The six columns to the right must be empty or hold our own earlier
  // results. Anything else is the customer's data, and is never overwritten.
  var block = sheet.getRange(top, col + 1, count, NB_COLUMNS.length);
  var existing = block.getValues();
  for (var r = headerRow === top ? 1 : 0; r < existing.length; r++) {
    if (!nbIsEmpty_(existing[r]) && !nbIsOurRow_(existing[r])) {
      ui.alert(nbOccupied_());
      return;
    }
  }

  var todo = [];
  for (var i = 0; i < phones.length; i++) {
    if (headerRow === top && i === 0) continue;
    if (nbLooksLikePhone_(phones[i]) && nbIsEmpty_(existing[i])) todo.push(i);
  }
  if (!todo.length) {
    ui.alert("Nothing to scrub: every selected number already has results, or none of the cells looks like a phone number.");
    return;
  }

  var confirm = ui.alert(
    "Scrub " + todo.length + (todo.length === 1 ? " number?" : " numbers?"),
    "This charges up to $" + (todo.length * NB_RATE).toFixed(2) + " from your NumberBroom API credits, $0.20 a number. " +
      "A number that is not a valid US number is answered free. Rows that already have results are skipped.",
    ui.ButtonSet.OK_CANCEL
  );
  if (confirm !== ui.Button.OK) return;

  if (writeHeader) {
    sheet.getRange(headerRow, col + 1, 1, NB_COLUMNS.length).setValues([NB_COLUMNS]);
    if (headerRow === top) existing[0] = NB_COLUMNS.slice(); // or the block write below would blank it
  }

  var started = Date.now();
  var done = 0;
  var charged = 0;
  var stop = null;
  for (var b = 0; b < todo.length && !stop; b += NB_BATCH) {
    if (Date.now() - started > NB_TIME_BUDGET_MS) {
      stop = "Stopped before Google's six-minute limit. Run it again to finish; rows with results are skipped.";
      break;
    }
    var batch = todo.slice(b, b + NB_BATCH);
    var requests = batch.map(function (idx) {
      var req = nbRequest_(key, "/verify", { phone: phones[idx] });
      var options = req.options;
      options.url = req.url;
      return options;
    });
    var responses = UrlFetchApp.fetchAll(requests);
    for (var k = 0; k < batch.length; k++) {
      var code = responses[k].getResponseCode();
      var body = nbBody_(responses[k]);
      if (code === 200) {
        existing[batch[k]] = nbResultRow_(body);
        done++;
        charged += Number(body.charged) || 0;
      } else if (code === 401 || code === 402 || code === 429) {
        stop = nbErrorText_(code, body);
      }
      // Anything else (a lookup that failed, which is not charged) stays
      // blank, so the next run tries that number again.
    }
    block.setValues(existing);
    SpreadsheetApp.flush();
  }

  var summary = "Scrubbed " + done + " of " + todo.length + (todo.length === 1 ? " number" : " numbers") +
    ", charged $" + charged.toFixed(2) + ".";
  ui.alert(stop ? summary + "\n\n" + stop : summary);
}

Building something bigger? The same lookup is available to any tool through the NumberBroom API, and GoHighLevel users can run it on every new contact with a GoHighLevel workflow.

Frequently asked questions

Cameron Hoffman

Founder, NumberBroom · 10 years in telecommunications and marketing

Cameron Hoffman is the founder of NumberBroom and has spent 10 years working in telecommunications and marketing. He built NumberBroom after repeatedly watching outbound teams dial purchased lists that were full of dead numbers, landlines and TCPA litigators.