GAS中DeveloperMetadata的实际用例 [英] Practical use case of DeveloperMetadata in GAS

问题描述

我最近发现与 DeveloperMetadata相关的已发布的新电子表格类和方法，我正在寻找代码示例，这些示例说明了这种数据结构的实际使用.

I recently found published new Classes and methods of Spreadsheet related to DeveloperMetadata, and I am seeking code examples showing practical use of such data structures.

我试图掌握元数据的概念，但到目前为止，它对我来说还是令人困惑.

I have tried to grasp the idea of metadata, but so far it is too confusing for me.

让我们假设这种情况-我正在为电子表格开发一些自动化.有带有订单的表(日期，客户名称，已售商品名称，价格等).在有界脚本中，我像这样sheet.getRange("H:H")(硬编码)处理价格列.但是，当工作表的用户在其他位置更改"H"列的位置时，脚本将停止正常工作-除非我将代码"H"更改为新的列字母.

Let's suppose this scenario - I'm developing some automation for spreadsheets. There is table with orders (date, customer name, sold item name, price, etc). In bounded script I address price column like this sheet.getRange("H:H") (hardcoded). But when an user of the sheet change position of a column "H" somewhere else the script will stop working properly - unless I change in code "H" to new column letter.

我正在寻找一些真实的GAS代码(不是Advanced Sheet服务)，这些代码将显示如何轻松处理这种情况.

I am seeking some real GAS code (not Advanced Sheet services) which will show how easily handle this situation.

我正在想像这样的东西(伪代码):

I'm imagining something like this (pseudo-code):

sheet.getRange("H:H").setMatadata("columnName","price"); //First set column identification

var priceColumnRange = sheet.getMetadaDataByKey("price").getRange(); //Then retrieve column range by its identification

我找到了一种将元数据设置为范围的方法，但是我没有找到如何轻松地检索(搜索/查找)此数据的方法.从文档上看确实很困难，而且我仍然不知道如何使用元数据来完成如此简单的任务.

I found a way how to set metadata to a range, but I didn't find a way how to easily retrieve (search/find) this data. It looks really difficult from documentation, and I still don't know how to use metadata for such an easy task.

推荐答案

• 您要使用Class DeveloperMetadata从行和列中搜索和检索DeveloperMetadata.
  • 您需要上述情况的示例脚本.
  • You want to search and retrieve DeveloperMetadata from rows and columns using Class DeveloperMetadata.
    • You want the sample scripts for above situation.

    如果我的理解是正确的，那么该示例脚本如何?我试图考虑这种情况，因为这对我的情况也很有用.我认为针对您的情况有几个示例脚本.因此，请将此视为其中之一.

    If my understanding is correct, how about this sample script? I tried to think of about this situation because this is also useful for my situation. I think that there are several sample scripts for your situation. So please think of this as one of them.

    在此示例脚本中，从表名为"Sheet1"的表中检索所有DeveloperMetadata.

    In this sample script, all DeveloperMetadata is retrieved from the sheet which has the sheet name of "Sheet1".

    var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().find();
    var res = v.map(function(e) {
      var loc = e.getLocation();
      var obj = loc.getLocationType() === SpreadsheetApp.DeveloperMetadataLocationType.COLUMN ? {range: loc.getColumn().getA1Notation()} :
        loc.getLocationType() === SpreadsheetApp.DeveloperMetadataLocationType.ROW ? {range: loc.getRow().getA1Notation()} : {};
      obj[e.getKey()] = e.getValue();
      return obj;
    });
    Logger.log(res)
    

    • 作为示例情况，将DeveloperMetadata设置为"A"列后，将其移至"B"列时，此脚本将检索"B"列的键和值.
    • 例如，如果要使用键和值进行搜索，则还可以使用以下脚本.
      • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").find();
      • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withValue("value").find();
      • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").withValue("value1").find();
      • As a sample situation, after DeveloperMetadata is set to the column "A", when it is moved to the column "B", this script retrieves the key and values of column "B".
      • For example, if you want to search using key and value, you can also use the following scripts.
        • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").find();
        • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withValue("value").find();
        • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").withValue("value1").find();

        将{key1: "value1"}和{key2: "value2"}的2个DeveloperMetadata设置为第1行和第A列时，将获得以下结果.

        When 2 DeveloperMetadata of {key1: "value1"} and {key2: "value2"} are set to row 1 and column A, the following result is obtained.

        [
          {
            "range": "1:1",
            "type": "ROW",
            "key1": "value1"
          },
          {
            "range": "A:A",
            "type": "COLUMN",
            "key2": "value2"
          }
        ]
        

        示例脚本2:

        另一种方法是，您还可以使用Sheets API搜索DeveloperMetadata，如下所示.使用Sheets API时，请在高级Google服务和API控制台中启用Sheets API.您可以在此处查看有关如何启用表格API的信息.

        Sample script 2:

        As another way, you can also search DeveloperMetadata using Sheets API as follows. When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

        var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataKey": "key1"}}]}; // Search by key
        // var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataValue": "value1"}}]}; // Search by value
        // var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataKey": "key1", "metadataValue": "value1"}}]}; // Search by key and value
        var res = Sheets.Spreadsheets.DeveloperMetadata.search(resource, spreadsheetId);
        

        参考文献:

        • Class DeveloperMetadata
        • spreadsheets.developerMetadata.search

        References:

        • Class DeveloperMetadata
        • spreadsheets.developerMetadata.search

        如果这不是您想要的，对不起.

        If this was not what you want, I'm sorry.

        关于第二个问题.

        1. 在"H:H"中添加元数据作为"columnName"的键和"price"的值.
        2. 如果列"H"已移动，则表示您希望使用元数据的值来检索新范围.

        如果我的理解是正确的，那该怎么办?

        If my understanding is correct, how about this?

        为了将元数据添加到"H"列，可以使用以下脚本.因为没有setMatadata()的方法，请使用addDeveloperMetadata().

        In order to add a metadata to the column "H", you can use the following script. Because there is no method of setMatadata(), please use addDeveloperMetadata().

        var sheet = SpreadsheetApp.getActiveSheet();
        sheet.getRange("H:H").addDeveloperMetadata("columnName", "price");
        

        使用值检索范围

        此示例脚本使用元数据值检索范围.在此脚本中，envn如果移动了"H"列，则可以检索使用元数据值的新范围.

        Retrieve ranges using value

        This sample script retrieves ranges using a value of metadata. In this script, envn if the column "H" was moved, the new range using the value of metadata can be retrieved.

        var sheet = SpreadsheetApp.getActiveSheet();
        var value = "price";
        var v = sheet.getRange(1, 1, 1, sheet.getMaxColumns())
          .createDeveloperMetadataFinder()
          .onIntersectingLocations()
          .withValue(value)
          .find();
        var ranges = v.map(function(e) {return e.getLocation().getColumn()}); // "ranges" is one dimensional array.
        

        这篇关于GAS中DeveloperMetadata的实际用例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！