ArrayFormula中自定义数字格式不显示，疑与Apps Script有关？

我用Google Apps Script写了onEdit函数，当下拉菜单被编辑时修改指定区域的数字格式，手动输入内容时正常，但ArrayFormula生成的数组内容完全不显示自定义格式。已经尝试过：

• 添加条件确保数字被识别为数值类型
• 将ArrayFormula作用区域设为自动数字格式
• 手动输入带自定义格式的内容可正常显示
  但数组内容随下拉选项动态变化，不知道怎么让脚本适配这些动态位置。

核心原因 #

onEdit触发器仅响应用户手动编辑单元格的操作，ArrayFormula的计算更新属于公式自动触发的内容变化，不会触发onEdit；同时ArrayFormula的有效数据范围是动态的，固定区域的格式设置会失效。

具体解决步骤 #

1. 改用onChange触发器
   这个触发器能捕获包括公式更新在内的编辑类变化，需要手动创建触发器（不能像onEdit那样自动生效）。

2. 动态定位ArrayFormula的有效数据范围
   不要用固定区域，而是根据ArrayFormula所在单元格，获取它实际填充的非空数据范围。

3. 针对性应用格式
   遍历动态范围，仅对数值类型的单元格设置自定义格式，避免影响文本或空白单元格。

修改后的示例代码 #

// 先手动运行此函数创建onChange触发器
function setupFormatTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('applyDynamicNumberFormat')
    .forSpreadsheet(ss)
    .onChange()
    .create();
}

function applyDynamicNumberFormat(e) {
  // 只处理编辑类变化（包含公式更新）
  if (e.changeType !== 'EDIT') return;

  const sheet = SpreadsheetApp.getActiveSheet();
  // 替换为你的下拉菜单单元格位置
  const dropdownCell = sheet.getRange("A1");
  const selectedOption = dropdownCell.getValue();

  // 替换为你的ArrayFormula所在单元格位置
  const arrayFormulaAnchor = sheet.getRange("B2");
  const startRow = arrayFormulaAnchor.getRow();
  const startCol = arrayFormulaAnchor.getColumn();

  // 动态获取ArrayFormula填充的最后一行
  const lastRow = sheet.getLastRow();
  if (lastRow < startRow) return; // 无数据时直接退出

  const dataRange = sheet.getRange(startRow, startCol, lastRow - startRow + 1, 1);
  const values = dataRange.getValues();

  // 根据下拉选项定义对应格式
  let targetFormat = "0.00";
  switch(selectedOption) {
    case "百分比":
      targetFormat = "0.00%";
      break;
    case "人民币":
      targetFormat = "¥#,##0.00";
      break;
    case "整数":
      targetFormat = "0";
      break;
    // 可添加更多格式选项
  }

  // 生成格式数组：仅数值单元格应用目标格式，其余用文本格式
  const formatArray = values.map(row => {
    return typeof row[0] === 'number' ? [targetFormat] : ["@"];
  });

  // 批量应用格式
  dataRange.setNumberFormats(formatArray);
}

额外注意事项 #

• 如果ArrayFormula的位置不固定，可以用文本查找定位：

  const arrayFormulaCell = sheet.createTextFinder("ArrayFormula").findNext();
  if (!arrayFormulaCell) return; // 未找到时退出
  

• 批量设置格式（setNumberFormats）比单个单元格设置更高效，避免触发脚本执行时间限制。
• 测试时修改下拉选项后，可能需要等待几秒让公式更新，触发器才会触发格式设置。

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