读取和写入开发者元数据

借助开发者元数据功能,您可以将元数据与电子表格中的各种实体和位置相关联。然后,您可以查询此元数据,并使用它来查找与之关联的对象。

您可以将元数据与行、列、工作表或电子表格相关联。

利用开发者元数据,您可以执行以下操作:

  • 将任意数据与电子表格中的各种实体和位置相关联 - 例如,将 totals 与 D 列相关联,或将 responseId = 1234 与第 7 行相关联。

  • 查找与特定元数据键或属性关联的所有位置和数据 - 例如,如果给定与 D 列关联的键 totals 或给定 responseId,则返回包含 responseId 元数据以及与其关联的元数据值的所有行。

  • 查找与特定实体或位置关联的所有数据 - 例如,给定 D 列,返回与该位置关联的所有元数据。

  • 通过指定关联的元数据检索某个位置中的值 - 例如,假设 totals 返回关联列或行中包含的值的表示形式,或者指定 summary 会返回关联表格资源的表示形式。

  • 通过指定关联的元数据更新某个位置中的值 - 例如,通过指定元数据 ID 来更新值,而不是通过 A1 表示法更新某行中的值。

读取和写入元数据

通过 spreadsheets.developerMetadata 资源,您可以访问与电子表格中的位置或对象相关联的开发者元数据。

开发者元数据简介

本部分介绍在使用 Table API 时应考虑开发者元数据的一些关键方面。

使用元数据作为标记

开发者元数据的一种用途是仅使用键和位置命名电子表格中的位置。例如,您可以将 headerRow 与表格中的特定行关联,或将 totals 与工作表中的特定列关联。标记可用于从语义上将电子表格的某些部分绑定到第三方工具或数据库中的字段,因此对电子表格的更改不会中断您的应用。

元数据作为属性

通过指定键、位置和 value 创建的元数据充当与工作表中的该位置关联的键值对。例如,您可以关联:

  • formResponseId = resp123,包含一行
  • lastUpdated = 1477369882 替换为列。

这样,您可以存储和访问与电子表格中的特定区域或数据相关联的自定义命名属性。

项目与文档可见元数据

为防止一个开发者项目干扰另一个开发者项目的元数据,提供了 2 项元数据 visibility 设置:projectdocument。使用 Sheets API 时,项目元数据只能通过创建它的开发者项目查看和访问。文档元数据可从任何有权访问文档的开发者项目进行访问。

未明确指定可见性的查询会返回发出请求的开发者项目的匹配文档元数据和匹配项目元数据。

唯一性

元数据键不必是唯一的,但 metadataId 必须是不同的。如果您创建元数据但未指定其 ID 字段,则 API 会分配一个 ID。此 ID 可用于识别元数据,而键和其他属性可用于识别元数据集。

创建元数据

如需创建元数据,请使用 batchUpdate 方法,并提供包含 metadataKeylocationvisibilitycreateDeveloperMetadataRequest。您可以选择指定 metadataValue 或显式 metadataId

如果您指定的 ID 已被使用,请求将会失败。如果您不提供 ID,API 会分配一个 ID。

显示示例

在此示例中,我们在请求中提供了键、值和行。响应会返回这些开发者元数据值以及已分配的元数据 ID。

请求

{
  "requests": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "location": {
            "dimensionRange": {
              "sheetId": sheetId,
              "dimension": "ROWS",
              "startIndex": 6,
              "endIndex": 7
            }
          },
          "visibility": "DOCUMENT",
          "metadataKey": "Sales",
          "metadataValue": "2022"
        }
      }
    }
  ]
}

答案

{
  "spreadsheetId": spreadsheetId,
  "replies": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "metadataId": metadataId,
          "metadataKey": "Sales",
          "metadataValue": "2022",
          "location": {
            "locationType": "ROW",
            "dimensionRange": {
              "sheetId": sheetId,
              "dimension": "ROWS",
              "startIndex": 6,
              "endIndex": 7
            }
          },
          "visibility": "DOCUMENT"
        }
      }
    }
  ]
}

读取单个元数据项

要检索单个不同的开发者元数据,请使用 spreadsheets.developerMetadata.get 方法,并指定包含该元数据的 spreadsheetId 以及开发者元数据的唯一 metadataId

显示示例

请求

在此示例中,我们在请求中提供了电子表格 ID 和元数据 ID。响应会返回该元数据 ID 的开发者元数据值。

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/developerMetadata/metadataId

答案

{
  "metadataId": metadataId,
  "metadataKey": "Sales",
  "metadataValue": "2022",
  "location": {
    "locationType": "ROW",
    "dimensionRange": {
      "sheetId": sheetId,
      "dimension": "ROWS",
      "startIndex": 6,
      "endIndex": 7
    }
  },
  "visibility": "DOCUMENT"
}

读取多个元数据项

要检索多个开发者元数据项,请使用 spreadsheets.developerMetadata.search 方法。您需要指定一个 DataFilter,用于匹配键、值、位置或可见性等属性组合的任何现有元数据。

显示示例

在此示例中,我们在请求中提供了多个元数据 ID。响应会返回每个元数据 ID 的开发者元数据值。

请求

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": metadataId
      }
    },
    {
      "developerMetadataLookup": {
        "metadataId": metadataId
      }
    }
  ]
}

答案

{
  "matchedDeveloperMetadata": [
    {
      "developerMetadata": {
        "metadataId": metadataId,
        "metadataKey": "Revenue",
        "metadataValue": "2022",
        "location": {
          "locationType": "SHEET",
          "sheetId": sheetId
        },
        "visibility": "DOCUMENT"
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": metadataId
          }
        }
      ]
    },
    {
      "developerMetadata": {
        "metadataId": metadataId,
        "metadataKey": "Sales",
        "metadataValue": "2022",
        "location": {
          "locationType": "SHEET",
          "sheetId": sheetId
        },
        "visibility": "DOCUMENT"
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": metadataId
          }
        }
      ]
    }
  ]
}

更新元数据

如需更新开发者元数据,请使用 spreadsheets.batchUpdate 方法并提供 UpdateDeveloperMetadataRequest。您需要指定一个以要更新的元数据为目标的 DataFilter、一个具有新值的 DeveloperMetadata 对象,以及一个描述要更新的字段的字段掩码

显示示例

在此示例中,我们在请求中提供了元数据 ID、工作表 ID 和新的元数据键。响应会返回这些开发者元数据值,以及更新后的元数据键。

请求

{
  "requests": [
    {
      "updateDeveloperMetadata": {
        "dataFilters": [
          {
            "developerMetadataLookup": {
              "metadataId": metadataId
            }
          }
        ],
        "developerMetadata": {
          "location": {
            "sheetId": sheetId
          },
          "metadataKey": "SalesUpdated"
        },
        "fields": "location,metadataKey"
      }
    }
  ]
}

答案

{
  "spreadsheetId": spreadsheetId,
  "replies": [
    {
      "updateDeveloperMetadata": {
        "developerMetadata": [
          {
            "metadataId": metadataId,
            "metadataKey": "SalesUpdated",
            "metadataValue": "2022",
            "location": {
              "locationType": "SHEET",
              "sheetId": sheetId
            },
            "visibility": "DOCUMENT"
          }
        ]
      }
    }
  ]
}

删除元数据

如需删除开发者元数据,请使用 batchUpdate 方法,并提供 DeleteDeveloperMetadataRequest。您需要指定 DataFilter 以选择要删除的元数据。

显示示例

在此示例中,我们在请求中提供了元数据 ID。响应会返回该元数据 ID 的开发者元数据值。

要确认开发者元数据已移除,请使用 spreadsheets.developerMetadata.get 方法,指定已删除的元数据 ID。您应该会收到 404: Not Found HTTP 状态代码响应,以及内容为“没有 ID 为 metadataId 的开发者元数据。

请求

{
  "requests": [
    {
      "deleteDeveloperMetadata": {
        "dataFilter": {
          "developerMetadataLookup": {
            "metadataId": metadataId
          }
        }
      }
    }
  ]
}

答案

{
  "spreadsheetId": spreadsheetId,
  "replies": [
    {
      "deleteDeveloperMetadata": {
        "deletedDeveloperMetadata": [
          {
            "metadataId": metadataId,
            "metadataKey": "SalesUpdated",
            "metadataValue": "2022",
            "location": {
              "locationType": "SHEET",
              "sheetId": sheetId
            },
            "visibility": "DOCUMENT"
          }
        ]
      }
    }
  ]
}

读取和写入与元数据关联的值

您还可以通过指定关联的开发者元数据和要更新的值,检索和更新行和列中的单元格值。为此,请使用以下适当方法以及匹配的 DataFilter

按元数据获取单元格值

要按元数据获取单元格值,请使用 spreadsheets.values.batchGetByDataFilter 方法。您需要指定电子表格 ID 以及一个或多个与元数据匹配的数据过滤器。

显示示例

在此示例中,我们在请求中提供了元数据 ID。响应会返回元数据 ID 的行单元格值(型号、月销售额)。

请求

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": metadataId
      }
    }
  ],
  "majorDimension": "ROWS"
}

答案

{
  "spreadsheetId": spreadsheetId,
  "valueRanges": [
    {
      "valueRange": {
        "range": "Sheet7!A7:Z7",
        "majorDimension": "ROWS",
        "values": [
          [
            "W-24",
            "74"
          ]
        ]
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": metadataId
          }
        }
      ]
    }
  ]
}

按元数据获取电子表格

在检索电子表格时,您可以使用 spreadsheets.getByDataFilter 方法返回数据子集。您需要指定电子表格 ID 以及一个或多个与元数据匹配的数据过滤器。

此请求将作为常规的“电子表格 GET”请求来发挥作用,但指定的数据过滤器所匹配的元数据列表决定了要返回哪些工作表、网格数据以及包含元数据的其他对象资源。如果 includeGridData 设置为 true,则系统还会针对工作表返回与指定网格范围相交的网格数据。

显示示例

在此示例中,我们会提供元数据 ID,并在请求中将 includeGridData 设置为 false。响应会返回电子表格和工作表属性。

请求

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": metadataId
      }
    }
  ],
  "includeGridData": false
}

答案

{
  "spreadsheetId": spreadsheetId,
  "properties": {
    "title": "Sales Sheet",
    "locale": "en_US",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "America/Los_Angeles",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false,
        "foregroundColorStyle": {
          "rgbColor": {}
        }
      },
      "backgroundColorStyle": {
        "rgbColor": {
          "red": 1,
          "green": 1,
          "blue": 1
        }
      }
    },
    "spreadsheetTheme": {
      "primaryFontFamily": "Arial",
      "themeColors": [
        {
          "colorType": "TEXT",
          "color": {
            "rgbColor": {}
          }
        },
        {
          "colorType": "BACKGROUND",
          "color": {
            "rgbColor": {
              "red": 1,
              "green": 1,
              "blue": 1
            }
          }
        },
        {
          "colorType": "ACCENT1",
          "color": {
            "rgbColor": {
              "red": 0.25882354,
              "green": 0.52156866,
              "blue": 0.95686275
            }
          }
        },
        {
          "colorType": "ACCENT2",
          "color": {
            "rgbColor": {
              "red": 0.91764706,
              "green": 0.2627451,
              "blue": 0.20784314
            }
          }
        },
        {
          "colorType": "ACCENT3",
          "color": {
            "rgbColor": {
              "red": 0.9843137,
              "green": 0.7372549,
              "blue": 0.015686275
            }
          }
        },
        {
          "colorType": "ACCENT4",
          "color": {
            "rgbColor": {
              "red": 0.20392157,
              "green": 0.65882355,
              "blue": 0.3254902
            }
          }
        },
        {
          "colorType": "ACCENT5",
          "color": {
            "rgbColor": {
              "red": 1,
              "green": 0.42745098,
              "blue": 0.003921569
            }
          }
        },
        {
          "colorType": "ACCENT6",
          "color": {
            "rgbColor": {
              "red": 0.27450982,
              "green": 0.7411765,
              "blue": 0.7764706
            }
          }
        },
        {
          "colorType": "LINK",
          "color": {
            "rgbColor": {
              "red": 0.06666667,
              "green": 0.33333334,
              "blue": 0.8
            }
          }
        }
      ]
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": sheetId,
        "title": "Sheet7",
        "index": 7,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ],
  "spreadsheetUrl": spreadsheetUrl
}

按元数据更新值

要更新与特定元数据匹配的单元格值,请使用 spreadsheets.values.batchUpdateByDataFilter 方法。您需要指定电子表格 ID、valueInputOption 以及一个或多个与元数据匹配的 DataFilterValueRange

显示示例

在此示例中,我们在请求中提供了元数据 ID 和更新后的行值。响应会返回元数据 ID 的更新属性和数据。

请求

{
  "data": [
    {
      "dataFilter": {
        "developerMetadataLookup": {
          "metadataId": metadataId
        }
      },
      "majorDimension": "ROWS",
      "values": [
        [
          "W-24",
          "84"
        ]
      ]
    }
  ],
  "includeValuesInResponse": true,
  "valueInputOption": "USER_ENTERED"
}

答案

{
  "spreadsheetId": spreadsheetId,
  "totalUpdatedRows": 1,
  "totalUpdatedColumns": 2,
  "totalUpdatedCells": 2,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "updatedRange": "Sheet7!A7:B7",
      "updatedRows": 1,
      "updatedColumns": 2,
      "updatedCells": 2,
      "dataFilter": {
        "developerMetadataLookup": {
          "metadataId": metadataId
        }
      },
      "updatedData": {
        "range": "Sheet7!A7:Z7",
        "majorDimension": "ROWS",
        "values": [
          [
            "W-24",
            "84"
          ]
        ]
      }
    }
  ]
}

按元数据清除值

要清除与特定元数据匹配的单元格值,请使用 spreadsheets.values.batchClearByDataFilter 方法。您需要指定一个数据过滤器,以选择要清除的元数据。

显示示例

请求

在此示例中,我们在请求中提供了元数据 ID。响应会返回电子表格 ID 和清除的范围。

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": metadataId
      }
    }
  ]
}

答案

{
  "spreadsheetId": spreadsheetId,
  "clearedRanges": [
    "Sheet7!A7:Z7"
  ]
}

元数据存储限制

可存储在电子表格中的元数据总量存在限制。此限制以字符为单位,由 2 个部分组成:

商品 存储空间上限分配
电子表格 3 万个字符
电子表格中的每个工作表 3 万个字符

您最多可以为电子表格存储 3 万个字符。此外,您可以为电子表格中的每个工作表存储 3 万个字符(工作表 1 存储 30,000 个字符,工作表 2 存储 30,000 个字符,依此类推)。因此,包含 3 个页面的电子表格最多可包含 120,000 个字符的开发者元数据。

developerMetadata 对象的键和值属性中的每个字符都会计入此限额。