基于下拉框值修改提醒单元格背景色的Apps Script问题修复

问题 #

我有一个Google表格，包含以下元素：

• 名为“提醒状态”的下拉单元格，选项值如Reminder 1 sent（支持任意数量提醒项）
• 多个名为reminder 1、2、3……n的提醒单元格
• “暂停日期”单元格

目前提醒单元格已实现根据暂停日期自动生成每两周后的日期，功能正常。需求是：根据下拉框选中值，对应修改提醒单元格背景色——选中Reminder 1 sent时，reminder 1单元格设为绿色；选中Reminder 2 sent时，reminder 2单元格设为绿色，以此类推。

现有Apps Script代码如下：

function fillReminders() {
  var app = SpreadsheetApp;

  var spreadSheet = app.getActiveSpreadsheet();
  
  var sheet = spreadSheet.getSheetByName("Sample");

  var suspensionDate = sheet.getRange(4, 4).getValue();
  var dropDownValue = sheet.getRange(4, 3).getValue();

  var email = sheet.getRange(1,1).getValue();
  
  var analystName = sheet.getRange(4,1).getValue();

  var caseId = sheet.getRange(4,2).getValue();

 // MailApp.sendEmail(email, analystName +" -" + caseId + " - Case Clinic Reminder", "Hi this is not working :D");


  if (suspensionDate) {
    var baseColumn = 5;
    var numberOfReminders = 6;

      for (var i = 1; i <= numberOfReminders; i++) {
        var reminder = new Date(suspensionDate);
        reminder.setDate(reminder.getDate() + (i - 1) * 14);
        var reminderCell = sheet.getRange(4, baseColumn + i - 1);

        var currentDate = new Date();

        if (reminder < currentDate) {
        var reminderStatus = "Reminder " + i + " sent";

        if (dropDownValue === reminderStatus) {
          reminderCell.setBackground("green");
          
        } else {
          reminderCell.setBackground("red");
        }
      } else {

        reminderCell.setBackground(null);
      }

        reminderCell.setValue(reminder);
        currentColumn++;
      }
    }
   else {
    sheet.getRange(4, 5, 1, sheet.getMaxColumns() - 4).clearContent();
  }
} 

但目前无论下拉框选择哪个值，只有reminder 1单元格的背景色会被修改，请问如何修复该问题，实现根据下拉框值动态更新对应提醒单元格的背景色？

问题分析 #

核心问题在于代码的逻辑判断范围：

• 只有当reminder日期小于当前日期时，才会触发背景色的判断逻辑
• 对于日期未过期的提醒单元格（reminder >= currentDate），直接将背景色设为null，覆盖了下拉框对应的颜色设置
• 通常只有第1个提醒的日期会早于当前日期，后续的提醒日期（每两周递增）都还未过期，所以只有reminder 1会执行颜色判断，其他单元格都被重置为默认背景

另外代码中currentColumn++属于未定义变量，会导致运行错误，需要删除。

解决方案 #

调整逻辑，优先根据下拉框值设置对应单元格的绿色背景，再针对过期的其他单元格设置红色，未过期的保持默认：

修改后的代码：

function fillReminders() {
  var app = SpreadsheetApp;
  var spreadSheet = app.getActiveSpreadsheet();
  var sheet = spreadSheet.getSheetByName("Sample");

  var suspensionDate = sheet.getRange(4, 4).getValue();
  var dropDownValue = sheet.getRange(4, 3).getValue();

  // 从下拉框值中提取对应的提醒编号（比如从"Reminder 2 sent"中得到2）
  var targetReminderIndex = dropDownValue ? parseInt(dropDownValue.match(/Reminder (\d+) sent/)[1]) : null;

  if (suspensionDate) {
    var baseColumn = 5;
    var numberOfReminders = 6;
    var currentDate = new Date();

    for (var i = 1; i <= numberOfReminders; i++) {
      var reminder = new Date(suspensionDate);
      reminder.setDate(reminder.getDate() + (i - 1) * 14);
      var reminderCell = sheet.getRange(4, baseColumn + i - 1);

      // 先判断是否是下拉框选中的目标提醒项，设置绿色背景
      if (targetReminderIndex === i) {
        reminderCell.setBackground("green");
      } else {
        // 非目标项：过期设红色，未过期设默认背景
        if (reminder < currentDate) {
          reminderCell.setBackground("red");
        } else {
          reminderCell.setBackground(null);
        }
      }

      reminderCell.setValue(reminder);
    }
  } else {
    sheet.getRange(4, 5, 1, sheet.getMaxColumns() - 4).clearContent();
  }
}

修改说明 #

1. 提取目标提醒编号：通过正则表达式从下拉框值中提取对应的数字，直接匹配循环中的i值，实现精准定位
2. 调整颜色判断顺序：优先处理下拉框选中的目标项，确保其始终显示绿色；非目标项再根据日期状态设置对应颜色
3. 删除无效代码：移除未定义的currentColumn++语句，避免运行报错
4. 优化变量作用域：将currentDate移到循环外，无需每次循环重新创建日期对象，提升执行效率

修改后，无论对应提醒日期是否过期，只要下拉框选中该项，就会将对应的单元格设置为绿色，其他单元格按照日期状态设置颜色，完全符合需求。

内容的提问来源于stack exchange，提问作者Elias Fares Swais