2

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")

1

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.

  • I want to query the information directly from the inbox. Inbox, how many emails do we have labeled as priority 1? Display number here in this sheet. – Bryan Canterbury Jan 17 '17 at 16:53
  • I see, that will have to be done through a script I'll work on that and post something up a little later. – STowslee Jan 17 '17 at 17:05
  • Give that a try and let me know how it works out for you. – STowslee Jan 17 '17 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.