如何不通过工作表名称引用活动工作表的前序工作表单元格？

我需要引用当前活动工作表的前一个工作表中的单元格，但不想硬编码工作表名称——因为工作表的名称或位置可能会变动（比如把'Sheet 3'移到'Sheet 1'或'Sheet 2'前面），希望无论怎么调整，都能始终引用活动工作表的前一个工作表。

举个例子：如果当前活动表是'Sheet 2'，就引用'Sheet 1'的A1单元格。

我尝试过的方法 #

我试过用索引来实现，但代码无法正常运行：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex(); var num = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheet];

以及：

var sh = ss.getSheetByName(sheet);

另外，我会持续创建新工作表，希望新工作表能自动引用它的前一个工作表单元格，不用每次创建后手动修改代码。

最终解决方案 #

经过调整，我写出了可以正常工作的代码，现在设置了按钮，点击创建新工作表后，新表会自动插入引用前一个工作表的公式，确保值能同步更新：

function rollOver() {
  // 获取活动工作表
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 获取活动工作表的索引
  var index = activeSheet.getIndex(); 

  // 获取当前表格的所有工作表
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  // 索引减2得到前一个工作表（因为工作表索引从1开始，数组下标从0开始）
  var prevSheet = allSheets[index - 2];

  // 获取前一个工作表的名称
  var prevSheetName = prevSheet.getSheetName();

  // 获取需要引用的单元格A1标识
  var rolloverCell = prevSheet.getRange("AW61").getA1Notation();
  var savingsCell = prevSheet.getRange("BE28").getA1Notation();

  // 给活动工作表的指定单元格插入引用公式
  activeSheet.getRange("AQ59").setFormula(`${prevSheetName}!${rolloverCell}`);
  activeSheet.getRange("BE15").setFormula(`${prevSheetName}!${savingsCell}`);
  
  // 日志输出验证变量
  Logger.log(prevSheetName);
  Logger.log(rolloverCell);
  Logger.log(savingsCell);
}

这段代码的优势在于：插入的是公式而非固定数值，所以即使之前的工作表的值被修改，所有相关工作表的对应单元格都会自动同步更新。

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