Trying to test the google spreadsheet in apps script using multiple sheets force to go to a specific sheet without using the URL

Question

I am using a number of sheets and want to force my script to go to a specific sheet but instead it stays on the active sheet that was just created in my script ?

Note: I do not want to use the URL but the actual name of the spread sheet instead, and think I have to use the setActiveSheet() but have not been able to find any information but that it does not use a string but instead you have to use the object from the parent class ?

The script is showing in the log the word "Master_Approval" and is the wrong sheet the name of the active sheet.

var Sprsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = Sprsheet.getActiveSheet();
var mySheet = sheet.getSheetName();

if (sheet.getSheetName() == "2020Members") {
  Logger.log('THE ACTIVE SHEETS NAME is :' + mySheet);    
}
else {      
  
  Logger.log('THE WRONG ACTIVE SHEETS NAME is :' + mySheet);
  // Force to the correct sheet ! 
  var sheetDataAsArr = sprSheet.getSheetByName("2020Members");
  var sheet = sprSheet.getActiveSheet();
  var mySheet = sheet.getSheetName();
  Logger.log('THE ACTIVE SHEETS NAME is :' + mySheet);
}   

It should show the "2020Members" but is not and if the test fails I want to force the active sheet to be "2020Members" and not the sheet we are in currently.

var sheetDataAsArr = sprSheet.getSheetByName("2020Members");
var mainSheet = sheetDataAsArr.getActiveSheet();
Logger.log('THE ACTIVE is :' + mainSheet);
var sheet = sprSheet.setActiveSheet(mainSheet);    
var mySheet = sheet.getSheetName();
Logger.log('THE ACTIVE SHEETS NAME is :' + mySheet);

What is wrong above?

Answer 1

What is wrong above ?

Answer:

In your last code snippet you have some lines incorrectly written:

• Each instance of sprSheet needs to be Sprsheet.
• You need to use getSheetName() on mainSheet to log the name of the sheet
• The line sprSheet.setActiveSheet(mainSheet) is setting the active sheet to whatever the active sheet already is, you need to use setActiveSheet(sheetDataAsArr)

Full Code:

function myFunction(){
  var Sprsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = Sprsheet.getActiveSheet();
  var mySheet = sheet.getSheetName();
  
  if (sheet.getSheetName() == "2020Members") {
    Logger.log('THE ACTIVE SHEETS NAME is :' + mySheet);    
  }
  else {    
    var sheetDataAsArr = Sprsheet.getSheetByName("2020Members");
    var mainSheet = Sprsheet.getActiveSheet();
    Logger.log('THE ACTIVE is :' + mainSheet.getSheetName());
    var sheet = Sprsheet.setActiveSheet(sheetDataAsArr);    
    var mySheet = sheet.getSheetName();
    Logger.log('THE ACTIVE SHEETS NAME is :' + mySheet);
  }   
}

Something to note: Google Sheets does have some strange behaviour with bound script files and sometimes the script tab will 'lose' it's bound status to the Spreadsheet if both are open and left unattended for long periods of time; this however can be fixed by reloading the Spreadsheet.

Related Questions:

• How to make all sheets of a Google spreadsheet show A1 in upper left?

Answer 2

I needed something very similar. Here is what I ended up using. Per your function, change accordingly. And you will need to change "Sheet2", to your sheet name "2020Members".

function theSecondSheet() {
   var activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    if(activeSpreadSheet.getSheetByName("Sheet2").activate()){
     SpreadsheetApp.setActiveSheet(activeSpreadSheet.getSheetByName("Sheet2"));
    
    }
}