revize.js

var DOCUMENT_ID = "TODO: Paste your document ID (extract it from spreadsheet URL)";
var EMAIL_RECIPIENT = "TODO1@example.com";
var EMAIL_BCC = "TODO2@example.com";
var DAYS_BEFORE = 10;

var FIRST_ROW = 3;
var FIRST_COL = 1;

var doc = SpreadsheetApp.openById(DOCUMENT_ID);
var sheet = doc.getSheets()[0];

var EMAIL_SUBJECT = doc.getName() + ": upozornění";

function sendNotification() {
  var dataRange = sheet.getRange(FIRST_ROW, FIRST_COL, sheet.getLastRow()-1, sheet.getLastColumn())
  var now = new Date();
  
  var message = "";
  var data = dataRange.getValues();
  var now = new Date();
  var lines = [];
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var place = row[1] + ' - ' + row[2];
    
    var ts1 = row[5];
    var alarmDate1 = '-';
    if (ts1.getTime) {
      alarmDate1 = getRelativeDate(new Date(ts1.getTime()), -DAYS_BEFORE);
      if (isSameDay(alarmDate1, now)) {
        lines.push(formatLine(place, 'Revize el. zařízení', row[3], row[4]));
      }
      alarmDate1 = formatDate(alarmDate1);
    }

    var ts2 = row[8];
    var alarmDate2 = '-';
    if (ts2.getTime) {
      alarmDate2 = getRelativeDate(new Date(ts2.getTime()), -DAYS_BEFORE);
      if (isSameDay(alarmDate2, now)) {
        lines.push(formatLine(place, 'Revize hromosvodů a LPS', row[6], row[7]));
      }
      alarmDate2 = formatDate(alarmDate2);
    }
    
    Logger.log("%s, Upozorni zarizeni: %s, Upozorni hromosvody: %s", place, alarmDate1, alarmDate2);
  }

  Logger.log("Zaznamu: %s", lines.length);
  Logger.log(lines);
  
  if (lines.length > 0) {
    var perex = 'Za ' + DAYS_BEFORE + ' dní vyprší tyto revize:';
    var footer = "Dokument: " + doc.getName() + " - " + doc.getUrl();
    var message = perex + "\n\n" + lines.join("\n") + "\n\n" + footer;
    var htmlMessage = perex + "<ul><li>" + lines.join("</li><li>") + "</li></ul>" + "<br/>" + footer;
    
    MailApp.sendEmail(EMAIL_RECIPIENT, EMAIL_SUBJECT, message, {
      bcc: EMAIL_BCC,
      htmlBody: htmlMessage
    });  
  }  
}

function getRelativeDate(date, daysOffset) {
  var newDate = new Date(date);
  newDate.setDate(newDate.getDate() + daysOffset);
  return newDate;
}

function isSameDay(d1, d2) {
  return d1.getDate() == d2.getDate() && d1.getMonth() == d2.getMonth() && d1.getYear() == d2.getYear();
}

function formatDate(date) {
  return date.getDate() + ". " + (date.getMonth() + 1) + ". " + date.getYear();
}

function formatRowAsDate(row) {
  if (row.getTime) {
    var d = new Date(row.getTime());
    return formatDate(d);
  }
  return '-';
}

function formatLine(place, desc, revNo, revDate) {
  return place + ", " + desc + " - " + revNo + ", z " + formatRowAsDate(revDate);
}