如何在Google Apps Script中添加红色单元格检测以终止脚本运行？

问题背景 #

现有一个可正常运行的Google Apps Script（Email8函数），用于发送带PDF、CSV附件的邮件。但员工常忽略标记缺失数据的红色单元格就执行脚本，需要添加代码检测指定范围（如A1:K90）内的红色单元格，若存在则终止脚本运行。

修改后的完整脚本 #

function Email8() {
  // 检测指定范围是否存在红色单元格，存在则终止脚本
  const hasRedCells = checkForRedCells("S/A - Darrin", "A1:K90");
  if (hasRedCells) {
    throw new Error("检测到红色标记单元格，请补全缺失数据后再执行脚本！");
  }

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("G9:H10");
  var emailAddress = emailRange.getValue();
  var subject = "S/A - Darrin";
  var body = "Thank you for your business.";

  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  var shID = getSheetID("S/A - Darrin");
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("F5:G8").getDisplayValues().flat().join(",");
  var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A21").getDisplayValue();
  MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)], bcc });

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  var email_ID1 = "jane@company.com";
  var subject = "CTC Sales Agreement.";
  var body = "Attached is your document. \n Thank you for your business";

  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  var shID = getSheetID("Xero Invoice - Darrin");
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=csv&id="+ssID+"&gid="+shID;

  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  MailApp.sendEmail (email_ID1, subject ,body, {attachments:[{fileName:sheetName+".csv", content:contents, mimeType:"application/csv"}]});
};

function getSheetID(name){
  var ss = SpreadsheetApp.getActive().getSheetByName(name);
  var sheetID = ss.getSheetId().toString();
  return sheetID;
}

// 检测指定工作表和范围是否存在红色背景单元格
function checkForRedCells(sheetName, rangeStr) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(rangeStr);
  const backgrounds = range.getBackgrounds();
  
  // 遍历所有单元格背景色，匹配红色（可根据实际红色值调整，这里用标准红色#ff0000）
  for (let row of backgrounds) {
    for (let color of row) {
      if (color === "#ff0000") {
        return true;
      }
    }
  }
  return false;
}

关键修改说明 #

• 添加了checkForRedCells函数：接收工作表名称和范围字符串，获取该范围所有单元格的背景色，遍历检测是否有标准红色（#ff0000）单元格，存在则返回true。
• 在Email8函数开头调用检测函数：若检测到红色单元格，直接抛出错误终止脚本，阻止后续邮件发送操作。
• 注意：如果你的红色单元格背景色不是标准的#ff0000，可以在checkForRedCells函数中替换为实际的颜色代码（可通过单元格背景色复制获取）。

内容的提问来源于stack exchange，提问作者V Allan