如何将Office Script中硬编码公式改为基于列索引的动态形式

要把基于列字母的代码改成动态列索引形式，你可以通过两种方式实现，以下是具体方案：

方案1：使用R1C1引用样式（无需转换列字母） #

这种方式直接用行号和列索引编写公式，无需额外转换列字母，代码更简洁：

const col1_index = 39; // AM列的索引
const col2_index = 40; // AN列的索引

// 选中AN4:AN5区域：起始行4，起始列col2_index，共2行1列
selectedSheet.getRange(4, col2_index, 2, 1).setFormulas([
  [`=R2C${col2_index}-R2C${col1_index}`], // 对应原公式=AN2-AM2
  [`=R3C${col2_index}-R3C${col1_index}`]  // 对应原公式=AN3-AM3
]);

代码说明 #

• getRange(4, col2_index, 2, 1)：参数依次为起始行号、起始列索引、行数、列数，精准定位目标区域。
• 公式里的R2C${col2_index}表示第2行、第col2_index列的单元格，对应A1样式的AN2；R2C${col1_index}对应AM2，以此类推。

方案2：构建A1样式字符串（需转换列索引为字母） #

如果偏好保留A1引用样式，可以添加一个辅助函数将列索引转为字母，再拼接公式：

// 辅助函数：将列索引转换为Excel列字母
function getColumnLetter(colIndex: number): string {
  let letter = '';
  while (colIndex > 0) {
    const remainder = colIndex % 26;
    colIndex = Math.floor(colIndex / 26);
    if (remainder === 0) {
      letter = 'Z' + letter;
      colIndex--;
    } else {
      letter = String.fromCharCode(64 + remainder) + letter;
    }
  }
  return letter;
}

// 定义列索引
const col1_index = 39;
const col2_index = 40;

// 转换为列字母
const col1_letter = getColumnLetter(col1_index);
const col2_letter = getColumnLetter(col2_index);

// 设置公式
selectedSheet.getRange(4, col2_index, 2, 1).setFormulas([
  [`=${col2_letter}2-${col1_letter}2`],
  [`=${col2_letter}3-${col1_letter}3`]
]);

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