如何用Google Apps Script实现Google Sheets过期日期自动更新状态？

原脚本问题分析 #

• 直接取整列G:G和H:H，包含大量空行，循环冗余且易触发错误
• status.setValue('Update Status')会将整个H列设为相同值，而非对应过期行
• 未处理空单元格或非日期类型的情况，会导致getTime()方法报错
• 未考虑单元格日期格式为文本的情况（需确保G列单元格是日期格式，而非纯文本）

修正后的脚本 #

function updateStatus() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('February 2023');
  if (!sheet) { // 检查工作表是否存在
    Logger.log('未找到指定工作表');
    return;
  }
  
  // 获取有数据的范围（避免空行）
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) { // 假设第1行是表头
    Logger.log('无数据行需要处理');
    return;
  }
  
  var dateRange = sheet.getRange(2, 7, lastRow - 1, 1); // G2到G最后一行
  var statusRange = sheet.getRange(2, 8, lastRow - 1, 1); // H2到H最后一行
  var endDates = dateRange.getValues();
  var statusValues = statusRange.getValues(); // 先获取现有状态，避免覆盖不需要修改的行
  var dayMs = 24 * 3600 * 1000;
  var today = parseInt(new Date().setHours(0, 0, 0, 0) / dayMs);
  
  // 遍历每一行数据
  for (var i = 0; i < endDates.length; i++) {
    var cellValue = endDates[i][0];
    // 跳过空值或非日期类型的单元格
    if (typeof cellValue !== 'object' || !(cellValue instanceof Date)) {
      continue;
    }
    
    var dateDay = parseInt(cellValue.getTime() / dayMs);
    // 仅当日期过期时更新状态
    if (dateDay < today) {
      statusValues[i][0] = 'Update Status';
    }
  }
  
  // 一次性写入所有修改，提升效率
  statusRange.setValues(statusValues);
  Logger.log('状态更新完成');
}

关键优化点 #

• 仅处理有数据的行，避免空行循环
• 先获取所有状态值，修改后一次性写入，减少Google Apps Script的API调用次数（提升性能）
• 增加工作表存在检查和数据行判断，避免无意义执行
• 跳过空值或非日期单元格，防止脚本报错
• 针对对应行单独修改状态，而非整列覆盖

额外注意事项 #

• 确保G列单元格的格式是日期类型，而非纯文本（可通过格式菜单设置为日期）
• 可通过脚本编辑器的「运行」按钮测试，或设置时间触发器（比如每天自动执行）

内容的提问来源于stack exchange，提问作者Michelle Schnitzmeier