Filter gmails using timestamp

user_1749354 0
Viewed 114

Am new dabbling with Google Apps Script; would like to ask if I'm in the right direction, and how to I manipulate time within the script.

I'm struggling in trying to maniuplate time values in Google App Script, basically I am able to pull the timestamp of each email sent, but I only want to paste into the spreadsheet email information that were recent, e.g. within 30minutes from script run time. This is to avoid pulling duplicate information.

Not sure if there is a currentTime() function here, or I have to create a new Date() object and do some calculations from there. Tried a few variations and nothing seemed to work proper.

Would appreciate any help in getting towards the right direction in doing this thank you!

function getDetails(){
  var DEST_URL = "SHEET_URL"; //redacted for sensitivity
  var DEST_SHEETNAME = "Test";
  
  var destss = SpreadsheetApp.openByUrl(DEST_URL);
  var destSheet = destss.getSheetByName(DEST_SHEETNAME);
  
  var threads = GmailApp.search("FILTERS"); //filter settings redacted for sensitivity

  for(var i = 0; i < threads.length; i++){
    var messages=threads[i].getMessages();  
    for(var j =0; j < 1; j++){ //only take first message in thread
      var message = messages[j];
      var subject = message.getSubject() ;
      var sentTimeStamp = message.getDate();

      if(sentTimeStamp is within last 30minutes as of script run time){ //this is where i need help
          var delimitString = subject.split("is sent");
          var detailName = delimitString[0];

          var lastRow = destSheet.getLastRow();
          destSheet.getRange(lastRow + 1,1).setValue(detailName);
          destSheet.getRange(lastRow + 1,2),setValue(sentTimeStamp);
      }
    }
  }
}
gmail-api google-apps-script google-sheets
2 Answers

You can convert timeStamp into ms seconds and then compare to the value of "30 s ago"

Sample:

      var sentTimeStamp = message.getDate();
      var now = new Date();
      var ThirtyMinutesAgo = now-30*60*1000;
      if(sentTimeStamp.getTime() < ThirtyMinutesAgo){
        ...
      }

References:

user_115997890

Another idea would be to query for emails that you received the last 30 minutes.

Explanation:

You can get the emails that you received the last 30 minutes ago as a query in the GmailApp.search function. See this link to see what filters you can use.

This will get the last emails with keyword "FILTERS" that you received the last 30 minutes.

var ThirtyMinutesAgo = new Date(); 
ThirtyMinutesAgo.setMinutes(ThirtyMinutesAgo.getMinutes() - 30);
const queryString = `"FILTERS" newer:${Math.round(ThirtyMinutesAgo.getTime()/1000)}`
const threads = GmailApp.search(queryString); // threads the last 30 minutes

This approach is more efficient for two reasons:

  1. You have less data (threads) to iterate over with the for loop.

  2. You don't need to apply and if statement on every thread.

Solution:

function getDetails(){
  var DEST_URL = "SHEET_URL"; //redacted for sensitivity
  var DEST_SHEETNAME = "Test";
  
  var destss = SpreadsheetApp.openByUrl(DEST_URL);
  var destSheet = destss.getSheetByName(DEST_SHEETNAME);
  
//  var threads = GmailApp.search("FILTERS"); //filter settings redacted for sensitivity
  
  // new code
  var ThirtyMinutesAgo = new Date(); 
  ThirtyMinutesAgo.setMinutes(ThirtyMinutesAgo.getMinutes() - 30);
  const queryString = `"FILTERS" newer:${Math.round(ThirtyMinutesAgo.getTime()/1000)}`
  const threads = GmailApp.search(queryString); // threads the last 30 minutes
  //

  for(var i = 0; i < threads.length; i++){
    var messages=threads[i].getMessages();  
    for(var j =0; j < 1; j++){ //only take first message in thread
      var message = messages[j];
      var subject = message.getSubject() ;
      var sentTimeStamp = message.getDate();

          var delimitString = subject.split("is sent");
          var detailName = delimitString[0];

          var lastRow = destSheet.getLastRow();
          destSheet.getRange(lastRow + 1,1).setValue(detailName);
          destSheet.getRange(lastRow + 1,2),setValue(sentTimeStamp);
      }
    }
  }
}
user_112252910
Related