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
      }
    }

  }          

}

Comments on this post

  1. john Anderson said on May 20, 2016 at 1:37 am

    thank you tom

    is there any way to make a bookmarklet from this script

    https://gist.github.com/yanofsky/5436496

    • Tom Woodward said on May 20, 2016 at 7:44 am

      Not without some work. That script is in Python so, as far as I know, you’d need to rewrite it in javascript.

      I’ve used Tags by Martin Hawksey a few times to do tag based archiving and I keep my own Twitter archive updated using another thing he wrote.

  2. john Anderson said on May 20, 2016 at 10:00 am

    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

Leave a Reply

Trackbacks and Pingbacks on this post

  1. Rampages Stats vs My Own Data – Bionic Teaching said on September 13, 2016 at 9:53 am

    […] was handy. I’ve been playing with logging data for a variety of reasons. For instance I now tag my rampages support emails in GMail and that logs them to a spreadsheet each night. I’m at least mostly consistent doing that because it’s a very light weight action on […]

TrackBack URL