Google Sheets脚本报#REF及自动扩展失败问题求助

我用以下Google Apps Script脚本导入商机数据，填充行后计算利息等数值，但出现了以下问题：

• 填充单元格时触发**#REF错误**
• 计算结果会短暂显示正确值，随后消失变为错误
• 报错提示：无法自动扩展，请插入新列(1)
  初步怀疑是表格数据量过大导致处理负载过高，想确认是否与此有关，以及如何解决。

function onInstall(e) {
  onOpen(e);
}

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu();
  menu.addItem('Créer étude', 'init');
  menu.addToUi();
}

function init() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  identifyJe();
  sheetMaker = ss.getActiveSheet();
  currentRow = ss.getActiveCell().getRowIndex();
  ssEtudes = SpreadsheetApp.openById(getSheetEtudesId());
  var ui = SpreadsheetApp.getUi();

  var response = ui.alert(
    'Ligne sélectionnée : ' + currentRow + '. Exécuter ?',
    ui.ButtonSet.OK_CANCEL
  );

  if (response !== ui.Button.OK) return;

  doEtude();
  doContrat();
  doPhases();
}

function doEtude(nomEtude) {
  var etudeData = [];

  for (var i = 0; i < etudeNamedRanges.length; i++) {
    var column = ss.getRangeByName(etudeNamedRanges[i]).getColumn();
    etudeData.push(sheetMaker.getRange(currentRow, column).getValue());
  }

  var destSheet = ssEtudes.getSheetByName('etudes');
  var rowToAppend = getFirstEmptyRow(destSheet, 0);

  for (var i = 0; i < etudeData.length; i++) {
    var column = ssEtudes.getRangeByName(etudeNamedRanges[i]).getColumn();
    destSheet.getRange(rowToAppend, column).setValue(etudeData[i]);
  }
}

function doContrat() {
  var contratData = [];

  for (var i = 0; i < contratNamedRanges.length; i++) {
    var column = ss.getRangeByName(contratNamedRanges[i]).getColumn();
    contratData.push(sheetMaker.getRange(currentRow, column).getValue());
  }

  var destSheet = ssEtudes.getSheetByName('contrats');
  var rowToAppend = getFirstEmptyRow(destSheet, 11);

  for (var i = 0; i < contratData.length; i++) {
    destSheet.getRange(rowToAppend, i + 4).setValue(contratData[i]);
  }
}

function doPhases() {
  var phasesCoords = getPhasesCoords();
  var phasesData = [];

  for (var i = 0; i < phasesCoords.length; i++) {
    var phaseData = getPhaseData(phasesCoords[i]);
    phaseData.unshift(i + 1);
    phasesData.push(phaseData);
  }

  var destSheet = ssEtudes.getSheetByName('phases');
  var rowToAppend = getFirstEmptyRow(destSheet, 9);
  var columns = [];

  for (var nPhase = 0; nPhase < phasesData.length; nPhase++) {
    var phaseData = phasesData[nPhase];

    for (var i = 0; i < phaseData.length; i++) {
      if (columns[i] === undefined)
        columns[i] = ssEtudes.getRangeByName(phasesNamedRanges[i]).getColumn();

      destSheet
        .getRange(rowToAppend + nPhase, columns[i])
        .setValue(phaseData[i]);
    }
  }
}

1. 检查列数上限
   报错提示明确提到无法自动扩展列，先确认目标工作表（etudes/contrats/phases）是否已用满Google Sheets的列数上限（18278列），或者存在列保护、隐藏列导致公式无法扩展。可以手动插入一列测试是否能成功，若无法插入则说明列数已达上限，需要清理无用列。

2. 优化脚本性能
   当前脚本通过循环逐个单元格调用setValue，数据量大时会触发大量API请求，导致处理延迟甚至计算中断，这可能是结果短暂显示后消失的原因。建议改为批量写入数据，减少API调用次数：
   比如修改doEtude中的写入逻辑：

   // 替换原循环写入代码
   var destColumns = etudeNamedRanges.map(rangeName => ssEtudes.getRangeByName(rangeName).getColumn());
   // 按列排序数据（确保对应正确列位置）
   var sortedData = destColumns.map(col => etudeData[destColumns.indexOf(col)]);
   destSheet.getRange(rowToAppend, destColumns[0], 1, sortedData.length).setValues([sortedData]);
   

   同理对doContrat和doPhases做批量写入优化。

3. 验证命名范围有效性
   检查etudeNamedRanges、contratNamedRanges、phasesNamedRanges中的命名范围是否在目标表格ssEtudes中存在且有效，若命名范围指向的列被删除或不存在，会直接导致#REF错误。

4. 检查公式依赖
   目标表格中的利息计算公式若使用了ARRAYFORMULA等动态扩展公式，需确认公式的引用范围是否能覆盖新插入的行。比如公式是否设置为ARRAYFORMULA(A2:A)而非固定范围，避免新行无法被公式包含。

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