Google Sheets中365组列对垂直堆叠的实现难题

需要将E列到ABH列（共736列）的365行数据按列对（E&F、G&H……）垂直堆叠，过滤掉两列都为空的行。原手动公式=FILTER(VSTACK(E6:F,G6:H,I6:J,K6:L), (VSTACK(E6:E,G6:G,I6:I,K6:K) <> "") + (VSTACK(F6:F,H6:H,J6:J,L6:L) <> "") )无法手动扩展到736列，AI生成公式失败，自行编写的Apps Script在处理到第370列时异常跳回A列，无法完成全部列处理。

原脚本核心错误 #

原脚本中判断列值是否存在的逻辑完全错误：

var startValue = (currentStartColumn <= numCols) ? values[row][currentStartColumn - start] : "";
var endValue = (currentEndColumn <= numCols) ? values[row][currentEndColumn - start] : "";

这里currentStartColumn是全局列索引（比如E列对应5），而numCols是当前批次的列数（如batchSize=100时为100），两者没有可比性。当处理到批次内后半部分列时，currentStartColumn会远大于numCols，导致错误取空值，进而引发逻辑异常。

修正后的Apps Script代码 #

function stackColumns() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const TOTAL_COLUMNS = 736; // E到ABH列总列数
  const START_ROW = 6; // 数据起始行
  const ROW_COUNT = 365; // 需要处理的行数
  const START_COLUMN = 5; // E列的索引
  const OUTPUT_START_COL = 739; // 输出起始列（例如ABJ列）
  const OUTPUT_START_ROW = 7; // 输出起始行

  let stackedData = [];

  // 直接按列对遍历所有目标列，避免分批索引混淆
  for (let col = START_COLUMN; col <= START_COLUMN + TOTAL_COLUMNS - 1; col += 2) {
    // 获取当前列对的完整数据范围
    const range = sheet.getRange(START_ROW, col, ROW_COUNT, 2);
    const values = range.getValues();

    // 过滤空行并收集数据
    values.forEach(row => {
      const [val1, val2] = row;
      if (val1 !== "" || val2 !== "") {
        stackedData.push([val1, val2]);
      }
    });
  }

  // 写入堆叠后的数据
  if (stackedData.length > 0) {
    const outputRange = sheet.getRange(OUTPUT_START_ROW, OUTPUT_START_COL, stackedData.length, 2);
    outputRange.clearContent();
    outputRange.setValues(stackedData);
  }
}

修正要点： #

• 取消易出错的分批逻辑，直接按列对（步长2）遍历全局列索引
• 每次直接获取当前列对的完整数据范围，无需手动计算数组索引
• 简化空行过滤逻辑，直接判断当前行的两个值是否非空
• 明确常量定义，便于后续调整参数

替代公式方案（无需脚本） #

使用REDUCE函数动态生成列对的VSTACK参数，无需手动编写所有列对：

=FILTER(
  REDUCE("",SEQUENCE(368,1,5,2),LAMBDA(a,c,VSTACK(a,INDIRECT(ADDRESS(6,c)&":"&ADDRESS(420,c+1))))),
  REDUCE("",SEQUENCE(368,1,5,2),LAMBDA(a,c,VSTACK(a,INDIRECT(ADDRESS(6,c)&":"&ADDRESS(420,c)))))<>"" +
  REDUCE("",SEQUENCE(368,1,6,2),LAMBDA(a,c,VSTACK(a,INDIRECT(ADDRESS(6,c)&":"&ADDRESS(420,c)))))<>""
)

公式说明： #

• SEQUENCE(368,1,5,2)：生成从5开始（E列）、步长2的序列，共368个（736列/2）
• REDUCE遍历序列，逐个将对应列对的行数据VSTACK起来
• FILTER过滤掉两列都为空的行
• 注意：ADDRESS(420,c)中的420是START_ROW+ROW_COUNT-1=6+365-1=420，需根据实际行数调整

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