如何在Google Sheets中通过条件格式根据日期自动更改单元格文本值

首先明确：条件格式仅支持修改单元格样式，无法修改单元格内的文本值，因此需要使用谷歌表格内置的Apps Script实现需求，该方案可完全匹配你提到的三个使用场景。

步骤1：提前配置E列下拉菜单 #

• 选中E列所有需要设置的行（排除表头行），点击顶部菜单栏「数据」→「数据验证」，规则选择「下拉列表（手动输入）/下拉列表（从范围）」，填入你需要的所有状态选项（包含Offline），保存后即可正常手动切换包裹状态，状态变更会自动同步到所有引用E列内容的「规划概览」标签页。

步骤2：添加自动更新脚本 #

• 点击表格顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑器，删除默认的空白代码，粘贴下方代码：

function onOpen() {
  // 打开表格时自动触发一次状态校验
  checkOfflineStatus();
  // 新增自定义菜单，支持手动触发状态更新
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('自定义工具')
    .addItem('立即更新所有包裹状态', 'checkOfflineStatus')
    .addToUi();
}

function onEdit(e) {
  // 手动修改表格内容后自动触发校验，可根据需要注释掉该函数
  checkOfflineStatus();
}

function checkOfflineStatus() {
  // 如需指定固定工作表，把下方getActiveSheet()改为getSheetByName('你的工作表名称')
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  // 假设第1行为表头，G列是第7列（预设下线日期）、E列是第5列（包裹状态），从第2行开始校验
  const dateRange = sheet.getRange(2, 7, lastRow - 1, 1);
  const dates = dateRange.getValues();
  const statusRange = sheet.getRange(2, 5, lastRow - 1, 1);
  const statuses = statusRange.getValues();
  const today = new Date();
  today.setHours(0, 0, 0, 0); // 清零时间部分，仅对比日期

  for (let i = 0; i < dates.length; i++) {
    const offlineDate = new Date(dates[i][0]);
    offlineDate.setHours(0, 0, 0, 0);
    // 仅当预设下线日期合法、且当前日期大于等于下线日期时，修改状态为Offline
    if (!isNaN(offlineDate.getTime()) && offlineDate <= today) {
      statuses[i][0] = 'Offline';
    }
  }
  // 批量写入状态，降低性能损耗
  statusRange.setValues(statuses);
}

步骤3：配置定时触发器 #

• 在脚本编辑页左侧点击「触发器」→「添加触发器」，选择checkOfflineStatus函数，事件源选择「时间驱动」，频率设置为「每天」，选择你需要的触发时段，保存后按照系统提示授权脚本访问表格的权限即可。

兼容说明 #

• E列手动修改状态的功能完全保留，未达到下线日期的行状态不会被脚本强制修改
• 状态变更为单元格原生值变更，所有引用E列的标签页、公式都会自动同步最新状态

内容的提问来源于stack exchange，提问作者Sami Chouchane