Creates status mail from google spreadsheet

journal.gs

var _sendEmail = true;

function debugGoalStatus() {
  _sendEmail = false;
  sendGoalStatus();
}

function sendGoalStatus() {
  // TODO: (Nice to have) Only send jubel-email if not already sent!
  var events = getEvents();
  for each (var goal in getGoals()) {
    var count = 0;
    for each (var event in events) {
      if (eventMatchesGoal(goal, event)) {
        count += event.count;
      }
    }
    var goalTitle = goal.name + (goal.note && " " + goal.note);
    var subject = "";
    var message = "";
    if (count < goal.target) {
      var subject = "Målet '" + goalTitle + "' er endnu ikke opnået!";
      var message = subject + "<br/> Du har opnået " + count + ". Target er " + goal.target + ".";
    }
    else {
      var subject = "Målet '" + goalTitle + "' er nået!";
      var message = subject + "<br/> Du har opnået " + count + ". Target var " + goal.target + ".";
    }
    Logger.log(message);
    sendEmailAboutGoal(subject, goal, message);
  }
}

function sendEmailAboutGoal(subject, goal, message) {
  if (!_sendEmail) {
    return;
  }

  var emailAddress = "somebody@example.com";
  var html = "<div>Angående målet: " + goal.name + (goal.note && " " + goal.note) + "</div>";
  html += "<div>" + message + "</div>";
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: html
  });
}

function eventMatchesGoal(goal, event) {
  if (event.name !== goal.name) return false;
  if (goal.note && event.note !== goal.note) return false;
  return periodMatches(goal, event);
}

function periodMatches(goal, event) {
  if (goal.periodType == 'Uge' && goal.period == event.week) return true;
  if (goal.periodType == 'Måned' && goal.period == event.date.getMonth()+1) return true;
  return false;
}

function getDay(date) {
  var parts = date.split(' ');
  var year = parts[2];
  var months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
  var month = months.indexOf(parts[0]);
  var day = parts[1].replace(',', '');
  return new Date(year, month, day);
}

function getWeek(date) {
  var week = Utilities.formatDate(date, "GMT", "'Week'w");
  return week.replace('Week', '');
}

function getEvents() {
  var mapValuesToItem = function(values) {
    var date = getDay(values[0]);
    var week = getWeek(date);
    return {
      date: date,
      name: values[2],
      count: values[3],
      note: values[4],
      week: week
    };
  };
  return getData("Hændelser", mapValuesToItem);
}

function getGoals() {
  var mapValuesToItem = function(values) {
    return {
      periodType: values[0],
      period: values[1],
      name: values[2],
      note: values[3],
      target: values[4],
      sentCelebrationMail: values[5]
    };
  };
  return getData("Mål", mapValuesToItem);
}

function getData(sheetName, mapValuesToItem) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var range = sheet.getRange(2, 1, lastRow, lastColumn);
  var result = [];
  for (var i=1; i<=lastRow; i++) {
    cell = range.getCell(i,1);
    if (cell.isBlank())
      break;
    
    var values = [cell.getValue()];
    for (var j=2; j<=lastColumn; j++) {
      values.push(range.getCell(i,j).getValue());
    }
    
    var item = mapValuesToItem(values);
    result.push(item);
  }
  
  return result;
}