如何提速Google Sheets Apps Script新插入列公式复制函数

问题背景 #

• 现有包含多个工作表的工作簿，主工作表共123行、90列数据
• 已实现onChange事件绑定，触发INSERT_COLUMN（插入列）操作时，自动将新列左侧紧邻列的内容填充到新列，之后清除所有非公式单元格的数值与备注
• 现有脚本可正常运行，但执行速度异常缓慢，未定位性能瓶颈

原有可运行代码 #

function getColumnLetter(a1Notation) {
  const letter = a1Notation.replace(/\d+/, ''); 
  return letter;
}

function getColumnLetterFromNumber(sheet, colNum) {
  const range = sheet.getRange(1, colNum); 
  return getColumnLetter(range.getA1Notation());
}

function forEachRangeCell(range, callback) {
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();

  for (let i = 1; i <= numCols; i+=1) {
    for (let j = 1; j <= numRows; j+=1) {
      const cell = range.getCell(j, i);

      callback(cell);
    }
  }
}

function deleteAllValuesAndNotesFromNonFormulaCells(range) {
  forEachRangeCell(range, function (cell) {
    if(!cell.getFormula()){ 
      cell.setValue(null);
      cell.clearNote();
    }
  });
}

function onInsertColumn(sheet, activeRng) {  
  if (activeRng.isBlank()) {
    const minCol = 5;
    const col = activeRng.getColumn();
    if (col >= minCol) {
      const prevCol = col - 1;    
      const colLetter = getColumnLetterFromNumber(sheet, col);    
      const prevColLetter = getColumnLetterFromNumber(sheet, prevCol);
      
      //SpreadsheetApp.getUi().alert(`Please wait while formulas are copied to the new column...`);
      const originRng = sheet.getRange(`${prevColLetter}:${prevColLetter}`);    
      originRng.copyTo(activeRng, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);    
      deleteAllValuesAndNotesFromNonFormulaCells(activeRng);
      const completeMsg = `New column ${colLetter} has formulas copied and is ready for new values (such as address, Redfin link, data, ratings).`;
      //SpreadsheetApp.getUi().alert(completeMsg);
      // SpreadsheetApp.getActiveSpreadsheet().toast(completeMsg);
    }
  }
}

function onChange(event) {   
  if(event.changeType === 'INSERT_COLUMN'){
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet()
    const colNumber = sheet.getSelection().getActiveRange().getColumn(); 

    const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);

    const sheetName = sheet.getName();
  
    if(sheetName === 'ratings'){
      onInsertColumn(sheet, activeRng);
    }
  }
}

核心性能瓶颈 #

• 逐单元格API调用是卡顿核心原因：Google Apps Script 与云端表格交互的API请求存在固定网络延迟，原有代码逐单元格调用getCell()、getFormula()、setValue()、clearNote()，处理一整列时会产生上千次单独请求，延迟被无限放大
• 冗余操作过多：原有逻辑先全量复制左侧列所有内容（值、公式、格式、备注等），再逐单元格删除非公式内容，完全可以通过调整复制类型省略后续清理步骤
• 无效范围过大：使用getMaxRows()取工作表最大行数（默认1000行）处理，远大于实际有效数据的123行，额外增加无意义的计算量
• 冗余工具逻辑：列号转A1列字母的逻辑完全多余，getRange()原生支持数字行列参数，额外调用API获取A1标记只会增加请求次数

优化方案 #

• 取消逐单元格遍历逻辑，所有表格操作均采用批量API，将单次执行的API请求次数从上千次压缩到5次以内
• 调整复制策略：不再全量粘贴内容，仅粘贴需要保留的格式、公式、数据验证、条件格式，从根源上省略后续删除非公式值、备注的步骤
• 缩小处理范围：使用getLastRow()获取实际有数据的最后一行，仅处理有效数据范围（如果需要兼容整列预设公式，可保留整行范围，不影响速度）
• 删除冗余的列字母转换逻辑，直接用数字参数操作Range

优化后代码 #

function onInsertColumn(sheet, activeCol) {
  const minCol = 5;
  if (activeCol < minCol) return;
  const lastRow = sheet.getLastRow();
  // 新列范围、左侧源列范围均直接用数字参数定义，无需转A1标记
  const targetRng = sheet.getRange(1, activeCol, lastRow, 1);
  const sourceRng = sheet.getRange(1, activeCol - 1, lastRow, 1);
  // 仅粘贴需要保留的内容，不需要的值、备注根本不会被复制，无需后续清理
  sourceRng.copyTo(targetRng, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  sourceRng.copyTo(targetRng, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  sourceRng.copyTo(targetRng, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
  sourceRng.copyTo(targetRng, SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
  // 如需提示可放开下面注释，内存计算列字母无需调用API
  // const colLetter = String.fromCharCode(64 + activeCol);
  // SpreadsheetApp.getActiveSpreadsheet().toast(`New column ${colLetter} has formulas copied and is ready for new values`);
}

function onChange(event) {   
  if(event.changeType !== 'INSERT_COLUMN') return;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  if (sheet.getName() !== 'ratings') return;
  const colNumber = sheet.getSelection().getActiveRange().getColumn();
  onInsertColumn(sheet, colNumber);
}

优化效果 #

原有脚本处理单插入列操作需要数秒到数十秒，优化后执行时间可压缩到100毫秒以内，无明显卡顿。

注：如果需要保留整列（包含下方空白行）的公式预设，只需将lastRow替换为sheet.getMaxRows()即可，执行速度不会有明显下降。

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