CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgmail
ashokkumar ponnnusamy
ashokkumar ponnnusamy

Reputation: 75

Get email details based on the dates in google sheets

I am using the below mentioned apps script to get the email details in google sheets based on the label name updated in the particular cell.

    function getBaEmails() {
    var sheet   = SpreadsheetApp.getActiveSpreadsheet();
    var ss = sheet.getSheetByName("Raw Data");
    var row     = 2;
    ss.getRange(2, 1, ss.getMaxRows() - 1, 4).clearContent();
    var label   = ss.getRange("F1").getValue();
    var pattern = ss.getRange("F2").getValue();
    var threads = GmailApp.search("in:" + label);
    for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var m = 0; m < messages.length; m++) {
    var msg = messages[m].getBody();
    if (msg.search(pattern) !== -1) {
    ss.getRange(row,1).setValue(
    Utilities.formatDate(messages[m].getDate(),"GMT","MM-dd-yyyy"));
    ss.getRange(row,2).setValue(messages[m].getFrom());        
    ss.getRange(row,3).setValue(messages[m].getSubject());
    var id = "https://mail.google.com/mail/u/0/#all/"
    + messages[m].getId();
    ss.getRange(row,4).setFormula(
    '=hyperlink("' + id + '", "View")');
    row++;
       }
      }
     }
    };

I am trying to update the apps script which helps to get the email details based on the dates.

For example : I need a email details from 08/01/2019 to 08/30/2019.

It would be great, if anyone help me to update the apps script.

Thanks in advance.

Upvotes: 0

Views: 562

Answers (1)

Cooper
Cooper

Reputation: 64042

Try something like this:

You can always test your queries in Gmail

function gmailSearch(label,after,before) {
  var label=label||'qs-vendors-google';
  var after=after||'2019/01/01';
  var before=before||'2019/08/01';
  var html='';
  var qry=Utilities.formatString('label:%s after:%s before:%s',label,after,before);
  var results=GmailApp.search(qry);
  for(var i=0;i<results.length;i++) {
    var msgs=results[i].getMessages()
    for(j=0;j<msgs.length;j++) {
      html+=Utilities.formatString('<strong>Date:</strong> %s <strong>From: </strong> %s <strong>Subject: </strong> %s<br />',msgs[j].getDate(),msgs[j].getFrom(),msgs[j].getSubject());
    }
  }
  var userInterface=HtmlService.createHtmlOutput(html).setWidth(1000);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Search Results')
}

Upvotes: 1

Related Questions