Google Sheets App Script 替代易出错公式打开表格自动填充空白值求助

Apps Script基于JS封装，你有其他平台JS开发经验可以直接上手，核心调用SpreadsheetApp类就能完成表格的所有操作，整体逻辑非常好理解。

你需求里提到单元格值一经设置就不会变动，因此不需要动态公式，直接把计算结果以静态值的形式写入单元格即可，完全不会出现原有公式排序、新增行后报错的问题。我们可以绑定「表格打开时」的触发器，每次打开自动扫一遍对应列的空白单元格，填充计算好的固定值。

• 打开目标表格，点击顶部菜单栏「扩展程序」->「Apps 脚本」进入代码编辑器
• 后续写好入口函数后，可以手动在编辑器左侧「触发器」栏添加触发规则：选择对应入口函数，事件来源选「电子表格」，事件类型选「打开时」即可，新手推荐手动配置，不容易出现权限冲突。

先封装一个获取工作表有效行数的工具函数，避免遍历大量空行浪费性能：

// 入参：工作表对象、判断是否为有效行的参考列，默认取第1列
function getLastRowWithData(sheet, refCol = 1) {
  const colValues = sheet.getRange(1, refCol, sheet.getLastRow(), 1).getValues();
  for (let i = colValues.length - 1; i >= 0; i--) {
    if (colValues[i][0] !== "") return i + 1;
  }
  return 1;
}

以下代码对应你给出的四个公式，列号和目标列可以根据你实际表格的布局自行调整：

function fillTransactionSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const transSheet = ss.getSheetByName("Transaction");
  const listSheet = ss.getSheetByName("Lists");
  
  // 提前把Lists表的映射关系存为对象，避免循环内反复查表提升性能
  const listAllValues = listSheet.getDataRange().getValues();
  const listMap = {};
  listAllValues.forEach(row => {
    if (row[0]) listMap[row[0]] = row[1];
  });

  // 按B列判断有效行，没有数据直接返回
  const lastValidRow = getLastRowWithData(transSheet, 2);
  if (lastValidRow < 2) return;

  // 批量获取需要用到的列数据，减少IO操作
  const colBValues = transSheet.getRange(2, 2, lastValidRow - 1, 1).getValues(); // B列日期
  const colEValues = transSheet.getRange(2, 5, lastValidRow - 1, 1).getValues(); // E列金额
  const colDValues = transSheet.getRange(2, 4, lastValidRow - 1, 1).getValues(); // D列关联匹配值

  // 初始化四个公式对应列的结果数组
  const monthRes = []; // 对应FILTER(text(B2:B,"MMMM"),Not(ISBLANK(B2:B)))
  const yearRes = []; // 对应FILTER(text(B2:B,"YYYY"),Not(ISBLANK(B2:B)))
  const typeRes = []; // 对应filter(if(E2:E < 0,"debit","credit"),not(ISBLANK(E2:E)))
  const lookupRes = []; // 对应filter(VLOOKUP(D2:D,Lists!A:B,2,false),not(ISBLANK(B2:B)))

  // 循环逐行计算
  for (let i = 0; i < lastValidRow - 1; i++) {
    const date = colBValues[i][0];
    // 计算月份、年份
    if (date) {
      const dateObj = new Date(date);
      monthRes.push([dateObj.toLocaleDateString('en-US', { month: 'long' })]);
      yearRes.push([dateObj.getFullYear()]);
    } else {
      monthRes.push([""]);
      yearRes.push([""]);
    }
    // 计算收支类型
    const amount = colEValues[i][0];
    typeRes.push([amount !== "" ? (amount < 0 ? "debit" : "credit") : ""]);
    // 匹配Lists表的值
    const matchKey = colDValues[i][0];
    lookupRes.push([listMap[matchKey] || ""]);
  }

  // 批量写入结果，这里假设四个结果分别存在C、D、F、G列，可自行调整列号
  transSheet.getRange(2, 3, lastValidRow - 1, 1).setValues(monthRes);
  transSheet.getRange(2, 4, lastValidRow - 1, 1).setValues(yearRes);
  transSheet.getRange(2, 6, lastValidRow - 1, 1).setValues(typeRes);
  transSheet.getRange(2, 7, lastValidRow - 1, 1).setValues(lookupRes);
}

如果需要只填充空白单元格，只需要在写入前先读取目标列的现有值，判断为空的位置再替换对应结果数组的值即可。

以下代码对应你给出的三个公式，列号可自行调整：

function fillBalanceHistorySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const balanceSheet = ss.getSheetByName("BalanceHistory");
  
  // 按B列判断有效行
  const lastValidRow = getLastRowWithData(balanceSheet, 2);
  if (lastValidRow < 2) return;

  // 批量获取B列日期、C列时间
  const colBValues = balanceSheet.getRange(2, 2, lastValidRow - 1, 1).getValues();
  const colCValues = balanceSheet.getRange(2, 3, lastValidRow - 1, 1).getValues();

  // 初始化结果数组
  const monthRes = []; // 对应FILTER(text(B2:B,"MMMM"),Not(ISBLANK(B2:B)))
  const yearRes = []; // 对应FILTER(text(B2:B,"YYYY"),Not(ISBLANK(B2:B)))
  const dateTimeRes = [["Date/Time"]]; // 对应ARRAYFORMULA({"Date/Time"; B2:B+C2:C})，自带表头

  // 循环逐行计算
  for (let i = 0; i < lastValidRow - 1; i++) {
    const date = colBValues[i][0];
    if (date) {
      const dateObj = new Date(date);
      monthRes.push([dateObj.toLocaleDateString('en-US', { month: 'long' })]);
      yearRes.push([dateObj.getFullYear()]);
      // 合并日期和时间
      const time = colCValues[i][0];
      const fullDateTime = time ? new Date(dateObj.getTime() + new Date(time).getTime()) : dateObj;
      dateTimeRes.push([fullDateTime]);
    } else {
      monthRes.push([""]);
      yearRes.push([""]);
      dateTimeRes.push([""]);
    }
  }

  // 批量写入结果，假设三个结果分别存在C、D、E列，可自行调整
  balanceSheet.getRange(2, 3, lastValidRow - 1, 1).setValues(monthRes);
  balanceSheet.getRange(2, 4, lastValidRow - 1, 1).setValues(yearRes);
  balanceSheet.getRange(1, 5, lastValidRow, 1).setValues(dateTimeRes);
}

把两个工作表的填充逻辑封装到统一的入口函数，绑定到「打开表格」的触发器上即可：

function onOpen() {
  fillTransactionSheet();
  fillBalanceHistorySheet();
}

• 第一次运行代码需要授权，遇到「未验证的应用」提示时，点击「高级」->「继续访问」即可完成授权
• 所有列号、工作表名如果和你实际表格布局不一致，直接修改对应参数即可适配
• 批量读写是Apps Script的核心优化规则，不要在循环内反复调用getRange/setValues，会严重拖慢运行速度

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