Google Apps Script操作Google Sheet遇过滤器列越界及写入失败问题求助

问题1：过滤器激活或多人协作时脚本无法写入/更新 #

优化方案 #

• 用脚本锁服务替代范围保护，避免多人协作冲突：

  var lock = LockService.getScriptLock();
  try {
    // 等待最多30秒获取锁，超时则放弃
    lock.waitLock(30000);
    // 在这里执行你的写入/更新逻辑
  } finally {
    // 无论成功失败都释放锁
    lock.releaseLock();
  }
  

• 操作数据时直接访问完整数据范围，忽略过滤器隐藏行：
  别依赖getActiveRange()或可视行，改用getDataRange()获取所有数据（包括隐藏行），通过唯一标识遍历匹配目标行：

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的表名");
  var data = sheet.getDataRange().getValues();
  var targetRow = -1;
  var uniqueId = "用户提交的唯一标识";
  
  // 从第二行开始遍历（假设第一行是表头）
  for(var i=1; i<data.length; i++){
    if(data[i][0] === uniqueId){ // 假设唯一标识在第一列
      targetRow = i+1; // 转换为Sheet的行号（从1开始计数）
      break;
    }
  }
  
  // 执行更新或追加操作
  if(targetRow !== -1){
    sheet.getRange(targetRow, 2).setValue("更新内容"); // 示例：更新该行第二列
  } else {
    sheet.appendRow([uniqueId, "新内容1", "新内容2"]);
  }
  

问题2：保存过滤器规则时触发列越界异常 #

问题根源 #

getColumnFilterCriteria(columnIndex)的参数是从1开始的列索引，如果传入的索引小于1或超过Sheet最大列数，就会抛出异常；另外，部分列可能没设置过滤规则，直接调用copy()会触发null错误。

修复方案 #

• 先校验列索引有效性，只处理有过滤规则的列：

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的表名");
  var defaultFilter = sheet.getFilter();
  if(!defaultFilter) return; // 没有过滤器直接跳过
  
  var maxColumns = sheet.getMaxColumns();
  var filterRules = {};
  // 只遍历已设置过滤规则的列ID
  var filteredColumnIds = defaultFilter.getColumnFilterCriteriaIds();
  
  filteredColumnIds.forEach(function(colId){
    if(colId >=1 && colId <= maxColumns){
      var criteria = defaultFilter.getColumnFilterCriteria(colId).copy().build();
      filterRules[colId] = criteria;
    }
  });
  
  // 移除现有过滤器
  defaultFilter.remove();
  
  // 执行你的数据写入/更新操作...
  
  // 恢复过滤器规则
  if(Object.keys(filterRules).length > 0){
    var newFilter = sheet.getRange(1, 1, sheet.getLastRow(), maxColumns).createFilter();
    Object.keys(filterRules).forEach(function(colId){
      newFilter.setColumnFilterCriteria(parseInt(colId), filterRules[colId]);
    });
  }
  

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