Google Sheets Phone & Email Validation

/**
 * 1Lookup for Google Sheets
 * Checks phone numbers and emails right inside your spreadsheet.
 * Each check uses 1Lookup credits, the same as an API call.
 */

var BASE_URL = 'https://app.1lookup.io/api/v1';
var CACHE_SECONDS = 21600; // remember each answer for 6 hours, so a recalculation is not charged again

/** Adds a 1Lookup menu every time the sheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('1Lookup')
    .addItem('Set API key', 'setApiKey')
    .addItem('Check selected cells', 'checkSelection')
    .addItem('Show credits left', 'showCredits')
    .addToUi();
}

/** Saves your API key in the script's settings, never in the code. */
function setApiKey() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.prompt('1Lookup API key', 'Paste your key from app.1lookup.io (API Keys page):', ui.ButtonSet.OK_CANCEL);
  if (answer.getSelectedButton() !== ui.Button.OK) return;
  PropertiesService.getScriptProperties().setProperty('ONELOOKUP_API_KEY', answer.getResponseText().trim());
  ui.alert('Saved. Type =PHONE_LINE_TYPE(A2) or =EMAIL_STATUS(B2) in any cell.');
}

function apiKey_() {
  var key = PropertiesService.getScriptProperties().getProperty('ONELOOKUP_API_KEY');
  if (!key) throw new Error('Choose 1Lookup > Set API key first');
  return key;
}

/** Sends one check to 1Lookup and returns the "data" part of the answer. */
function lookup_(product, body) {
  var cache = CacheService.getScriptCache();
  var cacheKey = product + ':' + JSON.stringify(body);
  var saved = cache.get(cacheKey);
  if (saved) return JSON.parse(saved);

  var response = UrlFetchApp.fetch(BASE_URL + '/' + product, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey_() },
    payload: JSON.stringify(body),
    muteHttpExceptions: true
  });
  var json;
  try { json = JSON.parse(response.getContentText()); } catch (e) { json = {}; }
  if (!json.success) {
    // 401: wrong key. 402: out of credits. 429: too many checks at once, wait a minute.
    throw new Error(json.error && json.error.message ? json.error.message : 'HTTP ' + response.getResponseCode());
  }
  try { cache.put(cacheKey, JSON.stringify(json.data), CACHE_SECONDS); } catch (e) {}
  return json.data;
}

function checkPhone_(phone) {
  return lookup_('phone', { phone_number: String(phone).trim() });
}

function checkEmail_(email) {
  return lookup_('email', { email: String(email).trim() });
}

/** Runs fn on one cell, or on every cell of a range such as A2:A50. */
function eachCell_(input, fn) {
  if (Array.isArray(input)) {
    return input.map(function (row) { return [eachCell_(row[0], fn)]; });
  }
  if (input === '' || input === null || input === undefined) return '';
  try { return fn(input); } catch (e) { return 'Error: ' + e.message; }
}

/**
 * Line type of a phone number: MOBILE, LANDLINE, VOIP, TOLL_FREE or UNKNOWN.
 * @param {string} phone A phone number such as +1 415 555 0100, or a range like A2:A50
 * @return The line type
 * @customfunction
 */
function PHONE_LINE_TYPE(phone) {
  return eachCell_(phone, function (p) { return checkPhone_(p).classification.line_type; });
}

/**
 * Whether a phone number is in service: ACTIVE, INACTIVE, INVALID or UNKNOWN.
 * @param {string} phone A phone number, or a range like A2:A50
 * @return The number's status
 * @customfunction
 */
function PHONE_STATUS(phone) {
  return eachCell_(phone, function (p) { return checkPhone_(p).classification.number_status; });
}

/**
 * Deliverability of an email: DELIVERABLE, RISKY, UNDELIVERABLE, TEMPORARY or UNKNOWN.
 * @param {string} email An email address, or a range like B2:B50
 * @return The deliverability status
 * @customfunction
 */
function EMAIL_STATUS(email) {
  return eachCell_(email, function (e) { return checkEmail_(e).classification.deliverability_status; });
}

/**
 * Any field of the 1Lookup answer, for example "insights.location.timezone".
 * Anything with an @ is checked as an email, everything else as a phone number.
 * @param {string} value A phone number or an email
 * @param {string} field The field's path in the answer
 * @return The field's value
 * @customfunction
 */
function LOOKUP_FIELD(value, field) {
  return eachCell_(value, function (v) {
    var data = String(v).indexOf('@') > -1 ? checkEmail_(v) : checkPhone_(v);
    var out = String(field).split('.').reduce(function (obj, key) {
      return obj === null || obj === undefined ? obj : obj[key];
    }, data);
    if (out === null || out === undefined) return '';
    return typeof out === 'object' ? JSON.stringify(out) : out;
  });
}

/**
 * Menu: checks every phone number or email in the selected column and writes
 * the answers in the 3 columns to its right as plain values, so they are
 * never charged again. Rows that already have an answer are skipped: for a
 * big list, run it again and it picks up where it stopped.
 */
function checkSelection() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var values = range.getValues();
  var firstRow = range.getRow();
  var outCol = range.getColumn() + 1;
  var done = sheet.getRange(firstRow, outCol, values.length, 1).getValues();
  var started = Date.now();
  var checked = 0;

  for (var i = 0; i < values.length; i++) {
    var value = values[i][0];
    if (value === '' || done[i][0] !== '') continue; // empty, or already checked
    if (Date.now() - started > 270000) { // Google stops a run after 6 minutes
      SpreadsheetApp.getActive().toast(checked + ' checked. Run Check selected cells again to finish.', '1Lookup');
      return;
    }
    var result;
    try {
      if (String(value).indexOf('@') > -1) {
        var e = checkEmail_(value);
        result = [e.classification.deliverability_status, e.classification.email_type, e.recommendations.primary_action];
      } else {
        var p = checkPhone_(value);
        result = [p.classification.line_type, p.classification.number_status, p.recommendations.primary_action];
      }
    } catch (err) {
      result = ['Error: ' + err.message, '', ''];
    }
    sheet.getRange(firstRow + i, outCol, 1, 3).setValues([result]);
    checked++;
    if (/credits/i.test(result[0])) break; // out of credits: stop here
  }
  SpreadsheetApp.getActive().toast(checked + ' checked.', '1Lookup');
}

/** Menu: shows how many credits are left. Free: reading your account is not a check. */
function showCredits() {
  var response = UrlFetchApp.fetch(BASE_URL + '/account', {
    headers: { Authorization: 'Bearer ' + apiKey_() },
    muteHttpExceptions: true
  });
  var json;
  try { json = JSON.parse(response.getContentText()); } catch (e) { json = {}; }
  SpreadsheetApp.getUi().alert(json.success
    ? 'Credits left: ' + json.data.tokens.total_available
    : 'Could not read your account (HTTP ' + response.getResponseCode() + ')');
}