End of Year Clean Ups (a.k.a. too many Google Contacts)

I’ve been on Gmail since it launched back in 2004 and thanks to Google automatically collecting email addresses into Google Contacts, I have over 8,000 contacts in that service. Trouble is, 8,000 entries in my iPhone’s Address Book makes it next to unusable, as very few people I ever email show up.

I searched the internet for a tool that would clean up by Google Contacts, deleting all the entries that I’ve not heard from or emailed in the last decade. No such tool seems to exist.

I asked the amazing SaneBox.com if they had such a feature in their pipeline, as their service knows who I frequently email, and who send me email. Their service is as good as their product and a day later their answer was back. Unfortunately, it was no.

But they did send me a spreadsheet with all the names, email addresses, and last email date from my Gmail account, and with that I scrubbed the rust off my programming skills and turned to Google Apps Scripts.

function myFunction() {
  // Scripts can only run for five minutes, so stop after 4.5
  var start = new Date().getTime();
  Logger.log("start time = " + start);

  // This script is attached to a Google (Spread)Sheet with columns: Name, Email, Status
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Double check at column B is labeled "Email"
  if ((sheet.getRange("B1").getValue() != "EMAIL") &&
      (sheet.getRange("B1").getValue() != "email") &&
      (sheet.getRange("B1").getValue() != "Email")) {
    Logger.log("Email addresses need to be in column B");
    SpreadsheetApp.getUi().alert("Email addresses need to be in column B");
    return;
  }

  // This grabs all the values in the spreadsheet
  var range = sheet.getDataRange();
  var values = range.getValues();
  var firstRow = false;

  // Iterate over each email (column B=1 and skip header row)
  var previousName = "";
  for (var i = 1; i < values.length; i++) {
    // Stop after 4.5 minutes
    var now = new Date().getTime();
    if ((now - start) > ((4*60*1000)+500)) {
      Logger.log("run time = " + (now-start));
      return;
    }

    // Skip all the rows with a value in column C=2
    var status = values[i][2];
    if (status != "") continue;
    else if (firstRow == false) {
      Logger.log("starting at row " + i);
      firstRow = true;
    }

    // Sanebox's spreadsheet has one row per contact,
    // with consecutive repeat names when there is more than one email address
    var name = values[i][0];
    if (name == previousName) {
       sheet.getRange(i+1,3).setValue("REPEAT");
       Logger.log("repeated contact");
    } else {
      var email = values[i][1];
      var contact = ContactsApp.getContact(email);
      if (contact == null) {
        sheet.getRange(i+1,3).setValue("NOT FOUND");
        Logger.log(email + " not found");
      }
      else {
        contact.deleteContact();
        sheet.getRange(i+1,3).setValue("DELETED");
        Logger.log(email + " deleted");
      }

      previousName = name;
    }
  }
}

1. Create a Google Sheet with the columns: Name, Email, Status, filling in the names and email addresses of the contacts you want to delete, leaving the Status column blank. (Delete the code that checks previousName if you want to just delete every email address without checking for consecutive duplicates.)

2. Choose the Extensions: Apps Scripts menu item

3. Run the script

4. Tell Google to ignore the security issues of running a script

Accessing Contacts is slow. I’m sure that is on purpose. Trouble is, these Apps Scripts are only allowed to run for six minutes. You can thus only delete around 30 entries every time you run the script. I have over 5,000 old email addresses to delete.

5. To get the script to run automatically, create a trigger that runs every 5 minutes.

This is the first Google Apps Script script I’ve written, but it took less than an hour to find the right objects and methods to use. The language is basically Javascript, which is basically C, and I learned that in 1987.

A few hours later… over 800 of my old email addresses are now out of my Google Contacts and by the time I wake up tomorrow all 5,000 ancient email addresses should be gone.

Thank you Google for this most excellent platform and for the full set of APIs to make this possible. My iPhone will thank you tomorrow.