对于命名范围,特定获取的唯一方法是通过工作簿的getRangeByName https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getRangeByName(String)方法。还有工作簿级别 https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getNamedRanges() and 工作表级 https://developers.google.com/apps-script/reference/spreadsheet/sheet#getNamedRanges()可用集合:
var namedRangeArray = SpreadsheetApp.getActive().getNamedRanges();
var a1array = [];
namedRangeArray.forEach(function (namedRange) {
var range = namedRange.getRange();
a1array.push(range.getA1Notation()); // Doesn't include a sheet reference...
...
});
我最初误读你的问题是指通过 A1 表示法获取多个范围。为此,RangeList https://developers.google.com/apps-script/reference/spreadsheet/range-listclass 提供了非常小的速度改进,但它目前仅接受 A1/R1C1 样式的输入数组,例如[ "A1", "B2", "C3" ]
,并要求范围位于同一张纸上。
function compare_GetRange_And_GetRangeList() {
var rangeStrings = [
"A1:A100", "B2:B101", "C3:C102"
];
var sheet = SpreadsheetApp.getActive().getActiveSheet()
// Acquire multiple.
console.time("Get rangelist");
var ranges = sheet.getRangeList(rangeStrings);
console.timeEnd("Get rangelist");
// Acquire single.
var separate = [];
console.time("Get individual");
["D4:D103", "E5:E104", "F6:F105"].forEach(function (a1) {
separate.push(sheet.getRange(a1));
});
console.timeEnd("Get individual");
// Do something with the acquired ranges.
[].concat(ranges.getRanges(), separate).forEach(function (rl) {
console.log(rl.getA1Notation());
});
}
If you run this and then check Stackdriver, you can see the execution times, e.g.:
我为每次调用使用不同的范围,以避免 Apps 脚本服务器缓存方法比较之间的引用。