如何在Google Apps Script中为动态范围设置条件求和公式？

以下是修改后的代码，能够根据A列中"*"标记的标题行动态调整求和范围：

function updateDynamicSumFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('sheet1');
  const dataRange = sheet1.getDataRange();
  const values = dataRange.getValues(); // 获取所有单元格值（0-based数组）
  const lastRow = dataRange.getLastRow(); // 表格最后一行的行号（1-based）

  // 收集所有A列为"*"的行的索引（0-based）
  const headerRowIndices = [];
  for (let i = 0; i < values.length; i++) {
    if (values[i][0] === "*") {
      headerRowIndices.push(i);
    }
  }

  // 遍历每个标题行，设置动态求和公式
  for (let j = 0; j < headerRowIndices.length; j++) {
    const currentHeaderIndex = headerRowIndices[j];
    const formulaRow = currentHeaderIndex + 1; // 公式所在的行号（1-based）

    // 确定求和范围的结束行
    let sumEndRow;
    if (j < headerRowIndices.length - 1) {
      // 不是最后一个标题行，结束行是下一个标题行的前一行
      sumEndRow = headerRowIndices[j + 1]; // 1-based行号
    } else {
      // 最后一个标题行，结束行是表格最后一行
      sumEndRow = lastRow;
    }

    // 计算R1C1公式中的相对偏移量
    const sumEndOffset = sumEndRow - formulaRow;

    // 设置从第13列到第32列（共20列）的求和公式
    const formulaRange = sheet1.getRange(formulaRow, 13, 1, 20);
    const dynamicFormula = `=SUM(R[1]C:R[${sumEndOffset}]C)`;
    formulaRange.setFormulaR1C1(dynamicFormula);
  }
}

代码说明： #

1. 收集标题行位置：遍历整个表格，记录所有A列为"*"的行的索引，后续用于确定求和范围的边界。
2. 动态计算求和范围：
   • 对于每个标题行，找到下一个标题行的位置，求和范围为当前标题行的下一行到下一个标题行的前一行。
   • 如果是最后一个标题行，求和范围延伸至表格的最后一行。
3. 设置R1C1动态公式：使用相对行偏移量构建求和公式，确保公式会随着标题行之间的行数变化自动适配。

使用此代码替代原函数后，无论标题行之间的行数如何增减，求和范围都会自动调整，不会出现遗漏或错误包含数据的情况。

内容的提问来源于stack exchange，提问作者Julie-Anne