Unable to activate a sheet by name

Question

I'm trying to activate a monthly sheet upon opening the Spreadsheet, according to the current month.

The problem is at the end of the script, when it fails selecting the sheet. It seems that getSheetByName() gets a null value, which is not accepted by setActiveSheet().

function selectmonth(){
  var now= new Date();
  var month= now.getMonth()+1;  
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetname="";

  switch (month){
    case 1:
      sheetname="urtarrila01";  //english=january01
      break;
    case 2:
      sheetname="otsaila02";    //english=february02 and so on...
      break;
    case 3:
      sheetname="martxoa03";
      break;
    case 4:
      sheetname="apirila04";
      break;
    case 5:
      sheetname="maiatza05";
      break;
    case 6:
      sheetname="ekaina06";
      break;
    case 9:
      sheetname="iraila09";
      break;
    case 10:
      etiketaizena="urria10";
      break;
    case 11:
      sheetname="azaroa11";
      break;
    case 12:
      sheetname="abendua12";
      break;      
    default:
      sheetname="LABURPENA-resumen";
  }

 //HERE mysheet gets null value, although the sheet exist, named "sheetname) 
  var mysheet=ss.getSheetByName(sheetname); 
  //AN HERE THE SCRIPT FAILS, ERROR MESSAGE=invalid argument on next line
  ss.setActiveSheet(mysheet);
}

Well this is the final code that works:

enter code here

  function hileHonetan(){
  var now= new Date(); 
  var month= now.getMonth();
  var mysheetname="";
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames= ['urtarrila01', 'otsaila02', 'martxoa03', 'apirila04',  'maiatza05',  'ekaina06', 'ekaina06', 'ekaina06', 'iraila09', 'urria10', 'azaroa11' , 'abendua12']
  mysheetname= sheetNames[month];
  var mysheet=ss.getSheetByName(mysheetname);
  mysheet.activate();

}

Serge insas Serge insas · Accepted Answer · 2013-09-04T11:34:48

Your script could much more simple ... Try it like this : (all the lines marked ;// can be removed can indeed be removed and are there only for demo purpose)

function selectmonth(){
  var now= new Date();
  var month = now.getMonth();  
  Logger.log(month);// can be removed
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = [];// can be removed
  var sheets = ss.getSheets();// can be removed
  for( var n in sheets){;// can be removed
    sheetNames.push(sheets[n].getName());// can be removed
  };// can be removed
  var mysheet = ss.getSheets()[month] 
  ss.setActiveSheet(mysheet);
  Browser.msgBox("this sheet is "+sheetNames[month]);// can be removed
}

And the "short" version :

function selectmonth(){
   var now= new Date();
   var month = now.getMonth();  
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var mysheet = ss.getSheets()[month] 
   ss.setActiveSheet(mysheet);
}

EDIT : if you really want to get the sheets by their names (in case you cannot be sure about sheet order) you could implement it like below (which is still quite simpler than your code) :

function selectmonth(){
  var now= new Date();
  var month = now.getMonth();  
  Logger.log(month);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = ['Sheet1','Sheet2','Sheet3','Sheet4','Sheet5','Sheet6','Sheet7','Sheet8','Sheet9','Sheet10','Sheet11','Sheet12',];;
  Logger.log(sheetNames)
  var mysheet = ss.getSheetByName(sheetNames[month]);
  ss.setActiveSheet(mysheet);
  Browser.msgBox("this sheet is "+sheetNames[month]);// can be removed
}

EDIT 2 : since you seem to keep getting issues let me suggest a third version that takes advantage of the numbers included in your sheetNames and find the right sheet even if they are not sorted... it goes like that :

function selectmonth(){
  var now= new Date();
  var month = now.getMonth();  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet;
  var sheets = ss.getSheets();
  for( var n in sheets){
    var index = Number(sheets[n].getName().replace(/[^0-9]/ig,''));
    Logger.log(index);// index is the number included in your sheetName, with that we can get the sheet in the following loop.
    if(index == month+1){mysheet = ss.getSheets()[n] ; break}
  }
  ss.setActiveSheet(mysheet);
  Browser.msgBox("this sheet is "+mysheet.getName());
}

Unable to activate a sheet by name

1 Answers