Log UPS & FedEx delivery times from Gmail to a Google Spreadsheet

logFedEx.js

// paste this into https://script.google.com
// setup a trigger to run on a schedule
// create a spreadsheet with these columns:
// "Tracking Code",Date,Time,Service,Carrier
var GOOGLE_SPREADSHEET_ID = "CHANGE_ME";

function getFedExDeliveries() {
  // get spreadsheet and tab
  var ss = SpreadsheetApp.openById(GOOGLE_SPREADSHEET_ID);
  var sheet = ss.getSheetByName('raw-data');
  var range = sheet.getDataRange();
  var data = range.getValues();
  var rowCount = range.getNumRows();
  
  // get the tracking code values from column A
  var trackingCodes = [];
  data.forEach(function(row) { 
    trackingCodes.push(row[0].toString())
  })
  
  var threads = GmailApp.search('subject:"fedex" "your package has been delivered"');
  for (var i = 0; i < threads.length; i++) {
   if (threads[i].getFirstMessageSubject().indexOf("delivered")>-1) {
      var deliveryTimes = getFedExDeliveryTime(threads[i]);
      deliveryTimes.forEach(function(delivery) {
        Logger.log(trackingCodes);
        if (trackingCodes.indexOf(delivery[0]) < 0) {
          var lastRow = sheet.getLastRow() + 1;
          delivery.push("=WEEKDAY(B" + lastRow.toString() + ")");
          sheet.appendRow(delivery);
          trackingCodes.push(delivery[0].toString());

        }
      })
    }
  }
  
  sheet.sort(2, false);
}

function getFedExDeliveryTime(thread) {
  var response = [];
  var messages = thread.getMessages();
  for (var i = 0; i < messages.length; i++) {
    var threadResponse = [];
    
    var plainBody = messages[i].getPlainBody();
    plainBody = plainBody.replace(/(\r\n|\n|\r)/gm," ");
    plainBody = plainBody.replace(/\xa0/gm, "")
    
    var trackingCode = findTrackingCode(plainBody);
    if (!trackingCode) {
      return [];
    } else {
      threadResponse.push(trackingCode);
    }
    
    var deliveryDateRegex = /.*Delivery date:.*([0-9]+\/[0-9]+\/[0-9]{4})/im;
    var deliveryDate = deliveryDateRegex.exec(plainBody);
    threadResponse.push(insertData(deliveryDate));

    var deliveryTimeRegex = /.*Delivery date:.*([0-9]+:[0-9]+)\s?(am|pm)/im;
    var deliveryTime = deliveryTimeRegex.exec(plainBody);
    var parsedDeliveryTime = "";
    if (deliveryTime && deliveryTime.length) {
      if (deliveryTime[1]) { 
        parsedDeliveryTime = deliveryTime[1].toString();
        if (deliveryTime[2]) {
          parsedDeliveryTime = parsedDeliveryTime + " " + deliveryTime[2].toString();
        }
        
      }
    }
    threadResponse.push(parsedDeliveryTime);
    
    var serviceRegex = /Service type:\* (.*)\s+\*Packaging type/im;
    var service = serviceRegex.exec(plainBody);
    threadResponse.push(insertData(service));
    
    threadResponse.push('FedEx');

    response.push(threadResponse);
  }
  
  return response;
}

function insertData(regexMatch) {
  if (regexMatch && regexMatch.length) {
    return regexMatch[1];
  }
  return '';
}

function findTrackingCode(plainBody) {
  var match = null;
  var possibleFormats = [
    /Tracking # (96\d{20})/,
    /Tracking # ([0-9]{12,15})/
  ];

  possibleFormats.forEach(function(regexFormat) {
    if (!plainBody.match(regexFormat)) return;
    match = regexFormat.exec(plainBody)[1];
  });
  
  return match;
}

logUPS.js

// paste this into https://script.google.com
// setup a trigger to run on a schedule
// create a spreadsheet with these columns:
// "Tracking Code",Date,Time,Service,Carrier
var GOOGLE_SPREADSHEET_ID = "CHANGE_ME";

function getUpsDeliveries() {
  // get spreadsheet and tab
  var ss = SpreadsheetApp.openById(GOOGLE_SPREADSHEET_ID);
  var sheet = ss.getSheetByName('raw-data');
  var range = sheet.getDataRange();
  var data = range.getValues();
  var rowCount = range.getNumRows();
  
  // get the tracking code values from column A and the dates from column B
  var trackingCodes = [];
  var dates = [];
  
  data.forEach(function(row, index) {
    if (index > 0) {
      trackingCodes.push(row[0]);
      dates.push(row[1]);
    }  
  })
  var maxDate=new Date(Math.max.apply(null,dates));

  var threads = GmailApp.search('subject:"Your UPS package was delivered"'); // after:' + maxDate.toLocaleDateString());
  for (var i = 0; i < threads.length; i++) {
   if (threads[i].getFirstMessageSubject().indexOf("Your UPS Package was delivered")>-1) {
      var deliveryTimes = getUpsDeliveryTime(threads[i]);
      deliveryTimes.forEach(function(delivery) {
        if (trackingCodes.indexOf(delivery[0]) < 0) {
          var lastRow = sheet.getLastRow() + 1;
          delivery.push("=WEEKDAY(B" + lastRow.toString() + ")");
          sheet.appendRow(delivery);

        }
      })
    }
  }
  
  sheet.sort(2, false);
  
}

function insertData(regexMatch) {
  if (regexMatch && regexMatch.length) {
    return regexMatch[1];
  }
  return '';
}

function getUpsDeliveryTime(thread) {
  var response = [];
  var messages = thread.getMessages();
  for (var i = 0; i < messages.length; i++) {
    var threadResponse = [];
    
    var plainBody = messages[i].getPlainBody();
    plainBody = plainBody.replace(/(\r\n|\n|\r)/gm," ");
    plainBody = plainBody.replace(/\xa0/gm, "")
    
    // this only records the first tracking number, just to ensure no duplicates are inserted into the spreadsheet
    var trackingNumbersRegex = /.*Tracking Number:.*\b(1Z ?[0-9A-Z]{3} ?[0-9A-Z]{3} ?[0-9A-Z]{2} ?[0-9A-Z]{4} ?[0-9A-Z]{3} ?[0-9A-Z]|[\dT]\d\d\d ?\d\d\d\d ?\d\d\d)\b/;
    var trackingNumber = trackingNumbersRegex.exec(plainBody);
    threadResponse.push(insertData(trackingNumber));

    var deliveryDateRegex = /.*Delivery Date:.*([0-9]{2})\/([0-9]{2})\/([0-9]{4})/im;
    var deliveryDate = deliveryDateRegex.exec(plainBody);
    var year, month, day, fullDate;
    if (deliveryDate && deliveryDate.length) {
        month = Math.floor(deliveryDate[1]);
        day = Math.floor(deliveryDate[2]);
        year = deliveryDate[3];
        fullDate = [month, day, year].join('/');
    }
    
    threadResponse.push(fullDate);

    var deliveryTimeRegex = /.*Delivery Time:.*([0-9]{2}):([0-9]{2})\s(AM|PM)/im;
    var deliveryTime = deliveryTimeRegex.exec(plainBody);
    var parsedDeliveryTime = "";
    if (deliveryTime && deliveryTime.length) {
      if (deliveryTime[1] && deliveryTime[2]) { 
        parsedDeliveryTime = Math.floor(deliveryTime[1]).toString() + ":" + Math.floor(deliveryTime[2]).toString();
        if (deliveryTime[3]) {
          parsedDeliveryTime = parsedDeliveryTime + " " + deliveryTime[3].toString();
        }
        
      }
    }
    threadResponse.push(parsedDeliveryTime);
    
    var serviceRegex = /.*UPS Service:\s?(UPS\s(Ground|SurePost|Next Day Air Saver|2nd Day Air|3 Day Select|Saver|Worldwide Saver|Next Day Air))?/i;
    var service = serviceRegex.exec(plainBody);
    threadResponse.push(insertData(service));
    
    threadResponse.push('UPS');

    response.push(threadResponse);
  }
  
  return response;
  
}

function test(){
  // get spreadsheet and tab
  var ss = SpreadsheetApp.openById(GOOGLE_SPREADSHEET_ID);
  var sheet = ss.getSheetByName('raw-data');
  var range = sheet.getDataRange();
  var data = range.getValues();
  var rowCount = range.getNumRows();
  
  // get the tracking code values from column A and the dates from column B
  var trackingCodes = [];
  var dates = [];
  data.forEach(function(row, index) {
    if (index > 0) {
      trackingCodes.push(row[0]);
      dates.push(row[1]);
    }  
  })
  var maxDate=new Date(Math.max.apply(null,dates));

  var threads = GmailApp.search('subject:"Your UPS package was delivered" after:' + maxDate.toLocaleDateString());
  
  Logger.log(maxDate.toLocaleDateString());
}