Google Apps Script：遍历U列日期清除30天前数据行问题排查

需求背景 #

现有appendRowsV函数用于向指定行追加数据，会在目标行的**U列（第21列）**写入当前日期。需要编写函数遍历U列，找出所有早于当前日期30天的记录，并将对应行的所有值设为null。

原追加数据函数：

function appendRowsV(sheet, data, optColumn) {
 if (!Array.isArray(data)) {
    data = [[data]];
  } else if (!Array.isArray(data[0])) {
    data = [data];
  }
  const rowStart = getNextArchiveV(sheet, optColumn);
  const columnStart = Number(optColumn) || 1;
  const numRows = data.length;
  const numColumns = data[0].length;
  const range = sheet.getRange(rowStart, columnStart, numRows, numColumns);
  range.setValues(data);
  var aDate = new Date();
  range.offset(0, 20, 1, 1).setValue(aDate).setNumberFormat("m/d/yy");
  return { range: range, rowStart: rowStart, columnStart: columnStart, numRows: numRows,  numColumns: numColumns };
}

用户尝试编写的hideRows函数：

function hideRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Archived Videos'); 
  var dateRange = sh.getRange(3, 20, 1, 1); 
  var dates = dateRange.getDisplayValues();
  var currentDate = new Date(); 

  for(var i = 20; i < dates.length; i++){
    var date = new Date(dates[i][0].replace(/-/g, '\/').replace(/T.+/, ''));
    if(date.valueOf() <= currentDate.valueOf()){  
      sh.getformulas(i+2);
      sh.setformulas();
    }
  }
  
}

错误排查 #

1. 范围获取错误：sh.getRange(3, 20, 1, 1)仅获取了第3行T列（第20列）的单个单元格，完全没拿到U列的日期数据，应该获取U列（第21列）从第3行到最后一行的所有数据范围。
2. 日期列索引错误：存储日期的是U列，对应索引为21，代码里误用了20（T列）。
3. 循环逻辑失效：dates是单个单元格的数组，长度为1，i=20的起始值远大于数组长度，循环根本不会执行；即使拿到正确数据，起始索引也应该从0开始。
4. 日期判断不符合需求：代码仅判断日期小于等于当前日期，未实现“早于当前日期30天”的逻辑；且用getDisplayValues()获取字符串日期再手动转换，容易出现格式错误，直接用getValues()获取日期对象更可靠。
5. 清空行的方法错误：getformulas和setformulas是语法错误（正确写法是getFormulas()和setFormulas()），且这种逐行操作公式的方式不符合“将对应行所有值设为null”的需求，效率也极低。

修正后的代码 #

function clearExpiredRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('Archived Videos');
  if (!sh) return; // 防止工作表不存在导致报错

  const currentDate = new Date();
  // 计算30天前的时间戳
  const thirtyDaysAgo = new Date(currentDate.getTime() - 30 * 24 * 60 * 60 * 1000);
  
  const lastRow = sh.getLastRow();
  if (lastRow < 3) return; // 没有数据行时直接返回
  
  // 获取U列（第21列）从第3行到最后一行的所有日期数据
  const dateRange = sh.getRange(3, 21, lastRow - 2, 1);
  const dates = dateRange.getValues();

  // 收集需要清空的行号
  const rowsToClear = [];
  dates.forEach((row, index) => {
    const cellDate = row[0];
    // 仅处理有效的日期对象，且日期早于30天前
    if (cellDate instanceof Date && !isNaN(cellDate.getTime()) && cellDate < thirtyDaysAgo) {
      rowsToClear.push(index + 3); // 数组索引从0开始，对应实际行号为index+3
    }
  });

  // 批量清空目标行内容
  rowsToClear.forEach(rowNum => {
    const targetRange = sh.getRange(rowNum, 1, 1, sh.getLastColumn());
    targetRange.clearContent(); // 清空内容，保留单元格格式；如需清空格式用clear()
    // 若需强制设为null，可替换为：
    // targetRange.setValues([new Array(sh.getLastColumn()).fill(null)]);
  });
}

内容的提问来源于stack exchange，提问作者Jon Beckner