Auto-Logging Email via Google Script


flickr photo shared by OSU Special Collections & Archives : Commons with no copyright restriction (Flickr Commons)

A while back I was logging emails in a Google sheet via IFTTT. I’d add a hashtag and forward it on where a spreadsheet would parse out some stuff from the subject line based on the | character. At some point it stopped working and I never quite figured out what the issue was. I thought I wrote about it but, if I did, I can’t find it. It may be in one of my many draft posts.

In any case, here’s a better and more customizable solution.

It’ll grab anything I label ‘support’ and throw it in a spreadsheet with a few different chunks of information in different columns (to, from, date, subject line, link to the email).

You’d open a spreadsheet and name a sheet ‘data.’

Open up the script editor (Tools>Script Editor) and put the script below in it.

You might want to change the search parameters. Look at the stuff below the asterisk line. If you want to play around with the right search parameters, just practice in GMail with these parameters and then use them in the query variable below.

You will want to set the trigger to run at 1-2AM each day.

So now, all I have to do to keep track of all the people I help via email is to label the email. Then I can run reports etc. using that spreadsheet as data.

function myFunction() {
  
//this is just the stuff that recognizes what spreadsheet you're in
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheets = ss.getSheets();
   var sheet = ss.getSheetByName("data"); //gets the right sheet
  
//this chunk gets the date info  
 var today = new Date();
 var dd = today.getDate()-1;
 var mm = today.getMonth()+1; //January is 0 DO NOT FORGET THIS
 var yyyy = today.getFullYear();
 var yesterday = yyyy + '/' + mm + '/' + dd;

//****************************************************  
//searches your GMail for emails matching two things, written after yesterday and with the label support
  var query = "after:" + yesterday  + " label:support";
  
  var threads = GmailApp.search(query);
  Logger.log('threads len ' + threads.length);
  
  Logger.log(query);
  
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    Logger.log(messages);    
    for (var m = 0; m < messages.length; m++) {
       var supportStats = [];
     
//here's where you decide what parts of the email you want
      var from = messages[m].getFrom(); //from field
      Logger.log(from);
      var to = messages[m].getTo();//to field
      Logger.log(to);
      var time = messages[m].getDate();//date field
      Logger.log(time);
      var subject = messages[m].getSubject();//subject field
      Logger.log(subject);
      var mId = messages[m].getId();//id field to create the link later
      
      var mYear = time.getFullYear();
      var mMonth = time.getMonth()+1;
      var mDay = time.getDate();
      var messageDate = mYear + '/' + mMonth + '/' + mDay;
      Logger.log('msg date ' + messageDate);

//decides what found emails match yesterday's date and push them to an array to write to the spreadsheet
      if (messageDate === yesterday) {
      supportStats.push(from);
      supportStats.push(to);
      supportStats.push(time);
      supportStats.push(subject);
      supportStats.push('https://mail.google.com/mail/u/0/#inbox/'+mId); //build the URL to the email
      SpreadsheetApp.getActiveSheet().appendRow(supportStats); //writes to the spreadsheet
      }
    }

  }          

}

4 thoughts on “Auto-Logging Email via Google Script

  1. there is many script to get all tweets from any user

    if you can make a bookmarklet from any of these script I would be grateful

Comments are closed.