CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yuniru
Yuniru

Reputation: 55

.getSheets() function return the wrong sheet

I have a simple Spreadsheet that only contains 2 sheets. I'm trying to select a cell in the second sheet depending on the coordinates of the active cell in the first sheet.

But I am facing a weird issue. Here is my code :

function Arrondis() {
  var firstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var cellRange = firstSheet.getCurrentCell();
  var selectedColumn = cellRange.getColumn();
  var selectedRow = cellRange.getRow();
  
  var secondSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var secondColumn = selectedColumn-1;
  var secondRow = selectedRow-26;

  Logger.log("FEUILLE 1 : "+firstSheet.getName());
  Logger.log("FEUILLE 2 : "+secondSheet.getName());
  Logger.log("ROW :"+selectedRow+" / COLUMN :"+selectedColumn+" / VALUE :"+cellRange.getValue());
  Logger.log("ROW :"+secondRow+"  COLUMN :"+secondColumn);
}

The 2 first Logger functions are perfectly working and both returns two different names. But the next Logger function is not working. It returns the coordinates of the current cell in the active sheet. I want it to always look up in my FIRST sheet no matter where I'm calling it from. I could add a line to set the active sheet at the beginning of my code but that seems too much.

Upvotes: 1

Views: 1165

Answers (1)

Marios
Marios

Reputation: 27350

Solution:

Since you are working with active cells I think the only way to get the active cell of an inactive sheet is to make the inactive sheet active.

function Arrondis() {
  
  const ss = SpreadsheetApp.getActive();
  const firstSheet = ss.getSheets()[0];
  ss.setActiveSheet(firstSheet);
  var cellRange = ss.getCurrentCell();
  
  var selectedColumn = cellRange.getColumn();
  var selectedRow = cellRange.getRow();
  
  var secondSheet = ss.getSheets()[1];
  var secondColumn = selectedColumn-1;
  var secondRow = selectedRow-26;

  Logger.log("FEUILLE 1 : "+firstSheet.getName());
  Logger.log("FEUILLE 2 : "+secondSheet.getName());
  Logger.log("ROW :"+selectedRow+" / COLUMN :"+selectedColumn+" / VALUE :"+cellRange.getValue());
  Logger.log("ROW :"+secondRow+"  COLUMN :"+secondColumn);
}

Upvotes: 1

Related Questions