导致错误的空 Google WorkSheet：异常：范围内的列数必须至少为 1

Question

我的 Google App 脚本将遍历所有 WorkSheets 以查找特定字符串并输出找到它的行和列，但是只要我添加一个新的空 WorkSheet 我就会收到错误“异常：范围内的列数必须至少为 1。” 我想了解导致此问题的原因。谢谢你。

function doForAllSheets(){

 var spreadsheet = SpreadsheetApp.getActive();

 var allSheets = spreadsheet.getSheets();

  

  allSheets.forEach(function(sheet){

    if(sheet.getSheetName() !== "Main"){

    sheet.activate();

 

    findValueInRange();

    

    }

  })

}

                     

function findValueInRange () {

  var ss = SpreadsheetApp.getActiveSheet(),

      theLastColumn = ss.getLastColumn(),

      theLastRow = ss.getMaxRows();

  //Returns a two dimensional array of both rows and columns

  var values = ss.getRange(1,1,theLastRow,theLastColumn).getValues();

  var i=0,

      rownum=0, 

      thisRow,

      colvalue,

      whatToFind = "testserial",

      j=0;

  for(i=0;i<values.length;i++) {

    thisRow = values[i];

    for (j=1;j<thisRow.length;j++) {

      colvalue = thisRow[j];

      if (colvalue === whatToFind) {

        Logger.log("The string is in row: " + (i + 1) + " and column: " + (j+1));

      }

    }

  }

}

Answer 1

像这样重写函数：function findValueInRange () {&nbsp; var ss = SpreadsheetApp.getActiveSheet(),&nbsp; &nbsp; &nbsp; theLastColumn = ss.getLastColumn(),&nbsp; &nbsp; &nbsp; theLastRow = ss.getMaxRows();&nbsp; if( theLastColumn>0) {&nbsp; //Returns a two dimensional array of both rows and columns&nbsp; var values = ss.getRange(1,1,theLastRow,theLastColumn).getValues();&nbsp; var i=0,&nbsp; &nbsp; &nbsp; rownum=0,&nbsp;&nbsp; &nbsp; &nbsp; thisRow,&nbsp; &nbsp; &nbsp; colvalue,&nbsp; &nbsp; &nbsp; whatToFind = "testserial",&nbsp; &nbsp; &nbsp; j=0;&nbsp; for(i=0;i<values.length;i++) {&nbsp; &nbsp; thisRow = values[i];&nbsp; &nbsp; for (j=1;j<thisRow.length;j++) {&nbsp; &nbsp; &nbsp; colvalue = thisRow[j];&nbsp; &nbsp; &nbsp; if (colvalue === whatToFind) {&nbsp; &nbsp; &nbsp; &nbsp; Logger.log("The string is in row: " + (i + 1) + " and column: " + (j+1));&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; }&nbsp; }}