范围未找到，谷歌应用程序脚本的谷歌表

Question

我试图在Google Sheets的Apps脚本中为此排序函数建立自定义范围，但我一直得到错误:"异常:范围未找到;sortProductionLog @宏。gs:15">

我确信这是一些基本的东西，但我已经搜索了很远很远，似乎找不到它。有人能告诉我吗?

谢谢你,瑞安

SORT_ORDER = [
{column: 118, ascending: true},  // 3 = column number, sorting by descending order
{column: 119, ascending: true}, // 1 = column number, sort by ascending order 
{column: 117, ascending: true},
{column: 25, ascending: true}
];
function sortProductionLog(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(SHEET_NAME);
var LastRow_WithValue = sheet.getLastRow();
var LastColumn_WithValue = sheet.getLastColumn();
var buildRange = "4,1," + (LastRow_WithValue - 4) + "," + (LastColumn_WithValue - 1);
var range = sheet.getRange(buildRange);
ss.toast(buildRange);
range.sort
}

Answer 1

问题是您试图使用字符串"4, 1, ..."作为.getRange()参数。该函数最多接受4个数字，或一个"A1"格式的字符串。

源:

• 。getRange(row, column, numRows, numColumns)

推荐的解决办法:

var buildRange = [4,1, LastRow_WithValue - 4, LastColumn_WithValue - 1];
var range = sheet.getRange(...buildRange);

另外，这里有一个合并版本:

function sortProductionLog() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME)
sheet.getRange(4, 1, sheet.getLastRow()-4, sheet.getLastColumn()-1)
.sort(SORT_ORDER)
}

Answer 2

试试这样做:

function sortProductionLog() {
const ss = SpreadsheetApp.getActive()
var sh = ss.getSheetByName("SHEET_NAME");
var range = sh.getRange(4, 2, sh.getLastRow() - 3, sh.getLastColumn());
ss.toast(range.getA1Notation());
range.sort([{ column: 118, ascending: true }, { column: 119, ascending: true }, { column: 117, ascending: true }, { column: 25, ascending: true }]);
}