Google表格:通过Apps脚本批量获取getRangeByName

本文介绍了Google表格:通过Apps脚本批量获取getRangeByName的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

一次通话能否获得多个范围?我有一个复杂的函数，需要按名称捕获许多范围，而Spreadsheet.getRangeByName(name)大大降低了我的脚本速度.有时，这些通话大约需要2秒钟，有时一次通话可能需要45秒钟.

Is it possible to get multiple ranges by name with one call? I have a complex function that needs to grab many ranges by name and Spreadsheet.getRangeByName(name) is slowing down my script significantly. Sometimes these calls take ~2 seconds, and sometimes a single call can take ~45 seconds.

限制:

• 我需要获取范围(不仅仅是值)，因为脚本会格式化，获取A1Notation供以后使用，标记等.
• 这些命名范围的地址由于其他功能而改变

我尝试过的事情:

• 将范围对象存储在文档属性中.这似乎不起作用，因为范围仅具有功能. JSON.stringify(Range)不返回任何内容.
• 使用Google Sheets API v4的batchGet函数，但它似乎仅在获取值

推荐答案

对于命名范围，唯一的特定获取方法是通过工作簿的 getRangeByName 方法.还有工作簿级别和工作表级可用的收藏集:

For named ranges, the only method of specific acquisition is via the workbook's getRangeByName method. There are also workbook-level and sheet-level collections available:

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 类提供了一个非常小的速度提高，但目前仅接受A1/R1C1样式的输入数组，例如[ "A1", "B2", "C3" ]，并且要求范围在同一张纸上.

I originally misread your question as referring to acquiring multiple ranges by A1 notation. For this, the RangeList class offers a very minor speed improvement, but it currently only accepts an A1/R1C1-style input array, e.g. [ "A1", "B2", "C3" ], and requires the ranges to be on the same sheet.

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

如果运行此命令，然后检查Stackdriver，则可以看到执行时间，例如:

If you run this and then check Stackdriver, you can see the execution times, e.g.:

我为每个调用使用了不同的范围，以避免Apps Script服务器在方法比较之间缓存引用.

I used different ranges for each call to avoid the Apps Script server caching the reference between method comparisons.