Google Sheets编辑触发自动筛选排序的Apps Script异常排查求助

现有代码核心问题 #

• 触发逻辑错误：公式计算导致的C列值变更不会触发onEdit事件，仅用户手动编辑单元格才会触发，当前设置的仅编辑C列触发完全匹配不到实际使用场景
• 公式未适配特殊值：ABS()函数遇到B列的"-"文本会返回#ERROR!错误，导致排序异常
• 缺少边界判断：表格仅存在表头时，getRange参数会出现负数抛出执行错误
• 无筛选适配逻辑：需求中要求的先应用筛选再排序没有对应实现

修正后完整代码 #

// 刷新C列公式，适配"-"文本场景
function refreshFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Trustlines");
  const lastRow = ws.getLastRow();
  if (lastRow < 2) return;
  // 可自定义B列为"-"时的默认值，改为-999则对应行默认排在最后
  const formula = '=IF(B2="-",0,ABS(B2))';
  ws.getRange("C2").setFormula(formula);
  const fillRange = ws.getRange(2, 3, lastRow - 1);
  ws.getRange("C2").copyTo(fillRange);
  SpreadsheetApp.flush();
}

// 自动排序+保留原有筛选规则
function autoSortWithFilter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Trustlines");
  const lastRow = ws.getLastRow();
  if (lastRow < 2) return;
  const dataRange = ws.getRange(2, 1, lastRow - 1, 3);

  // 暂存当前筛选状态
  const existingFilter = ws.getFilter();
  let filterCriteria = null;
  if (existingFilter) {
    filterCriteria = existingFilter.getColumnFilterCriteria(3);
    existingFilter.remove();
  }

  // 按C列降序排序
  dataRange.sort({column: 3, ascending: false});

  // 还原筛选设置
  if (filterCriteria) {
    const newFilter = ws.getRange(1, 1, lastRow, 3).createFilter();
    newFilter.setColumnFilterCriteria(3, filterCriteria);
  }
}

// 编辑触发逻辑调整
function onEdit(e) {
  const row = e.range.getRow();
  const column = e.range.getColumn();
  const ws = e.source.getActiveSheet();
  // 仅在Trustlines表编辑B列2行及以上内容时触发流程
  if (ws.getName() !== "Trustlines" || column !== 2 || row < 2) return;
  refreshFormulas();
  autoSortWithFilter();
}

使用说明 #

替换原有Apps Script全部代码后保存项目，首次手动运行refreshFormulas函数完成权限授权，后续无需手动操作，编辑B列内容时会自动更新C列数值、保留筛选规则并完成排序。

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