Search code examples
google-apps-scriptgmail

Counting emails received in date range (current count too high)

I am trying to set up a very simple script to count the amount of individual emails (not threads) received in Gmail over a specific timeframe. Currently testing with set dates where I know what the individual message count should be.

I have used the following code

function emailCount() {
    var receivedCount = 0;
    var threads = GmailApp.search("to:("my email") after:2023/02/01 before:2023/02/02"); 
    Logger.log(threads.length);

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

    Logger.log(receivedCount);
}

Which returns the correct amount of email threads, but the email count is too high. The script is counting individual emails from the returned threads, even if those emails do not fit my search parameters (i.e. not in my date range).

Is there a way to return an individual email count from a date range, so I can get an accurate count for how many emails were received within a certain date range?

Similar posts have been made about email counts (I got the script from this post- Google Apps Script to count number of emails sent/received yesterday, that has a certain label, then save # daily to spreadsheet) but I can't see how I can achieve the count I am after.

Solved!

Thanks to @Tedinoz for their post below and advice. Amended their code slightly to create a function that generates an email count on a daily basis and saves it in a spreadsheet.

//Not my code! All credit to @Tedinoz and others recognised at the end of the post. 

function emailCount() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')

  var startDate = sheet.getRange('C1').getValue(); // the start date of search range

  var endDate = sheet.getRange('E1').getValue(); // the end date of serach range

  Logger.log("DEBUG: Search Start date = "+startDate+",\nSeachEnd date = "+endDate)

   var startDateFormated = formatDate(startDate); // using function to covert date to yyyy/mm/ddd so it can be used in a gmail search. 

  Logger.log ("DEBUG: Formated start date = "+startDateFormated);

  var endDateFormated = formatDate(endDate);// using function to covert date to yyyy/mm/ddd so it can be used in a gmail search.

 Logger.log ("DEBUG: Formated end date = "+endDateFormated);

 Logger.log("to:([email protected]) after:"+startDateFormated+" before:"+endDateFormated); // log of what the gmail search will be


  
  var threads = GmailApp.search("to:([email protected]) after:"+startDateFormated+" before:"+endDateFormated); // gmail search 
  var threadCount = threads.length // pulls threads from search

   var messsageCounter = 0 // sets the message counter as 0

   for (var i=0;i<threadCount;i++){ // loops through the threads to get messages
      var messages = threads[i].getMessages()     
     var messageCount = messages.length
     Logger.log("DEBUG: Number of messages: "+messageCount)
    
     for (var m=0;m<messageCount;m++){ // loops through the messages to get dates
       var messageDate = messages[m].getDate()
       var subject = messages[m].getSubject() // pulls subject for easy debugging
       var sender =messages[m].getFrom() // pulls from as will need to exlude certain senders

      //Compare message date to search parameters. As well as dates needed to exclude emails sent by myself, as these were being included in the message counts from threads. 
      if (startDate.valueOf() <= messageDate.valueOf()&&messageDate.valueOf()<=endDate.valueOf()&&sender.valueOf()!="My name <my email address>"){
         Logger.log("DEBUG: Valid message: message date = "+messageDate+" subject = "+subject+" from = "+sender)
         messsageCounter++
      }
       else{
          Logger.log("DEBUG: Failed message: message date = "+messageDate)
      }
     }
   }
  Logger.log("Number of valid messages = "+messsageCounter)

// last section of script saves returned count into my spreadsheet. I have a trigger for a daily count, so it saves it into the last entry of a column.
//Also not my original code, but cannot find where I originally copied it from! 

  var columnIndex = 8 ; //col h - this is the col where I want to save the email count
  var values = sheet.getRange(1, columnIndex, sheet.getMaxRows()).getValues(); 
  for (var t = 0; t < values.length; t++) {
    if (values[t] == "") {
      var emptyCellIndex = t + 1;
      break;
    }
  }
  sheet.getRange(emptyCellIndex, columnIndex).setValue(messsageCounter);
}

To format the date, so it can be used in a gmail search function, I used the following code from @user3470953. Found as an answer to the post - Format JavaScript date as yyyy-mm-dd


function formatDate(date) {
    var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        year = d.getFullYear();

    if (month.length < 2) 
        month = '0' + month;
    if (day.length < 2) 
        day = '0' + day;

    return [year, month, day].join('/');
}
Solution

  • GmailApp.search returns threads. In order to evaluate/identify the specific messages that satisfy the dates in the search query, one must loop through the messages, get the message date, and compare the message date to the "from"/"to" dates of the query.

    The following script is an example of looping through the messages recovered from GMail.search. The number of threads returns was 1 (one); the number of messages = 5 (five); however only 2 (two) messages satisfied the criteria for the "from"/"to" dates.

    function myFunction() {
  var startDate = new Date(2023, 0, 24); // the month is 0-indexed
  var endDate = new Date(2023, 0, 27); // the month is 0-indexed
  Logger.log("DEBUG: Search Start date = "+startDate+",\nSeachEnd date = "+endDate)
  
  var threads = GmailApp.search("subject:(Britax Safe-n-Sound Compaq AHR) after:2023/1/24 before:2023/1/27"); 
  var threadCount = threads.length

  var messsageCounter = 0
  for (var i=0;i<threadCount;i++){
    var messages = threads[i].getMessages()     
    var messageCount = messages.length
    Logger.log("DEBUG: Number of messages: "+messageCount)
    
    for (var m=0;m<messageCount;m++){
      var messageDate = messages[m].getDate()

      //Compare message date to search parameters
      if ((startDate.valueOf() <= messageDate.valueOf()) ||(messageDate.valueOf()<=endDate.valueOf)){
        Logger.log("DEBUG: Valid message: message date = "+messageDate)
        messsageCounter++
      }
      else{
        Logger.log("DEBUG: Failed message: message date = "+messageDate)
      }
    }
  }
  Logger.log("Number of valid messages = "+messsageCounter)
}

    RESULTS

    snapshot