Group label counts by month using Gmail apps script

Question

New to google apps script/javascript.

I am trying to group the results of the following code by month in order to use the data as a chart element, what would be the best way to do this:

function listLabels(labelcount) {
  var response = Gmail.Users.Labels.list('me');
  if (response.labels.length == 0) {
    Logger.log('No labels found.');
  } else {
    Logger.log('Labels:');
    for (var i = 0; i < response.labels.length; i++) {
      var label = response.labels[i];

      // Use the label name to get the messages that match this label
      var label_messages = Gmail.Users.Messages.list('me', {
        'labelIds': [label.id]
      });

      Logger.log('%s = %s', label.name, label_messages.resultSizeEstimate);
    }
  }
}

Answer 1

Distribution of Labels over months

This function collects the number of messages with a given label per label per month and for all months disregarding years. Although, you could easily modify it to filter out unwanted years.

It dumps the data out on the active spreadsheet after clearing it each time it runs. For convenience I put the data into monthly array and displayed them utilizing the join() method if you wish to expand them you can easily do so by splitting the column and I included a column above it that contains the first letter of the respective month.

function listLabels() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var response = Gmail.Users.Labels.list('me');
  var msA=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
  var miA=['J','F','M','A','M','J','J','A','S','O','N','D']
  var mA=[0,0,0,0,0,0,0,0,0,0,0,0];
  if (response.labels.length) {
    sh.clearContents();
    sh.appendRow(['Label Name','Monthly Distribution'])
    sh.appendRow(['',miA.join(',')])
    for (var i = 0; i < response.labels.length; i++) {
      var label=response.labels[i];
      var label_messages = Gmail.Users.Messages.list('me', {'labelIds': [label.id]});
      var msgs=label_messages.messages;
      if(msgs) {
        var lA=[0,0,0,0,0,0,0,0,0,0,0,0];
        for(var j=0;j<msgs.length;j++) {
          var m=GmailApp.getMessageById(msgs[j].id).getDate().getMonth();
          mA[m]+=1;
          lA[m]+=1;
        }
        sh.appendRow([label.name,lA.join(',')])
      }
    }
    sh.appendRow(['***********************']);
    sh.appendRow(['***********************']);
    sh.appendRow(['Month','Total Count']);
    for(var i=0;i<12;i++) {
      sh.appendRow([msA[i],mA[i]]);
    }
  }
}

This is what the output looks like.

I truncated the individual labels because I didn't want to share my private information.

• Gmail API
• Class GmailApp

Distribution of Labels over years and months

function listLabelsIncludingYears() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var response = Gmail.Users.Labels.list('me');
  var msA=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
  var miA=['J','F','M','A','M','J','J','A','S','O','N','D']
  var mA=[0,0,0,0,0,0,0,0,0,0,0,0];
  var yObj={yA:[]};
  if (response.labels.length) {
    sh.clearContents();
    sh.appendRow(['Label Name','Year','Monthly Distribution'])
    sh.appendRow(['','',miA.join(',')])
    for (var i = 0; i < response.labels.length; i++) {
      var label=response.labels[i];
      var label_messages = Gmail.Users.Messages.list('me', {'labelIds': [label.id]});
      var msgs=label_messages.messages;
      if(msgs) {
        var lA=[0,0,0,0,0,0,0,0,0,0,0,0];
        for(var j=0;j<msgs.length;j++) {
          var m=GmailApp.getMessageById(msgs[j].id).getDate().getMonth();
          var y=GmailApp.getMessageById(msgs[j].id).getDate().getFullYear();
          if(yObj.hasOwnProperty(y)) {
            yObj[y][m]+=1;
          }else{
            yObj[y]=[0,0,0,0,0,0,0,0,0,0,0,0,0];
            yObj.yA.push(y);
            yObj[y][m]+=1;
          }
          lA[m]+=1;
        }
        sh.appendRow([label.name,y,lA.join(',')]);
      }
    }
    SpreadsheetApp.flush();
    sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn()).sort({column:2,ascending:true});
    sh.getRange(2,3,sh.getLastRow()-1,1).splitTextToColumns(',');
    sh.autoResizeColumns(1,sh.getLastColumn())
    SpreadsheetApp.flush();
    sh.appendRow(['***********************']);
    sh.appendRow(['***********************']);
    sh.appendRow(['Year','Month','Total Count']);
    yObj.yA.sort();
    for(var k=0;k<yObj.yA.length;k++) {
      for(var i=0;i<12;i++) {
        sh.appendRow([yObj.yA[k],msA[i],yObj[y][i]]);
      }
    }
  }
}

I also added auto sorting, split text to columns and resizing.

Added page token support

I added pagetoken support at the request of another user.

function listLabelsIncludingYears() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var response = Gmail.Users.Labels.list('me');
  var msA=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
  var miA=['J','F','M','A','M','J','J','A','S','O','N','D']
  var mA=[0,0,0,0,0,0,0,0,0,0,0,0];
  var yObj={yA:[]};
  if (response.labels.length) {
    sh.clearContents();
    sh.appendRow(['Label Name','Year','Monthly Distribution'])
    sh.appendRow(['','',miA.join(',')])
    for (var i = 0; i < response.labels.length; i++) {
      var label=response.labels[i];
      var label_messages = Gmail.Users.Messages.list('me', {'labelIds': [label.id]});
      var pagetoken='';
      do{
        var msgs=label_messages.messages;
        if(msgs) {
          var lA=[0,0,0,0,0,0,0,0,0,0,0,0];
          for(var j=0;j<msgs.length;j++) {
            var m=GmailApp.getMessageById(msgs[j].id).getDate().getMonth();
            var y=GmailApp.getMessageById(msgs[j].id).getDate().getFullYear();
            if(yObj.hasOwnProperty(y)) {
              yObj[y][m]+=1;
            }else{
              yObj[y]=[0,0,0,0,0,0,0,0,0,0,0,0,0];
              yObj.yA.push(y);
              yObj[y][m]+=1;
            }
            lA[m]+=1;
          }
          sh.appendRow([label.name,y,lA.join(',')]);
        }
        pagetoken=label_messages.nextPageToken;  
      }while(pagetoken!='');
    }
    SpreadsheetApp.flush();
    sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn()).sort({column:2,ascending:true});
    sh.getRange(2,3,sh.getLastRow()-1,1).splitTextToColumns(',');
    sh.autoResizeColumns(1,sh.getLastColumn())
    SpreadsheetApp.flush();
    sh.appendRow(['***********************']);
    sh.appendRow(['***********************']);
    sh.appendRow(['Year','Month','Total Count']);
    yObj.yA.sort();
    for(var k=0;k<yObj.yA.length;k++) {
      for(var i=0;i<12;i++) {
        sh.appendRow([yObj.yA[k],msA[i],yObj[y][i]]);
      }
    }
  }
}

Note: page token support has not been tested because I delete most of my emails so I would never trigger the need.