I have a spreadsheet with two sheets that look like this:

I'm trying to write a script that does the following:

• Look in the date column for a value that matches today's date
• If a match is found, look under the different names in that row for a blank cell with a white color background (#ffffff)
• If a match is found, send an email to the manager of that particular name (e.g., in B2, Jack has a white cell, so send an email to Jack's manager ([email protected]; pulled up from the 'Contact' sheet)

Currently I have the following code:

function sendEmail3() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Main');
  const data = sheet.getRange(2,1,27,7).getValues();

// console.log(data);

 data.forEach(row => {
const sysDate = new Date();
  sysDate.setHours(0);
  sysDate.setMinutes(0);
  sysDate.setSeconds(0); 

const sheetDate = new Date(row[0])

// format both system date and sheet date so that they are identical strings
const sysDateFormatted = Utilities.formatDate(sysDate, "GMT-0700", "MM/dd/yyyy");
const sheetDateFormatted = Utilities.formatDate(sheetDate, "GMT-0700", "MM/dd/yyyy");

// a simple if statement with a console log. This needs to be replaced
// if the dates match, then look in that row for blank cells with white backgrounds (#ffffff)
 if (sysDateFormatted == sheetDateFormatted) {
  console.log("They are equal");
} else {
  console.log("they are not equal");
}

// I tried the following and was able to get the backgrounds for each cell in this range
const specificData = sheet.getRange(2,2,999,6) // this range doesn't include the headers
const dataColor = specificData.getBackgrounds();
console.log(dataColor);

})
}

I can't seem to put all the pieces together. If anyone could help me sort out the logic for this one I'd greatly appreciate it! Thanks!

CodePudding user response：

• do not include the variables in loop that is not changing
• dates got by getValue/getValues are already date object
• formatDate with MM/dd/yyyy is not referring to the time so no need to setHours/setMinutes/setSeconds

function sendEmail3() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Main');
  const range = sheet.getDataRange();
  const data = range.getValues();
  const dataColor = range.getBackgrounds();

  const contact = ss.getSheetByName('Contact').getDataRange().getValues();
  const manager = {};
  for (const row of contact) {
    manager[row[0]] = {
      name: row[1],
      email: row[2]
    };
  }

  const sysDate = new Date();
  const sysDateFormatted = Utilities.formatDate(sysDate, "GMT-0700", "MM/dd/yyyy");

  for (let i = 1; i < data.length; i  ) {
    const sheetDate = data[i][0];
    const sheetDateFormatted = Utilities.formatDate(sheetDate, "GMT-0700", "MM/dd/yyyy");

    if (sysDateFormatted === sheetDateFormatted) {
      for (let j = 1; j < data[0].length; j  ) {
        if (dataColor[i][j] === '#ffffff') {
          const name = data[0][j];
          sendMail(name);
        }
      }
      break;
    }
  }

  function sendMail(name) {
    const subject = '...';
    const body = `...${name}...`;
    GmailApp.sendEmail(manager[name].email, subject, body);
  }
}