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

Riles
October 13, 2023
114 views
0 votes
2 Answers

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)

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.

Answers

- Cooper
- October 13, 2023 at 4:58 am
- 0 votes
0
Get the Sheet Before the active sheet
```
function getSheetBeforeActiveSheet() {
  const ss = SpreadsheetApp.getActive();
  const ns = ss.getSheets().map(sh => sh.getName());
  const ashn = ss.getActiveSheet().getName();
  ss.getSheets()[ns.indexOf(ashn)-1].activate();
}
```
Login or Signup to reply.

- George
- October 13, 2023 at 5:23 am
- 0 votes
0
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
Login or Signup to reply.