GDoc Sheet Christmas Card List

gistfile1.js

var ss = SpreadsheetApp.getActiveSpreadsheet();

/**
 * Runs at script installation.
 */
function onInstall() {
  onOpen();
}

/**
 * Runs at spreadsheet file opening.
 */
function onOpen() {
  // Build spreadsheet custom menu entries
  var menuEntries = [
    {name: "Sync contacts", functionName: "syncContacts"},
    {name: "Configure", functionName: "configureUI"}
  ];
  ss.addMenu("Contacts", menuEntries);
}

/**
 * Syncs contacts from Google Contacts.
 */
function syncContacts() {
  if(isConfigured() == false) {
    Browser.msgBox("Please configure the application");
  } else {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var contactsSheet = ss.getSheetByName("Contacts");
    // Clear the sheet (keeping sheet header)
    //contactsSheet.getDataRange().offset(1,0).clear();
    var properties = ScriptProperties.getProperty('syncSetup');
    properties = Utilities.jsonParse(properties);
    var myContacts = ContactsApp.getContactsByGroup(ContactsApp.getContactGroup(properties.contactgroups));
    for(var i = 0; i < myContacts.length; i++) {
      var contactId = myContacts[i].getId();
      var contactName = myContacts[i].getGivenName();
      var contactSurname = myContacts[i].getFamilyName();
      var contactAddressesArray = myContacts[i].getAddresses(ContactsApp.Field.HOME_ADDRESS);
      var contactAddresses = getAddresses(contactAddressesArray);
      // Check if the contact has already been inserted
      var existingContactIndex = contactExists(contactId);
      if(existingContactIndex === false) {
        // Write contacts with at least a name, surname or full name
        if(contactName != "" || contactSurname != "" || contactFullName != "") {
          contactsSheet.appendRow([contactId, contactSurname, contactName, contactAddresses]);
        }
      } else {
        // Do the update
        updateContact(contactId, contactSurname, contactName, contactAddresses);
        // Sync the Date Last Contacted back to Google Contacts
        if(myContacts[i].getCustomFields("Date Last Contacted").length == 0) {
          myContacts[i].addCustomField("Date Last Contacted", getContactLastContactedDate(existingContactIndex));
        } else {
          // Custom field already exists
          var customFields = myContacts[i].getCustomFields("Date Last Contacted");
          for(var j = 0; j < customFields.length; j++) {
            customFields[j].setValue(getContactLastContactedDate(existingContactIndex));
          }
        }
      }       
    }  
  }
}

/**
 * Returns a string representing addresses, newline separated.
 */
function getAddresses(contactAddressArray) {
  var contactAddresses = "";
  var numAddresses = contactAddressArray.length;
  for(var j = 0; j<contactAddressArray.length; j++) {
    var contactAddress = contactAddressArray[j].getAddress();
    if(j == numAddresses -1) {
      contactAddresses += contactAddress;
    } else {
      contactAddresses += contactAddress + "\r";
    }
  }

  return contactAddresses;
}

/**
 * Configuration interface builder.
 */
function configureUI() {
  var app = UiApp.createApplication().setTitle('Configure').setWidth(510).setHeight(370);
  var main_panel = app.createVerticalPanel().setStyleAttribute('border', '1px solid #C0C0C0');
  var settings_panel = app.createVerticalPanel().setPixelSize(500, 290).setId('settings_panel');
  var grid = app.createGrid(1, 2).setCellSpacing(20);
  settings_panel.add(grid);

  var button_panel = app.createHorizontalPanel().setWidth(500);
  var button_align = app.createHorizontalPanel().setSpacing(10);
  var close = app.createButton('Close', app.createServerHandler('closeApp_')).setWidth(100);
  button_panel.add(button_align);
  button_panel.setCellHorizontalAlignment(button_align, UiApp.HorizontalAlignment.CENTER);
  settings_panel.add(button_panel);
  main_panel.add(settings_panel);
  app.add(main_panel);

  var properties = ScriptProperties.getProperty('syncSetup');
  var user = Session.getEffectiveUser().getEmail();

  if (properties != null){
    properties = Utilities.jsonParse(properties);
  }

  grid.setWidget(0, 0, app.createLabel('Select contacts group to sync:'));
      
  var contactsGroupsList = app.createListBox().setName('contactgroups');
  var groups = ContactsApp.getContactGroups();
  for (var i in groups) {
    var groupName = groups[i].getGroupName();
    var item = contactsGroupsList.addItem(groupName);
    if (properties != null && groupName == properties.contactgroups) {
      contactsGroupsList.setSelectedIndex(Number(i));
    }         
  }
  grid.setWidget(0, 1, contactsGroupsList);

  var record = app.createButton("Save", app.createServerHandler('createProperties_').addCallbackElement(grid)).setEnabled(true);
  button_align.add(record.setWidth(100));
  button_align.add(close);
  record.addClickHandler(app.createClientHandler().forEventSource().setEnabled(true).forTargets(close).setEnabled(true));

  ss.show(app);  
}

/**
 * Closes the running application.
 */
function closeApp_() {
  var app = UiApp.getActiveApplication();
  app.close();

  return app;
}

/**
 * Saves the user selected properties and creates the trigger.
 *
 * @param {object} e the event object
 * @return {UiApp} the application instance
 */
function createProperties_(e) {
  var app = UiApp.getActiveApplication();

  ScriptProperties.setProperty('syncSetup', Utilities.jsonStringify(e.parameter));
  var panel = app.getElementById('settings_panel').clear();
  var info = app.createLabel('Configuration saved');
  panel.add(info).setCellHorizontalAlignment(info, UiApp.HorizontalAlignment.CENTER).setStyleAttribute('paddingTop', '50px');
  var button = app.createButton('Close', app.createServerHandler('closeApp_')).setWidth(100);
  panel.add(button).setCellHorizontalAlignment(button, UiApp.HorizontalAlignment.CENTER);

  return app;
}

/**
 * Checks if the application has been configured.
 *
 * @return {boolean} true if the application has been configured, false otherwise
 */
function isConfigured() {
  var properties = ScriptProperties.getProperty('syncSetup');
  if (properties == null) {
    return false;
  } else {
    return true;
  }
}

/**
 * Checks if the specified contact already exists in the sheet.
 *
 * @param {string} contactId ID of the contact
 * @return {integer} index of the contact in the sheet, false otherwise 
 */
function contactExists(contactId) {
  var data = ss.getActiveSheet().getDataRange().getValues();
  for(var i = 0; i < data.length; i++) {
    if(data[i][0] == contactId) {
      return i;
    }
  }

  return false;
}

/**
 * Updates the specified contact.
 *
 * @param {string} contactId ID of the contact
 * @param {string} contactSurname contact surname
 * @param {string} contactName contact name 
 * @param {string} contactAddresses contact addresses 
 * @return {boolean} operation result
 */
function updateContact(contactId, contactSurname, contactName, contactAddresses) {
  var contactIndex = contactExists(contactId);
  var contactRange = ss.getActiveSheet().getRange(contactIndex+1, 1, 1, 4);
  // Update the entry
  contactRange.setValues([[contactId, contactSurname, contactName, contactAddresses]]);

  return true;
}

/**
 * Gets the contact last contacted date.
 *
 * @param {integer} contactIndex the contact row
 * @return {string} the last contacted date value for the contact
 */
function getContactLastContactedDate(contactIndex) {
  var contactRange = ss.getActiveSheet().getRange(contactIndex+1, 7);

  return contactRange.getValue();
}