Google-sheets – Reference Gmail labels in Google Sheets

formulasgmailgmail-labelsgoogle sheets

We have a shared Gmail account for our support team. That inbox has been given labels including a set which lists the email's priority level 0-5. To keep track of the progress we use a Google sheet which requires manually entering the quantities of emails with each priority label.

Is there a way to reference the quantities of each of these labels in Google sheets?

Not an actual formula but for conceptual purposes:
=QTY(inbox label "priority 1")

Best Answer

This should do what you are looking for:

When you open your spreadsheet go to tools --> script editor and you can copy/paste the code below.

Refresh the spreadsheet and you will have a new menu item at the far left called GMail, opening this menu will give you the ability to click on "Count Labels." Doing so will run the script.

This will go through your inbox and for every label applied create a new row on a tab called "priorities" which will have the label name and the count of the emails which have that specific tag assigned to them. From that tab you can reference the table in any way you'd like.

function label_count() {

  var labels = GmailApp.getUserLabels();
  var label_array = [];
  for (var i = 0; i < labels.length; i++) {
    var label_name = labels[i].getName();
    var label_count = GmailApp.getUserLabelByName(label_name).getThreads().length;
label_array.push([label_name,label_count]);;
  }

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('priorities');
  sheet.clear();
  sheet.appendRow(['Label Name','Label Count']);
  sheet.getRange(sheet.getLastRow()+1, 1, label_array.length, 2).setValues(label_array);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('GMail')
  .addItem('Count Labels', 'label_count')
  .addToUi();
}

Some notes:

  1. You will need to add the "priorities" tab to the spreadsheet you are using this script on.

  2. This script must be installed and run while you are using it as the user whose e-mail you are looking to search (your shared email address).

  3. This script will only update the page when you tell it to by using the custom menu, if you want it to update automatically at specific intervals you can set up a trigger through the script editor by clicking on the icon which looks like a clock.

Related Topic