google-apps-script - "The coordinates of the range are outside the dimensions of the sheet"排序时出错

Question

我正在尝试创建一个脚本来对当前事件工作表/选项卡上的表格进行排序，但我收到无法识别的错误。我不断收到 The coordinates of the range are outside the dimensions of the sheet.。我的表中有从 columnA 到 columnQ 的数据。 columnA 中的数据是标识号，columnB 到 columnQ 包含公式。我的脚本做错了什么？

var sortFirst = 7; //index of column to be sorted by; 1 = column A, 2 = column B, etc.
  var sortFirstAsc = true; //Set to false to sort descending

  var sortSecond = 8;
  var sortSecondAsc = true;

  var sortThird = 9;
  var sortThirdAsc = true;

  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}, {column: sortThird, ascending: sortThirdAsc}]);

Answer 1

很有可能是线路上的错误

var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());

很有可能的解决办法是改成

var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

上面是因为范围从第 2 行开始并且因为工作表很可能在最后一行有内容。

相关

• Google Sheets Script getLastRow() not returning all values
• Need help debugging Google Apps Script code intended to update hundreds of files in Google Drive