Google Sheets：首行ArrayFormula过滤表格及脚本功能开发求助

问题 #

• 需要用ArrayFormula处理A-O列，但对I列排序时，列内公式会随排序移位，希望将计算逻辑固定以避免移位
• 删除A7-H7单元格内容后，表格中不要出现空行
• 需在P7:P列添加按钮，实现点击删除对应行的功能

解决方案 #

一、用Apps Script实现固定计算逻辑（彻底解决公式移位） #

将原ArrayFormula的计算逻辑写成脚本，直接把计算结果写入I-O列，排序时不会因公式移位导致错误。同时脚本会自动处理空行删除，并支持删除行按钮功能。

完整脚本代码

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  // 仅响应A7:H范围内的编辑操作
  if (sheet.getName() !== "Sheet1" || !range.getA1Notation().match(/^[A-H][7-9]\d*$/)) return;
  
  const lastRow = sheet.getLastRow();
  if (lastRow <7) return;
  const dataRange = sheet.getRange(7, 1, lastRow - 6, 15); // A7:O区域
  const values = dataRange.getValues();
  // 获取固定参数单元格的值
  const o2 = sheet.getRange("O2").getValue();
  const n2 = sheet.getRange("N2").getValue();
  const a2 = sheet.getRange("A2").getValue();
  
  let runningTotal = a2;
  const processedData = values.map(row => {
    const [, , , , eVal, fVal, gVal, hVal] = row;
    let i, j, k, l, m, n, o;
    
    // 计算I列值
    if (fVal === "") {
      i = gVal === "" ? "" : Math.round((hVal - gVal) * 10000 * 100) / 100;
    } else {
      i = Math.round((fVal - hVal) * 10000 * 100) / 100;
    }
    
    // 计算J列值
    j = eVal === "" ? "" : Math.round((eVal * o2 / 1000) * 100) / 100;
    
    // 计算K列值
    if (fVal === "") {
      k = gVal === "" ? "" : Math.round(((n2 - gVal) * eVal / gVal * (-1)) * 100) / 100;
    } else {
      k = Math.round(((n2 - fVal) * eVal / fVal * (-1)) * 100) / 100;
    }
    
    // 计算L列值
    l = eVal === "" ? "" : Math.round((j + k) * 100) / 100;
    
    // 计算M列累计值
    if (eVal === "") {
      m = "";
    } else {
      runningTotal += l;
      m = runningTotal;
    }
    
    // 计算N列值
    n = eVal === "" ? "" : Math.round((l / m * 100) * 100) / 100;
    
    // 计算O列值
    if (gVal === "") {
      o = fVal === "" ? "" : Math.round((eVal * i * 0.0001 / fVal) * 100) / 100;
    } else {
      o = Math.round((eVal * i * 0.0001 / gVal) * 100) / 100;
    }
    
    // 更新行中的I-O列（数组索引8到14对应I到O列）
    row[8] = i;
    row[9] = j;
    row[10] = k;
    row[11] = l;
    row[12] = m;
    row[13] = n;
    row[14] = o;
    return row;
  });
  
  // 将计算结果写回表格
  dataRange.setValues(processedData);
  
  // 自动删除A-H全空的行
  deleteEmptyRows(sheet);
}

// 批量删除A-H列全空的行
function deleteEmptyRows(sheet) {
  const lastRow = sheet.getLastRow();
  if (lastRow <7) return;
  const checkRange = sheet.getRange(7, 1, lastRow - 6, 8); // A7:H区域
  const rows = checkRange.getValues();
  const deleteRowNumbers = [];
  
  rows.forEach((row, index) => {
    if (row.every(cell => cell === "")) {
      deleteRowNumbers.push(index +7); // 转换为实际行号
    }
  });
  
  // 倒序删除，防止行号偏移
  for (let i = deleteRowNumbers.length -1; i >=0; i--) {
    sheet.deleteRow(deleteRowNumbers[i]);
  }
}

// 删除当前按钮所在行的函数
function deleteRowByButton() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const activeCell = sheet.getActiveCell();
  // 仅处理P列（第16列）且行号≥7的点击
  if (activeCell.getColumn() !==16 || activeCell.getRow() <7) return;
  
  sheet.deleteRow(activeCell.getRow());
}

使用步骤

1. 打开目标谷歌表格，点击顶部菜单栏「扩展程序」→「Apps 脚本」
2. 清空编辑器中的默认代码，粘贴上述脚本
3. 将脚本中sheet.getName() !== "Sheet1"的Sheet1替换为你的表格实际名称
4. 点击编辑器顶部的「保存」按钮，命名脚本（比如TableManager）
5. 首次点击「运行」，按提示完成权限授权
6. 为P列添加删除按钮：
   • 点击顶部菜单栏「插入」→「绘图」，绘制一个按钮形状（如矩形），添加文字「删除」
   • 右键点击绘制好的按钮→「分配脚本」，输入deleteRowByButton
   • 将按钮复制到P7及以下的对应行位置即可

二、无脚本替代方案（隐藏列存公式） #

如果不想使用脚本，可以把原ArrayFormula放到隐藏列，再用引用关联到I-O列：

• 选择一组空白列（比如Q-U），将原I-O列的ArrayFormula分别输入到Q7、R7、S7、T7、U7、V7、W7
• 在I7输入=INDEX(Q:Q,ROW())，下拉填充（或用ARRAYFORMULA(INDEX(Q:Q,ROW(Q7:Q)))批量填充）
• 同理，J7引用R列、K7引用S列，以此类推
• 最后隐藏Q-W列，这样排序时I-O列的引用不会移位，因为公式固定在隐藏列中

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