Sending Email if a Cell in Range is Blank

Question

First time question asker, so please let me know if my question needs more details.

I am trying to find a way to send an email at 3:00 pm every weekday to the recipients in table B41:G51 if there are any blank cells in table D22:G35. I know that the best way to do this is to write a script. Unfortunately, my efforts in learning how to write scripts have been fruitless.

It would be nice if the time in column B that correlated with the row that has the blank cell was in would be in the subject of the email as well.

Can anyone help me write a script that would do this?

Example: https://docs.google.com/spreadsheets/d/1wkZ7fdGXWH6zBAivcTKpVXpYaOl5wrK47njtyVOxwhg/edit?usp=sharing

In the spreadsheet shared, cells D26:G26 are blank. I would like it if an email was sent to all email addresses in table B41:G51 with the subject of "Please fill out points for [Sheetname] [A21]" body text stating "There are not any points put in for [Sheetname] on [A21]. Please put in points for this time and date. This is an automated message. Please do not reply.

I have looked up a mailto function, but could not identify one. The only thing I can find is to write a script, which I have not been successful with writing.

Answer 1

Try this

function sendEmailIfBlankCellsExist() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("B21:G35");
  var recipientsRange = sheet.getRange("D43:G49"); // Change this range to match the merged cells
  var namesRange = sheet.getRange("C43:C49"); // Range containing names

  var today = new Date();
  var dayOfWeek = today.getDay(); // 0 = Sunday, 1 = Monday, ..., 6 = Saturday

  // Check if it's a weekday (Monday to Friday) and the time is 3:00 PM (15:00)
  if (dayOfWeek >= 1 && dayOfWeek <= 5) {
    var dataValues = dataRange.getValues();
    var isBlank = false;

    // Check if any cell in the data range is blank
    for (var i = 0; i < dataValues.length; i++) {
      for (var j = 0; j < dataValues[i].length; j++) {
        if (!dataValues[i][j]) {
          isBlank = true;
          break;
        }
      }
      if (isBlank) {
        break;
      }
    }

    // If there are blank cells, send the email
    if (isBlank) {
      var subject = "Missing points for " + sheet.getName() + " " + Utilities.formatDate(new Date(sheet.getRange("A21").getValue()), "GMT+0300", "MM/dd/yyyy");
      var recipients = recipientsRange.getValues();
      var names = namesRange.getValues();
      
      for (var i = 0; i < recipients.length; i++) {
        var recipient = recipients[i][0];
        var name = names[i][0];
        if (recipient) {
          var rowIndex = i + recipientsRange.getRow(); // Calculate the corresponding row
          var cell = sheet.getRange(rowIndex, recipientsRange.getColumn() + 4); // Offset by 4 columns
          var sheetURL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
          var tableHtml = createDataTable(dataValues, sheetURL); // Create the HTML table
          var body = "Dear " + name + ",<br><br>" +
            "There are no points recorded for " + sheet.getName() + " on " + Utilities.formatDate(new Date(sheet.getRange("A21").getValue()), "GMT+0300", "MM/dd/yyyy") + ".<br>" +
            "Please log the points for this session at your earliest convenience.<br><br>" +
            "Below is the data (missing points are highlighted in red):<br><br>" +
            tableHtml + // Include the HTML table
            "<br>Thank you,<br><br>" +
            "<font color='red'>Automated Message - Please Do not reply</font>"; // Make the message red

          // Send the email with HTML body
          MailApp.sendEmail({
            to: recipient,
            subject: subject,
            htmlBody: body // Use htmlBody instead of body
          });
          
          // Set "Successful Sent" in column H
          cell.setValue("Successful Sent");
        }
      }
    }
  }
}

// Function to create a well-formatted HTML table from a 2D array
// Function to create a well-formatted HTML table from a 2D array
function createDataTable(data, sheetURL) {
  var html = "<table style='border-collapse: collapse; width: 30%; border: 1px solid #b7b7b7;'>"; // Adjust the width here

  // Header row with background colors, text colors, and center alignment
  html += "<tr>";
  for (var j = 0; j < data[0].length; j++) {
    var headerBgColor = (j < 2) ? "#34a853" : "#741b47";
    var headerTextColor = "white";
    html += "<td style='background-color:" + headerBgColor + "; color:" + headerTextColor + "; border: 1px solid #b7b7b7; text-align:center; padding: 5px; white-space: nowrap;'>" + data[0][j] + "</td>";
  }
  html += "</tr>";

  // Data rows with background color for empty cells and center alignment for all columns
  for (var i = 1; i < data.length; i++) {
    html += "<tr>";
    for (var j = 0; j < data[i].length; j++) {
      var cellBgColor = data[i][j] ? "" : "#e8245e"; // Background color for empty cells
      var cellBorderStyle = "border: 1px solid #b7b7b7;";
      var cellContent = data[i][j];
      var cellAlignment = "center"; // Center align text for all columns
      if (j === data[i].length - 1 && cellContent === "Automated Message - Please Do not reply") {
        cellContent = "<font color='red'>" + cellContent + "</font>";
      }
      html += "<td style='background-color:" + cellBgColor + "; " + cellBorderStyle + " text-align:" + cellAlignment + "; padding: 5px; white-space: nowrap;'>" + cellContent + "</td>";
    }
    html += "</tr>";
  }

  html += "</table>";

  // Add the link below the table
  html += "<p>Go to the spreadsheet: <a href='" + sheetURL + "'>Link</a></p>";

  return html;
}