Clean a contact list and import it into Google Contacts

/**
 * Contact Cleaner for Google Contacts
 * Cleans a contact list in this spreadsheet, saves a Google Contacts CSV,
 * imports it into your Google Contacts, then checks what arrived.
 *
 * Setup: Services (+) > People API > Add. Then reload the spreadsheet
 * and use the "Contact Cleaner" menu.
 * Guide: https://www.makeinfo.co/resources/clean-import-google-contacts
 */
const CONFIG = {
  sheetName: "Contacts",   // tab that holds your raw list
  nameCase: "title",          // 'title' fixes ALL CAPS / all lower names, 'keep' leaves them
  defaultCountryCode: "1",  // added to numbers written without one
  phoneFormat: "e164",        // 'e164' = +14155550123, 'keep' = as typed
  dedupeBy: "email_or_phone",   // 'email' | 'phone' | 'email_or_phone' | 'name_phone' | 'none'
  mergeDuplicates: true,     // copy missing details from a duplicate into the row that is kept
  keepIf: "name_and_contact",  // 'name_and_contact' | 'email_or_phone' | 'email'
  invalidEmail: "blank",      // 'blank' keeps the contact without it, 'drop' removes the row
  removeJunk: true,          // clears "n/a", "test", noreply@ and example.com values
  label: "Imported contacts",  // Google Contacts label for everything imported ('' for none)
  skipExisting: true,        // don't import people whose email or phone is already in your contacts
};

// Column names the script recognises in your contact tab (case and spacing ignored).
const HEADER_ALIASES = {
  fullName: ['name', 'full name', 'contact', 'contact name', 'display name'],
  firstName: ['first name', 'first', 'firstname', 'given name', 'forename'],
  lastName: ['last name', 'last', 'lastname', 'surname', 'family name'],
  email: ['email', 'e-mail', 'e mail', 'email address', 'e-mail address', 'mail', 'e-mail 1 - value'],
  phone: ['phone', 'phone number', 'mobile', 'mobile phone', 'cell', 'telephone', 'tel', 'phone 1 - value'],
  company: ['company', 'organization', 'organisation', 'organization name', 'business', 'employer'],
  jobTitle: ['job title', 'title', 'position', 'role', 'organization title'],
  notes: ['notes', 'note', 'comments', 'comment'],
};

const CLEAN_TAB = 'Cleaned';
const REMOVED_TAB = 'Removed';
const CHECK_TAB = 'Import check';
const CLEAN_HEADERS = ['First Name', 'Last Name', 'Email', 'Email 2', 'Phone', 'Phone 2',
  'Company', 'Job Title', 'Notes', 'Source Row', 'Status', 'Contact ID'];
const COL = {};
CLEAN_HEADERS.forEach(function (h, i) { COL[h] = i; });

const JUNK_VALUES = ['n/a', 'na', 'none', 'null', 'unknown', 'test', '-', '--', '.', '?', 'x', 'tbd', 'no name'];
const JUNK_EMAIL = /^(no-?reply|do-?not-?reply|test)@|@(example|test)\.(com|org|net)$/i;
const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[a-z]{2,}$/i;
const PREFIXES = /^(mr|mrs|ms|miss|mx|dr|prof)\.?\s+/i;

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Contact Cleaner')
    .addItem('1. Clean & review', 'cleanContacts')
    .addItem('2. Save Google Contacts CSV', 'exportCsv')
    .addItem('3. Import into Google Contacts', 'importContacts')
    .addItem('4. Check the import', 'verifyImport')
    .addToUi();
}

// ---------------------------------------------------------------- 1. Clean

function cleanContacts() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(CONFIG.sheetName);
  if (!sheet) throw new Error('No tab called "' + CONFIG.sheetName + '". Rename your contact tab or change CONFIG.sheetName.');

  const values = sheet.getDataRange().getDisplayValues();
  if (values.length < 2) throw new Error('The contact tab needs a header row and at least one contact.');
  const cols = mapHeaders_(values[0]);
  if (cols.email < 0 && cols.phone < 0) {
    throw new Error('Could not find an email or phone column. Headers found: ' + values[0].join(', '));
  }

  const kept = [];
  const removed = [];
  const seen = {}; // dedupe key -> index in kept

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    if (row.every(function (v) { return String(v).trim() === ''; })) continue;

    const c = normalizeRow_(row, cols);
    c.sourceRow = i + 1;

    const problem = unusableReason_(c);
    if (problem) {
      removed.push([c.sourceRow, problem].concat(row));
      continue;
    }

    const keys = dedupeKeys_(c);
    const hit = keys.filter(function (k) { return k in seen; })[0];
    if (hit) {
      const original = kept[seen[hit]];
      if (CONFIG.mergeDuplicates) mergeInto_(original, c);
      keys.forEach(function (k) { if (!(k in seen)) seen[k] = seen[hit]; });
      removed.push([c.sourceRow, 'Duplicate of row ' + original.sourceRow + (CONFIG.mergeDuplicates ? ' (details merged)' : '')].concat(row));
      continue;
    }
    keys.forEach(function (k) { seen[k] = kept.length; });
    kept.push(c);
  }

  writeTab_(CLEAN_TAB, CLEAN_HEADERS, kept.map(function (c) {
    return [c.first, c.last, c.emails[0] || '', c.emails[1] || '', c.phones[0] || '', c.phones[1] || '',
      c.company, c.jobTitle, c.notes.join('\n'), c.sourceRow, 'Ready', ''];
  }));
  const removedHeaders = ['Source Row', 'Reason'].concat(values[0]);
  writeTab_(REMOVED_TAB, removedHeaders, removed.map(function (r) {
    while (r.length < removedHeaders.length) r.push('');
    return r.slice(0, removedHeaders.length);
  }));

  ss.getSheetByName(CLEAN_TAB).activate();
  SpreadsheetApp.getUi().alert(
    'Kept ' + kept.length + ' contacts, removed ' + removed.length + '.\n\n' +
    'Check the "' + CLEAN_TAB + '" tab and fix anything by hand. The "' + REMOVED_TAB + '" tab says why each row was dropped.\n\n' +
    'Nothing has been added to Google Contacts yet.');
}

function mapHeaders_(headerRow) {
  const normalized = headerRow.map(function (h) {
    return String(h).toLowerCase().replace(/[_\s]+/g, ' ').trim();
  });
  const cols = {};
  Object.keys(HEADER_ALIASES).forEach(function (field) {
    cols[field] = -1;
    HEADER_ALIASES[field].some(function (alias) {
      const idx = normalized.indexOf(alias);
      if (idx >= 0) cols[field] = idx;
      return idx >= 0;
    });
  });
  return cols;
}

function normalizeRow_(row, cols) {
  const get = function (field) { return cols[field] >= 0 ? cleanText_(row[cols[field]]) : ''; };
  const c = { emails: [], phones: [], notes: [] };

  let first = get('firstName');
  let last = get('lastName');
  if (!first && !last && get('fullName')) {
    const split = splitName_(get('fullName'));
    first = split.first;
    last = split.last;
  }
  c.first = formatName_(first.replace(PREFIXES, ''));
  c.last = formatName_(last);
  c.company = get('company');
  c.jobTitle = get('jobTitle');
  if (get('notes')) c.notes.push(get('notes'));

  // A cell may hold several emails or phones separated by ; , / or spaces
  const emailParts = [];
  get('email').split(/[;,\/]+/).forEach(function (part) {
    const words = part.trim().split(' ');
    const allEmails = words.every(function (w) { return w.indexOf('@') >= 0; });
    // "[email protected] [email protected]" is two emails; "jane @ gmail.com" is one email with stray spaces
    if (allEmails) words.forEach(function (w) { emailParts.push([w, w]); });
    else emailParts.push([words.join(''), part.trim()]);
  });
  emailParts.forEach(function (pair) {
    const raw = pair[1];
    const email = pair[0].toLowerCase().replace(/^mailto:/, '').replace(/^[<(]|[>).]$/g, '');
    if (!email) return;
    if (CONFIG.removeJunk && JUNK_EMAIL.test(email)) return;
    if (EMAIL_RE.test(email)) addUnique_(c.emails, email);
    else c.badEmail = c.badEmail || raw;
  });
  if (c.badEmail && CONFIG.invalidEmail === 'blank') c.notes.push('Unreadable email in source: ' + c.badEmail);

  get('phone').split(/[;,\/]+/).forEach(function (raw) {
    if (!raw.trim()) return;
    const phone = formatPhone_(raw);
    if (phone) addUnique_(c.phones, phone);
    else c.notes.push('Unreadable phone in source: ' + raw.trim());
  });

  // Google Contacts gets two of each here; anything beyond that is kept in Notes
  c.emails.splice(2).forEach(function (e) { c.notes.push('Other email: ' + e); });
  c.phones.splice(2).forEach(function (p) { c.notes.push('Other phone: ' + p); });
  return c;
}

function cleanText_(value) {
  const s = String(value).replace(/[​-‍]/g, '').replace(/\s+/g, ' ').trim();
  if (CONFIG.removeJunk && JUNK_VALUES.indexOf(s.toLowerCase()) >= 0) return '';
  return s;
}

function splitName_(full) {
  // "Smith, Jane" -> Jane / Smith
  if (full.indexOf(',') > 0) {
    const parts = full.split(',');
    return { first: parts.slice(1).join(' ').trim(), last: parts[0].trim() };
  }
  const words = full.replace(PREFIXES, '').split(' ');
  return { first: words[0], last: words.slice(1).join(' ') };
}

function formatName_(name) {
  if (!name || CONFIG.nameCase === 'keep') return name;
  // Only touch names typed in ALL CAPS or all lower case, so "McKenzie" and "DeShawn" survive
  if (name !== name.toUpperCase() && name !== name.toLowerCase()) return name;
  return name.toLowerCase()
    .replace(/(^|[\s\-'’])([a-zÀ-ɏ])/g, function (m, sep, ch) { return sep + ch.toUpperCase(); })
    .replace(/\bMc([a-z])/g, function (m, ch) { return 'Mc' + ch.toUpperCase(); });
}

function formatPhone_(raw) {
  const text = String(raw).trim();
  if (CONFIG.phoneFormat === 'keep') return text;

  const m = text.match(/^(.*?)(?:\s*(?:ext\.?|extension|x|#)\s*(\d{1,6}))?$/i);
  const main = m[1];
  const ext = m[2];
  let digits = main.replace(/\D/g, '');
  const international = /^\s*\+/.test(main) || /^\s*00/.test(main);

  if (/^\s*00/.test(main)) digits = digits.slice(2);
  if (!international) {
    const cc = CONFIG.defaultCountryCode;
    if (cc === '1' && digits.length === 11 && digits.charAt(0) === '1') {
      // US/Canada number already written with its leading 1
    } else {
      digits = cc + digits.replace(/^0+/, ''); // drop the national trunk 0 (UK, IN, AU, DE...)
    }
  }
  if (digits.length < 8 || digits.length > 15) return '';
  return '+' + digits + (ext ? ' ext. ' + ext : '');
}

function unusableReason_(c) {
  if (c.badEmail && CONFIG.invalidEmail === 'drop') return 'Invalid email: ' + c.badEmail;
  const hasName = c.first || c.last || c.company;
  const hasEmail = c.emails.length > 0;
  const hasPhone = c.phones.length > 0;
  if (CONFIG.keepIf === 'email' && !hasEmail) return 'No valid email';
  if (CONFIG.keepIf === 'email_or_phone' && !hasEmail && !hasPhone) return 'No valid email or phone';
  if (CONFIG.keepIf === 'name_and_contact') {
    if (!hasName) return 'No name or company';
    if (!hasEmail && !hasPhone) return 'No valid email or phone';
  }
  return '';
}

function phoneKey_(phone) {
  return String(phone).split(/ext/i)[0].replace(/\D/g, '').slice(-10);
}

function dedupeKeys_(c) {
  const by = CONFIG.dedupeBy;
  const keys = [];
  if (by === 'email' || by === 'email_or_phone') {
    c.emails.forEach(function (e) { keys.push('e:' + e); });
  }
  if (by === 'phone' || by === 'email_or_phone') {
    c.phones.forEach(function (p) { keys.push('p:' + phoneKey_(p)); });
  }
  if (by === 'name_phone' && (c.first || c.last)) {
    const name = (c.first + ' ' + c.last).toLowerCase().trim();
    c.phones.forEach(function (p) { keys.push('np:' + name + '|' + phoneKey_(p)); });
  }
  return keys;
}

function mergeInto_(a, b) {
  ['first', 'last', 'company', 'jobTitle'].forEach(function (f) { if (!a[f] && b[f]) a[f] = b[f]; });
  b.emails.forEach(function (e) {
    if (a.emails.indexOf(e) >= 0) return;
    if (a.emails.length < 2) a.emails.push(e);
    else addUnique_(a.notes, 'Other email: ' + e);
  });
  b.phones.forEach(function (p) {
    if (a.phones.some(function (x) { return phoneKey_(x) === phoneKey_(p); })) return;
    if (a.phones.length < 2) a.phones.push(p);
    else addUnique_(a.notes, 'Other phone: ' + p);
  });
  b.notes.forEach(function (n) { addUnique_(a.notes, n); });
}

function addUnique_(list, value) {
  if (list.indexOf(value) < 0) list.push(value);
}

function writeTab_(name, headers, rows) {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  sheet.clear();
  const all = [headers].concat(rows);
  const range = sheet.getRange(1, 1, all.length, headers.length);
  range.setNumberFormat('@'); // plain text, so +14155550123 stays a phone number and not a formula
  range.setValues(all);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  sheet.setFrozenRows(1);
  return sheet;
}

// Reads the Cleaned tab back, including any fixes you made by hand
function readCleaned_() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CLEAN_TAB);
  if (!sheet) throw new Error('Run "1. Clean & review" first.');
  const values = sheet.getDataRange().getDisplayValues();
  return values.slice(1).map(function (r, i) {
    return {
      row: i + 2,
      first: r[COL['First Name']].trim(),
      last: r[COL['Last Name']].trim(),
      emails: [r[COL['Email']], r[COL['Email 2']]].map(String).map(function (s) { return s.trim(); }).filter(String),
      phones: [r[COL['Phone']], r[COL['Phone 2']]].map(String).map(function (s) { return s.trim(); }).filter(String),
      company: r[COL['Company']].trim(),
      jobTitle: r[COL['Job Title']].trim(),
      notes: r[COL['Notes']].trim(),
      status: r[COL['Status']],
      contactId: r[COL['Contact ID']],
    };
  }).filter(function (c) {
    return c.first || c.last || c.company || c.emails.length || c.phones.length;
  });
}

// ---------------------------------------------------------------- 2. CSV

function exportCsv() {
  const contacts = readCleaned_();
  const headers = ['First Name', 'Last Name', 'Organization Name', 'Organization Title', 'Notes', 'Labels',
    'E-mail 1 - Value', 'E-mail 2 - Value', 'Phone 1 - Value', 'Phone 2 - Value'];
  const labels = CONFIG.label ? CONFIG.label + ' ::: * myContacts' : '* myContacts';
  const lines = [headers].concat(contacts.map(function (c) {
    return [c.first, c.last, c.company, c.jobTitle, c.notes, labels,
      c.emails[0] || '', c.emails[1] || '', c.phones[0] || '', c.phones[1] || ''];
  })).map(function (fields) {
    return fields.map(function (f) { return '"' + String(f).replace(/"/g, '""') + '"'; }).join(',');
  });

  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HHmm');
  const file = DriveApp.createFile('Google Contacts import ' + stamp + '.csv', lines.join('\r\n'), MimeType.CSV);
  showLink_('CSV saved to your Google Drive',
    contacts.length + ' contacts, in the column layout Google Contacts imports.', file.getUrl(), 'Open the CSV');
}

// ---------------------------------------------------------------- 3. Import

function importContacts() {
  requirePeopleApi_();
  const ui = SpreadsheetApp.getUi();
  const contacts = readCleaned_().filter(function (c) { return c.status !== 'Imported'; });
  if (!contacts.length) {
    ui.alert('Nothing left to import. Every row on the "' + CLEAN_TAB + '" tab is already marked Imported.');
    return;
  }
  const answer = ui.alert('Import ' + contacts.length + ' contacts?',
    'They will be added to the Google Contacts of ' + Session.getActiveUser().getEmail() +
    (CONFIG.label ? ' with the label "' + CONFIG.label + '"' : '') + '.', ui.ButtonSet.OK_CANCEL);
  if (answer !== ui.Button.OK) return;

  const existing = CONFIG.skipExisting ? existingContactKeys_() : {};
  const sheet = SpreadsheetApp.getActive().getSheetByName(CLEAN_TAB);
  const groupName = CONFIG.label ? findOrCreateGroup_(CONFIG.label) : '';
  const toCreate = [];
  let skipped = 0;

  contacts.forEach(function (c) {
    const already = c.emails.some(function (e) { return existing['e:' + e.toLowerCase()]; }) ||
      c.phones.some(function (p) { return existing['p:' + phoneKey_(p)]; });
    if (already) {
      setStatus_(sheet, c.row, 'Skipped: already in Google Contacts', '');
      skipped++;
    } else {
      toCreate.push(c);
    }
  });

  let created = 0;
  let failed = 0;
  for (let i = 0; i < toCreate.length; i += 200) {
    const chunk = toCreate.slice(i, i + 200);
    try {
      const res = withRetry_(function () {
        return People.People.batchCreateContacts({
          contacts: chunk.map(function (c) { return { contactPerson: toPerson_(c) }; }),
          readMask: 'names,emailAddresses,phoneNumbers',
        });
      });
      const ids = matchCreated_(chunk, res.createdPeople || []);
      chunk.forEach(function (c, j) {
        if (ids[j]) { setStatus_(sheet, c.row, 'Imported', ids[j]); created++; }
        else { setStatus_(sheet, c.row, 'Failed: not returned by Google', ''); failed++; }
      });
      if (groupName) {
        const added = ids.filter(String);
        if (added.length) {
          withRetry_(function () {
            return People.ContactGroups.Members.modify({ resourceNamesToAdd: added }, groupName);
          });
        }
      }
    } catch (err) {
      chunk.forEach(function (c) { setStatus_(sheet, c.row, 'Failed: ' + err.message, ''); });
      failed += chunk.length;
    }
    SpreadsheetApp.flush(); // progress is saved row by row, so a re-run picks up where this stopped
    Utilities.sleep(1500);
  }

  ui.alert('Import finished',
    created + ' added, ' + skipped + ' skipped (already in your contacts), ' + failed + ' failed.\n\n' +
    'Run "4. Check the import" to confirm every field arrived.', ui.ButtonSet.OK);
}

function toPerson_(c) {
  const p = {};
  if (c.first || c.last) p.names = [{ givenName: c.first, familyName: c.last }];
  if (c.emails.length) p.emailAddresses = c.emails.map(function (v) { return { value: v }; });
  if (c.phones.length) p.phoneNumbers = c.phones.map(function (v) { return { value: v }; });
  if (c.company || c.jobTitle) p.organizations = [{ name: c.company, title: c.jobTitle }];
  if (c.notes) p.biographies = [{ value: c.notes, contentType: 'TEXT_PLAIN' }];
  return p;
}

function fingerprint_(emails, phones, first, last) {
  if (emails.length) return 'e:' + emails[0].toLowerCase();
  if (phones.length) return 'p:' + phoneKey_(phones[0]);
  return 'n:' + (first + ' ' + last).toLowerCase().trim();
}

// Pairs each created person with the sheet row it came from
function matchCreated_(chunk, createdPeople) {
  const queue = {};
  chunk.forEach(function (c, j) {
    const fp = fingerprint_(c.emails, c.phones, c.first, c.last);
    (queue[fp] = queue[fp] || []).push(j);
  });
  const ids = chunk.map(function () { return ''; });
  createdPeople.forEach(function (resp, k) {
    const person = resp.person;
    if (!person || !person.resourceName) return;
    const name = (person.names || [])[0] || {};
    const fp = fingerprint_(
      (person.emailAddresses || []).map(function (e) { return e.value; }),
      (person.phoneNumbers || []).map(function (p) { return p.value; }),
      name.givenName || '', name.familyName || '');
    const j = queue[fp] && queue[fp].length ? queue[fp].shift() : k;
    if (j < ids.length && !ids[j]) ids[j] = person.resourceName;
  });
  return ids;
}

function existingContactKeys_() {
  const keys = {};
  let pageToken;
  do {
    const res = withRetry_(function () {
      return People.People.Connections.list('people/me', {
        personFields: 'emailAddresses,phoneNumbers', pageSize: 1000, pageToken: pageToken,
      });
    });
    (res.connections || []).forEach(function (p) {
      (p.emailAddresses || []).forEach(function (e) { keys['e:' + String(e.value).toLowerCase()] = true; });
      (p.phoneNumbers || []).forEach(function (ph) { keys['p:' + phoneKey_(ph.value)] = true; });
    });
    pageToken = res.nextPageToken;
  } while (pageToken);
  return keys;
}

function findOrCreateGroup_(label) {
  let pageToken;
  do {
    const res = People.ContactGroups.list({ pageSize: 1000, pageToken: pageToken });
    const match = (res.contactGroups || []).filter(function (g) {
      return g.groupType === 'USER_CONTACT_GROUP' && (g.name === label || g.formattedName === label);
    })[0];
    if (match) return match.resourceName;
    pageToken = res.nextPageToken;
  } while (pageToken);
  return People.ContactGroups.create({ contactGroup: { name: label } }).resourceName;
}

function setStatus_(sheet, row, status, contactId) {
  sheet.getRange(row, COL['Status'] + 1, 1, 2).setValues([[status, contactId]]);
}

// ---------------------------------------------------------------- 4. Verify

function verifyImport() {
  requirePeopleApi_();
  const rows = readCleaned_().filter(function (c) { return c.status === 'Imported' && c.contactId; });
  if (!rows.length) {
    SpreadsheetApp.getUi().alert('No imported rows to check yet. Run "3. Import into Google Contacts" first.');
    return;
  }

  const out = [];
  let ok = 0;
  for (let i = 0; i < rows.length; i += 200) {
    const chunk = rows.slice(i, i + 200);
    const res = withRetry_(function () {
      return People.People.getBatchGet({
        resourceNames: chunk.map(function (c) { return c.contactId; }),
        personFields: 'names,emailAddresses,phoneNumbers,organizations',
      });
    });
    const byId = {};
    (res.responses || []).forEach(function (r) { byId[r.requestedResourceName] = r.person; });

    chunk.forEach(function (c) {
      const problems = comparePerson_(c, byId[c.contactId]);
      if (!problems.length) ok++;
      out.push([c.row, (c.first + ' ' + c.last).trim() || c.company, c.contactId,
        problems.length ? problems.join('; ') : 'OK']);
    });
  }

  writeTab_(CHECK_TAB, ['Cleaned Row', 'Name', 'Contact ID', 'Result'], out).activate();
  SpreadsheetApp.getUi().alert(ok + ' of ' + rows.length + ' imported contacts match the sheet exactly.' +
    (ok < rows.length ? '\n\nThe "' + CHECK_TAB + '" tab lists what differs.' : ''));
}

function comparePerson_(c, person) {
  if (!person) return ['Not found in Google Contacts'];
  const problems = [];
  const name = (person.names || [])[0] || {};
  if ((name.givenName || '') !== c.first || (name.familyName || '') !== c.last) problems.push('Name differs');
  const emails = (person.emailAddresses || []).map(function (e) { return String(e.value).toLowerCase(); });
  c.emails.forEach(function (e) { if (emails.indexOf(e.toLowerCase()) < 0) problems.push('Missing email ' + e); });
  const phones = (person.phoneNumbers || []).map(function (p) { return phoneKey_(p.value); });
  c.phones.forEach(function (p) { if (phones.indexOf(phoneKey_(p)) < 0) problems.push('Missing phone ' + p); });
  const org = (person.organizations || [])[0] || {};
  if (c.company && org.name !== c.company) problems.push('Company differs');
  return problems;
}

// ---------------------------------------------------------------- Helpers

function requirePeopleApi_() {
  if (typeof People === 'undefined') {
    throw new Error('Turn on the People API first: in the Apps Script editor, click + next to Services, pick "Peopleapi", then Add.');
  }
}

function withRetry_(fn) {
  for (let attempt = 0; ; attempt++) {
    try {
      return fn();
    } catch (err) {
      const retryable = /429|quota|rate|backend|503|500/i.test(err.message);
      if (!retryable || attempt >= 4) throw err;
      Utilities.sleep(Math.pow(2, attempt) * 2000);
    }
  }
}

function showLink_(title, message, url, linkText) {
  const html = HtmlService.createHtmlOutput(
    '<div style="font-family:Arial,sans-serif;font-size:14px;line-height:1.5">' +
    '<p>' + message + '</p>' +
    '<p><a href="' + url + '" target="_blank" style="color:#0F766E;font-weight:bold">' + linkText + '</a></p>' +
    '<p style="color:#57534E">To import it by hand instead of running step 3: contacts.google.com → Import → pick this file.</p>' +
    '</div>').setWidth(420).setHeight(190);
  SpreadsheetApp.getUi().showModalDialog(html, title);
}