无法按名称激活工作表

我试图在打开电子表格时根据当前月份激活每月工作表。

问题出在脚本末尾，无法选择工作表。看起来getSheetByName() gets a null值，不被接受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);
}

这是最终有效的代码：

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();

}

你的脚本可以更简单...... 试试这样：（所有标记的行;// can be removed确实可以删除并且仅用于演示目的）

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
}

还有“短”版本：

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

编辑：如果您确实想按名称获取工作表（如果您无法确定工作表顺序），您可以像下面这样实现它（这仍然比您的代码简单）：

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 :既然您似乎不断遇到问题，让我建议第三个版本，该版本利用您的sheetNames中包含的数字并找到正确的工作表，即使它们没有排序......它是这样的：

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());
}