如何在Google Apps Script中获取不含ArrayFormula溢出数据的表格值与公式

阿华AIGC实验室

2026-4-23

我完全理解你的困扰——用getFormulas()和getValues()合并的方法，碰到ArrayFormula的溢出值就会翻车，因为溢出的自动值会被当成普通手动输入的值，粘贴后直接挡住ArrayFormula的扩展空间。而且解析公式判断是否是ArrayFormula再处理溢出范围，确实会拖慢脚本，完全不实用。

这里有个更高效的方案，不用解析任何公式，直接通过Google Sheets Advanced Service获取仅用户手动输入的内容（公式或值），自动排除ArrayFormula的溢出结果：

核心思路

Google Sheets的API里，每个单元格的内容分为两类：

userEnteredValue/userEnteredFormula：用户手动输入的值或公式（包括你要保留的ArrayFormula本身）
effectiveValue：单元格实际显示的值（包括ArrayFormula溢出的自动计算值）

我们只需要调用API获取前两类内容，就能得到干净的、不含溢出值的数据集。

步骤1：启用Google Sheets Advanced Service

首先得在脚本编辑器里开启这个服务：

打开你的Google表格，点击「扩展」→「Apps Script」进入脚本编辑器
在左侧菜单点击「服务」（如果看不到，点击右上角的齿轮图标→「设置」，勾选「显示“服务”菜单」）
找到「Google Sheets API」，点击「启用」

步骤2：编写脚本实现功能

下面是完整的示例代码，包含获取用户输入数据、复制到目标表的逻辑：

// 获取指定工作表范围中，仅用户手动输入的公式/值
function getUserEnteredData(sheetId, rangeA1) {
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  
  // 调用Sheets API，仅请求用户手动输入的内容
  const response = Sheets.Spreadsheets.get(ssId, {
    ranges: [`${sheetId}!${rangeA1}`],
    fields: 'sheets(data(rowData(values(userEnteredValue,userEnteredFormula))))'
  });
  
  const result = [];
  const rowData = response.sheets[0].data[0].rowData || [];
  
  rowData.forEach(row => {
    const currentRow = [];
    const cellValues = row.values || [];
    
    cellValues.forEach(cell => {
      if (cell.userEnteredFormula) {
        // 优先保留手动输入的公式（包括ArrayFormula）
        currentRow.push(cell.userEnteredFormula);
      } else if (cell.userEnteredValue) {
        // 处理不同类型的手动输入值（数字、文本、布尔等）
        const inputVal = cell.userEnteredValue;
        currentRow.push(inputVal.numberValue || inputVal.stringValue || inputVal.boolValue || '');
      } else {
        // 没有用户手动输入，留空（对应ArrayFormula的溢出单元格）
        currentRow.push('');
      }
    });
    
    result.push(currentRow);
  });
  
  return result;
}

// 复制源表数据到目标表（不含ArrayFormula溢出值）
function copyCleanData() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('源表');
  const destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('目标表');
  
  // 替换成你需要处理的范围，比如A1:Z100
  const targetRange = 'A1:Z100';
  const cleanData = getUserEnteredData(sourceSheet.getSheetId(), targetRange);
  
  // 将干净的数据写入目标表
  destSheet.getRange(1, 1, cleanData.length, cleanData[0].length).setValues(cleanData);
}