Reminder cu 24 de ore înainte

Cum arată foaia

Instalare, cam 5 minute

1. Pune programările într-o foaie cu coloanele Name, Email, Date, Time, Reminder sent.
2. Lipește codul în Apps Script.
3. Adaugă un trigger de timp: sendAppointmentReminders, zilnic, dimineața.
4. Datele pot fi celule de tip dată sau text yyyy-MM-dd.
5. După trimitere, coloana Reminder sent primește data, ca să nu plece al doilea email.

/**
 * Emails bookings dated tomorrow that do not yet have "Reminder sent".
 * Add a time-driven trigger: sendAppointmentReminders, day timer, any morning hour.
 * Date cells may be real dates or yyyy-MM-dd text.
 */
function sendAppointmentReminders() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Bookings') || SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  if (data.length < 2) return;
  var headers = data[0].map(function (h) { return String(h).trim(); });
  var dateCol = headers.indexOf('Date');
  var emailCol = headers.indexOf('Email');
  var nameCol = headers.indexOf('Name');
  var sentCol = headers.indexOf('Reminder sent');
  if (dateCol < 0 || emailCol < 0 || nameCol < 0 || sentCol < 0) {
    throw new Error('Need columns Name, Email, Date, and Reminder sent.');
  }
  var tz = Session.getScriptTimeZone();
  var tomorrow = new Date();
  tomorrow.setDate(tomorrow.getDate() + 1);
  var target = Utilities.formatDate(tomorrow, tz, 'yyyy-MM-dd');
  for (var r = 1; r < data.length; r++) {
    var email = String(data[r][emailCol] || '').trim();
    var sent = String(data[r][sentCol] || '').trim();
    if (sent || email.indexOf('@') < 0) continue;
    if (asYmd_(data[r][dateCol], tz) !== target) continue;
    MailApp.sendEmail({
      to: email,
      subject: 'Reminder: appointment on ' + target,
      body: 'Hello ' + data[r][nameCol] + ',\n\nThis is a reminder for your appointment on ' + target + '.\n'
    });
    sheet.getRange(r + 1, sentCol + 1).setValue(new Date());
  }
}

function asYmd_(value, tz) {
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return Utilities.formatDate(value, tz, 'yyyy-MM-dd');
  }
  return String(value || '').trim().slice(0, 10);
}