How to Create a Contact Group with Google Sheets

Simplify your process of managing permissions

Photo by Viktor Talashuk on Unsplash

One of the most critical parts of collaborative work is ensuring that you collaborate with the right people. If you are working in a group where the stakeholders are constantly changing and you are just giving people folder permissions ad-hoc before you know it you have a permissions nightmare. 

I work with a few organizations where every year or so there is a new batch of people joining the team so they need to get access to all the correct folders to do their job and the people who are no longer on the team need to get their access revoked.

A simple way to facilitate this is by using Google Contacts Groups. When you are giving permissions in Workspace you can simply type the name of the group and everyone in that group will be given access to that file or folder.

---

Do you have an 💡?

If you have automation ideas you would like built, reach out to me for a free consultation!

---

Create a Group

This will create a prompt in your Google Sheet asking you to give a name to your newly created group.

function createGroup(){
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter the name of the group: ');
  if(response.getSelectedButton() == ui.Button.OK){
    const groupName = response.getResponseText();
    ContactsApp.createContactGroup(groupName);
  }
}

Get Contact Data

We will get the contact data that is on the Google Sheet and then format it into a JSON array of relevant data.

function selectContactData(){
  const sheetData = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  const headerRow = sheetData[0];
  let output = [];
  sheetData.forEach((row, i) => {
    if(i > 0){
      output.push({
        'firstName': row[0],
        'lastName': row[1],
        'email': row[2],
        'company': row[3],
        'jobTitle': row[4],
        'sheetRow': i+1
      })
    }
  })
  return output
}

Add Contacts to Group

In this step, we ask the user which group they would like to add the contacts to, and IF this group exists it will add the contacts to that group.

function addContactsToGroup(){
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Add Contacts to Group','Enter the name of the group you want to add these contacts to:',ui.ButtonSet.OK_CANCEL)
  if(response.getSelectedButton() == ui.Button.OK){
    const contactGroup = doesGroupExist(response.getResponseText())
    if(contactGroup !== false){
      selectContactData().forEach(contact => {
        let newContact = ContactsApp.createContact(contact.firstName, contact.lastName,contact.lastName);
        newContact.addCompany(contact.company, contact.jobTitle)
        newContact.addToGroup(contactGroup)
        SpreadsheetApp.getActiveSheet().getRange(contact.sheetRow,6).setValue(newContact.getId());
      })
    }
  }else{
    ui.alert('Group Does Not Exist',`List of current groups: ${ContactsApp.getContactGroups().map(group =>` ${group.getName()}` )}`,ui.ButtonSet.OK)
    }
}

function doesGroupExist(input){
  let currentGroups = ContactsApp.getContactGroups().map(group => group.getName().toUpperCase());
  let indexOf = currentGroups.indexOf(input.toUpperCase());
  if(indexOf !== -1){
      let contactGroup = ContactsApp.getContactGroups()[indexOf];
      return contactGroup
  } else{
    return false
  }
}

---

Support my Work

If this tutorial was helpful to you and you want to support my work, consider buying me a slice of 🍕, my favorite food, by quite a wide margin!

---

Let’s put it all together

If you want to get the code from GitHub check out the Repo.

function onOpen(){
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Create a Group', 'createGroup')
    .addItem('Add Contacts to Group', 'addContactsToGroup')
    .addToUi()
}

function createGroup(){
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter the name of the group: ');
  if(response.getSelectedButton() == ui.Button.OK){
    const groupName = response.getResponseText();
    ContactsApp.createContactGroup(groupName);
  }
}

function addContactsToGroup(){
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Add Contacts to Group','Enter the name of the group you want to add these contacts to:',ui.ButtonSet.OK_CANCEL)
  if(response.getSelectedButton() == ui.Button.OK){
    const contactGroup = doesGroupExist(response.getResponseText())
    if(contactGroup !== false){
      selectContactData().forEach(contact => {
        let newContact = ContactsApp.createContact(contact.firstName, contact.lastName,contact.lastName);
        newContact.addCompany(contact.company, contact.jobTitle)
        newContact.addToGroup(contactGroup)
        SpreadsheetApp.getActiveSheet().getRange(contact.sheetRow,6).setValue(newContact.getId());
      })
    } else{
      ui.alert('Group Does Not Exist',`List of current groups: ${ContactsApp.getContactGroups().map(group =>` ${group.getName()}` )}`,ui.ButtonSet.OK)
    }
  }
}

function doesGroupExist(input){
  let currentGroups = ContactsApp.getContactGroups().map(group => group.getName().toUpperCase());
  let indexOf = currentGroups.indexOf(input.toUpperCase());
  if(indexOf !== -1){
      let contactGroup = ContactsApp.getContactGroups()[indexOf];
      return contactGroup
  } else{
    return false
  }
}

function selectContactData(){
  const sheetData = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  const headerRow = sheetData[0];
  let output = [];
  sheetData.forEach((row, i) => {
    if(i > 0){
      output.push({
        'firstName': row[0],
        'lastName': row[1],
        'email': row[2],
        'company': row[3],
        'jobTitle': row[4],
        'sheetRow': i+1
      })
    }
  })
  return output
}