App script to scrape OTF Email Summary

otfEmailScrapper.gs

function scrapeEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var threads = GmailApp.search("from:[email protected] OR [email protected] ");
  sheet.getRange(1,1).setValue("Date");
  sheet.getRange(1,2).setValue("Time");
  sheet.getRange(1,3).setValue("Coach");
  sheet.getRange(1,4).setValue("Location");
  sheet.getRange(1,5).setValue("Minutes in Grey Zone");
  sheet.getRange(1,6).setValue("Minutes in Blue Zone");
  sheet.getRange(1,7).setValue("Minutes in Green Zone");
  sheet.getRange(1,8).setValue("Minutes in Orange Zone");
  sheet.getRange(1,9).setValue("Minutes in Red Zone");
  sheet.getRange(1,10).setValue("Calories");
  sheet.getRange(1,11).setValue("SplatPoints");
  sheet.getRange(1,12).setValue("Avg. Heart Rate");
  sheet.getRange(1,13).setValue("Peak HR");
  sheet.getRange(1,14).setValue("Miles on Treadmill");
  sheet.getRange(1,15).setValue("Minutes on Treadmill");
  sheet.getRange(1,16).setValue("Avg, Treadmill Pace");
  sheet.getRange(1,17).setValue("Day of the Week");
  sheet.getRange(1,18).setValue("Month");
  sheet.getRange(1,19).setValue("Month");
  sheet.getRange(1,20).setValue("Count");
  sheet.getRange(1,21).setValue("Year");
  
  for(var i = 0; i< threads.length; i++) {
    var msgs = threads[i].getMessages();
    var plain = msgs[0].getPlainBody();
    var noBlankLines = plain.replace(/^\s*\n/gm, "");
    var contentArray = noBlankLines.split("\n");
        
    var date = msgs[0].getDate();
    /*if (date < new Date('February 01, 2020 00:00:00')) {
      continue;
    }*/
    
    sheet.getRange(i+2, 1).setValue(date);
    sheet.getRange(i+2, 2).setValue(contentArray[6]);
    sheet.getRange(i+2, 3).setValue(contentArray[7]);
    sheet.getRange(i+2, 4).setValue(contentArray[4]);
    sheet.getRange(i+2, 5).setValue(parseInt(contentArray[8]));
    sheet.getRange(i+2, 6).setValue(parseInt(contentArray[9]));
    sheet.getRange(i+2, 7).setValue(parseInt(contentArray[10]));
    sheet.getRange(i+2, 8).setValue(parseInt(contentArray[11]));
    sheet.getRange(i+2, 9).setValue(parseInt(contentArray[12]));
    sheet.getRange(i+2, 10).setValue(parseInt(contentArray[15]));
    sheet.getRange(i+2, 11).setValue(parseInt(contentArray[18]));
    sheet.getRange(i+2, 12).setValue(parseInt(contentArray[21]));
    sheet.getRange(i+2, 13).setValue(parseInt(contentArray[23].split(":")[1]));
    
    var miles = parseFloat(contentArray[30].split(" ")[0]);
    sheet.getRange(i+2, 14).setValue(miles);
    var timeOnTread = 0;
    var tmPace = 0.0;
    var timeStr = contentArray[32];
    var timeM = parseInt(contentArray[32].split(":")[0]);
    var timeS = parseInt(contentArray[32].split(":")[1]);
    timeOnTread = ((timeM * 60) + timeS) / 60.0;
    if (timeOnTread != 0) {
      tmPace = miles * 60.0 / timeOnTread;
    }
    
    sheet.getRange(i+2,15).setValue(timeOnTread);
    sheet.getRange(i+2,16).setValue(tmPace);
    sheet.getRange(i+2,17).setValue(intToDayOfWeek(date.getDay()))
    
    sheet.getRange(i+2, 18).setValue(intToMonth(date.getMonth()));
    sheet.getRange(i+2, 19).setValue(date.getMonth()+1);
    sheet.getRange(i+2, 20).setValue(1);
    sheet.getRange(i+2, 21).setValue(date.getFullYear());
  }
}

function intToMonth(i) {
  switch(i) {
    case 0: return "January";
    case 1: return "February";
    case 2: return "March";
    case 3: return "April";
    case 4: return "May";
    case 5: return "June";
    case 6: return "July";
    case 7: return "August";
    case 8: return "September";
    case 9: return "October";
    case 10: return "November";
    case 11: return "December";
    default: return "Unknown";
  }
}

function intToDayOfWeek(i) {
  switch(i) {
    case 0: return "Sunday";
    case 1: return "Monday";
    case 2: return "Tuesday";
    case 3: return "Wednesday";
    case 4: return "Thursday";
    case 5: return "Friday";
    case 6: return "Saturday";
    default: return "Unknown";
  }
}