Google Apps Script to count number of emails sent/received yesterday, that has a certain label, then save # daily to spreadsheet

Question

I looked at the below question and it gets me half way there. I can run Suyash Gandhi's code and it will count the total number of emails with a certain label for all of time. I am trying to get a count for (A) # of emails sent and (B) # emails received by label for a specific date.

Google Apps Script to count number of emails received yesterday, that has certain label, then save # daily to spreadsheet

For example,

Label    Date       #Received    #Sent
Test     1/1/2017   2            4
Test     1/2/2017   2            4
Test     1/3/2017   2            4
Test     1/4/2017   2            0 
Test     1/10/2017  2            8 

Vs what the code currently does:
Label    Date       Count
Test     1/10/2017   30

I'd like to run the script say at midnight to count the prior days emails.

Shyam Kansagra Shyam Kansagra · Accepted Answer · 2017-01-30T07:23:25

I've come up with this script, hope it helps.

var labelName = "purgeMailTest";
var theDate ="2017-01-28";

function myFunction() 
{
  var dayOfMonth = theDate.split("-");
  var nextDayOfMonth = parseInt(dayOfMonth[2])+1;
  var nextDate = dayOfMonth[0]+"-"+dayOfMonth[1]+"-"+nextDayOfMonth;
  var sentCount = 0, receivedCount = 0;

  //Sent count
  var sentSearchQuery = "label:"+labelName+" before:"+nextDate+" after:"+theDate+" is:sent";
  var threads = GmailApp.search(sentSearchQuery, 0, 500);

  for(var i=0; i<threads.length; i++)
  {
    sentCount = sentCount + threads[i].getMessages().length;
  }

  Logger.log(theDate +":"+sentCount+" sent");

  //Received count
  var receivedSearchQuery = "label:"+labelName+" before:"+nextDate+" after:"+theDate+" -is:sent";
  var threads = GmailApp.search(receivedSearchQuery, 0, 500);


  for(var i=0; i<threads.length; i++)
  {
    receivedCount = receivedCount + threads[i].getMessageCount();
  }

  Logger.log(theDate +":"+receivedCount+" received");

}

To summarize:

I have simply used gmail search filters to get the sent and received emails for a particular date.

How to use this script?

change label name according to your environment and also set the theDate variable as the date you want and run the script. And when it finishes, check the logs.

Let me know if any help required further.

Google Apps Script to count number of emails sent/received yesterday, that has a certain label, then save # daily to spreadsheet

1 Answers