Can I reference the sheet tab before the active sheet tab without using it's name?

Question

It it possible to reference a cell in the sheet tab before the active sheet tab?

For instance, my active sheet is 'Sheet 2' but I want to reference 'A1' in 'Sheet 1'. However I don't want to use the name because the name may change and the position may change. For example I arrage 'Sheet 3' in front of 'Sheet 1' or 'Sheet 2'. But the constant is I always want to reference the Sheet in front of the active sheet.

I've tried using Index but the only return I get is the number.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex(); var num = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheet];(doesn't work)

or

var sh = ss.getSheetByName(sheet);(doesn't work)

I know I can write code to reference a cell in another sheet, but I will be creating new sheets and I want them to always reference cells in the sheet tab before it. I don't want to manually change my code after creating the new sheet.

UPDATE: Thank you! Here is my updated code:

function rollOver() {
  // Active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // getting the index of the active sheet
  var index = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex(); 

  //getting all the sheets in the active spreadsheet
  var referenceSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  // Index of active sheet minus 2 to get the sheet before active sheet
  var referenceSheet = referenceSheets[index - 2];

  // Get reference sheet name
  var sheetName = referenceSheet.getSheetName();

  //Get reference sheet cells
  var rollover = referenceSheet.getRange("AW61").getA1Notation();
  var savings = referenceSheet.getRange("BE28").getA1Notation();

  // Insert formulas into new cells
  ss.getRange("AQ59").setFormula(sheetName + "!" + rollover);
  ss.getRange("BE15").setFormula(sheetName + "!" + savings);
  
  //Check variable output
  Logger.log(sheetName);
  Logger.log(rollover);
  Logger.log(savings);
}

This code really helps. For my instance I have a button that when it is clicked it creates a new spreadsheet. However, I want to always reference values in the sheet before it. At first I was just inserting the value of the cell in the referenced sheet, but now inserting the a formula to = that cell it will continuously update the value. Which is huge since I will continue to make new sheets. So even if I change a value of a sheet 5 sheets before it, It will update all of them.

Answer 1

You can try this sample code to always get reference sheet before the active sheet.

Sample Code:

function myFunction() {
  // getting the index of the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex(); 
  //getting all the sheets in the active spreadsheet
  var referenceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets() 
  // Index of active sheet minus 2 to get the sheet before active sheet
  var referenceData = referenceSheet[ss - 2].getRange("A1").getValue() 
  console.log(referenceData)

}

Output:

Reference:

getSheets