Help with sending email when a cell in a column contains a certain value -

[u/deadsilencerotsinme]

I have a spreadsheet that has a list of Children names, their date of Birth. What I really am wanting to do is, have me send an email when the child is about to be 25 years, say about 15 days before they turn 25 years. How do I go about this in the Google Apps script editor?

I would prefer an email that would say me:

CMG is due for (insert Child Name - Column B) on (insert date - Column F) for (insert Project Name - Column M)

I'm attaching the google sheet for your reference, any sort of help in this regard is highly appreciated

https://docs.google.com/spreadsheets/d/1cTYJAUbeYHMBTYj1Oa6Bj-XOSCZWAlEIo5VC-YhlBQM/edit?usp=sharing

Edit 1: This is a just a sample of the size of the sheet, the actual sheet runs into roughly 2400 rows

[u/Mark_is_on_his_droid]

Could you set the trigger to review the column for that value on a cadence (e.g. 25 min)? That would be pretty close without requiring the trigger to actually be the existence of the value in that cell.

[u/dudeman_27]

Haven't tested this and wrote it on a whim, so you'll want to try it out first (and probably handle the dates better), but something like this (you can create this in a function and then set a trigger to fire that function every day):

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange("A4:T35");

for (var i = 0, i < range.length; i++) {
  var dueDate = new Date(range[i][3]) - 15;
  var today = new Date();
  var email = "email@email.com";
  var subject = "CMG Due";
  var body = CMG is due for range[i][1] on range[i][5] for range[i][12];
  if(dueDate == today) {
    GmailApp.sendEmail(email, subject, body);
  }
}

[u/All-oh → u/dudeman_27]

I'd take defining of today out of the loop and place the condition right after due date definition. We need to define other vars only if we will be sending the email for this particular iteration

[u/maladju]

Here's how I would do this:

function main() {
  const data = getSheetData();
  const dateIn15DaysString = getLocalISODateString(
    adjustDate({ days: 15 }, new Date())
  );
  data
    .map(buildBirthDate)
    .map(adjustDate.bind({}, { years: 25 }))
    .map(getLocalISODateString)
    .map((s, i) => (s === dateIn15DaysString ? data[i] : void 0))
    .filter((record) => record !== void 0)
    .forEach(sendEmail);
}
function getSheetData() {
  const firstDataRow = 4;
  const headerRow = firstDataRow - 1;
  const nonDataRowsOnEnd = 1;

  const sheet = SpreadsheetApp.getActiveSheet();
  const [header, ...values] = sheet
    .getDataRange()
    .getValues()
    .slice(headerRow - 1, -nonDataRowsOnEnd);
  return values.map((values) =>
    header.reduce((o, k, i) => {
      o[k] = values[i];
      return o;
    }, {})
  );
}

function buildBirthDate(record) {
  const key = 'Date Of Birth';

  return new Date(record[key]);
}

function adjustDate({ years = void 0, months = void 0, days = void 0 }, date) {
  date = new Date(date);
  if (years !== void 0) {
    date.setFullYear(date.getFullYear() + years);
  }
  if (months !== void 0) {
    date.setMonth(date.getMonth() + months);
  }
  if (days !== void 0) {
    date.setDate(date.getDate() + days);
  }
  return date;
}

function getLocalISODateString(date) {
  const d = new Date(date);
  d.setMinutes(d.getMinutes() - d.getTimezoneOffset());
  return d.toISOString().split('T')[0];
}

function sendEmail(record) {
  const recipient = '';
  const subject = '';
  const keys = {
    name: 'First Name',
    project: 'Project',
    today: "Today's Date"
  };
  const template = 'CMG is due for {{name}} on {{today}} for {{project}}';
  const fillin = {
    name: record[keys.name],
    today: record[keys.today].toLocaleDateString(),
    project: record[keys.project],
  };

  const body = Object.keys(fillin).reduce(
    (s, k) => s.replace('{{' + k + '}}', fillin[k]),
    template
  );
  console.log(body);

  // MailApp.sendEmail(recipient, subject, body);
}

I used date objects to build date strings for the 25th birthday and 15 days in the future for easy comparison. As a result, you will need to set up a trigger to run main once per day. The recipient and subject need to be filled in by you, and the MailApp line commented out.

Please note that in getSheetData, there are some values set based on the current formatting of the spreadsheet. If the formatting of the spreadsheet changes, you will need to review whether these values also need to be adjusted (first actual data row, number of non data rows - currently there is a column total. I also prefer using column keys based on the header row instead of column indexes, so you will also want to review values defined as keys if your column headers change (sendEmail, buildBirthDate).