Функция метаданных позволяет связывать метаданные с различными объектами и местоположениями в электронной таблице. Затем вы можете запрашивать эти метаданные и использовать их для поиска объектов, с которыми они связаны.
Вы можете связывать метаданные со строками, столбцами, листами или электронными таблицами.
О метаданных
Ниже описаны некоторые ключевые аспекты метаданных, которые следует учитывать при работе с API Google Sheets:
Метаданные в виде тегов : Один из способов использования метаданных разработчика — это тег , который указывает местоположение в электронной таблице, используя только ключ и адрес. Например, вы можете связать
headerRowс определенной строкой илиtotalsс определенным столбцом в листе. Теги можно использовать для семантической привязки частей электронной таблицы к полям в стороннем инструменте или базе данных, чтобы изменения в электронной таблице не нарушили работу вашего приложения.Метаданные как свойства : Метаданные, созданные путем указания ключа, местоположения и значения, действуют как пара «ключ-значение», связанная с этим местоположением на листе. Например, вы можете связать:
-
formResponseId = resp123с строкой -
lastUpdated = 1477369882с колонкой
Это позволяет хранить и получать доступ к пользовательским именованным свойствам, связанным с определенными областями или данными в электронной таблице.
-
Видимость метаданных проекта и документа : Чтобы предотвратить конфликт между метаданными одного проекта разработчика и метаданными другого, существуют два параметра
visibilityметаданных:projectиdocument. При использовании API Google Sheets метаданныеprojectвидны и доступны только из того проекта Google Cloud, который его создал. Метаданныеdocumentдоступны из любого проекта Google Cloud, имеющего доступ к документу.Запросы, в которых явно не указана
visibility, возвращают соответствующие метаданныеdocumentи соответствующие метаданныеprojectдля проекта Google Cloud, выполняющего запрос.Уникальность : ключи метаданных не обязательно должны быть уникальными, но идентификатор
metadataIdдолжен быть уникальным. Если вы создаете метаданные и оставляете поле ID неуказанным, API присваивает ему идентификатор. Этот ID может использоваться для идентификации метаданных, в то время как ключи и другие атрибуты могут использоваться для идентификации наборов метаданных.Возврат метаданных через API-запросы : объект
DataFilterявляется частью вызова API и описывает данные, которые должны быть выбраны или возвращены из API-запроса.Один объект
DataFilterможет указывать только один тип критериев фильтрации для поиска данных:developerMetadataLookup: Выбирает данные, связанные с указанными метаданными разработчика, соответствующими заданным критериям.a1Range: Выбирает данные, соответствующие указанному диапазону в формате A1 . Например,Sheet1!A1:B10.gridRange: Выбирает данные, соответствующие указанному диапазону сетки, используя индексы, начинающиеся с нуля. Например,Sheet1!A3:B4 == sheetId: 123456, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2.
Для фильтрации по нескольким местоположениям или критериям можно использовать несколько объектов
DataFilterв одном API-запросе. Передайте массив или список объектовDataFilterв пакетный запрос, например, методspreadsheets.values.batchGetByDataFilter. Любой диапазон, соответствующий любому из фильтров данных в запросе, будет возвращен или изменен.Для получения дополнительной информации см. раздел «Чтение и запись значений, связанных с метаданными» .
Варианты использования
Ниже приведены несколько примеров использования управления метаданными:
Связывайте произвольные данные с различными объектами и местоположениями в электронной таблице : например, свяжите
totalsсо столбцом D илиresponseId = 1234со строкой 7.Найдите все местоположения и данные, связанные с определенным ключом метаданных или атрибутом : например, имея
totalsключа, связанные со столбцом D, или заданныйresponseId, верните все строки с метаданнымиresponseIdи связанным с ними значением метаданных.Найти все данные, связанные с конкретным объектом или местоположением : например, для заданного столбца D вернуть все метаданные, связанные с этим местоположением.
Получение значений в определенном месте путем указания связанных метаданных : например, по
totalsзначениям возвращается представление значений, содержащихся в соответствующем столбце или строке, а поsummary— представление соответствующего ресурса таблицы.Обновите значения в определенном месте, указав связанные метаданные : например, вместо обновления значений в строке с помощью обозначения A1 , обновите значения, указав идентификатор метаданных.
Чтение и запись метаданных
Ресурс spreadsheets.developerMetadata предоставляет доступ к метаданным, связанным с местоположением или объектом в электронной таблице. Метаданные разработчика могут использоваться для связывания произвольных данных с различными частями электронной таблицы. Метаданные остаются связанными с этими местами по мере редактирования электронной таблицы.
Создать метаданные
Для создания метаданных используйте метод batchUpdate ресурса spreadsheets и передайте объекту CreateDeveloperMetadataRequest значения metadataKey , location и visibility из ресурса spreadsheets.developerMetadata . При желании вы можете указать metadataValue или явно заданный metadataId .
Если вы укажете идентификатор, который уже используется, запрос будет отклонен. Если вы не укажете идентификатор, API присвоит его автоматически.
В этом примере мы указываем в запросе ключ, значение и строку. В ответе возвращаются эти значения метаданных разработчика, а также присвоенный идентификатор метаданных.
Запрос
{
"requests": [
{
"createDeveloperMetadata": {
"developerMetadata": {
"location": {
"dimensionRange": {
"sheetId": SHEET_ID,
"dimension": "ROWS",
"startIndex": 6,
"endIndex": 7
}
},
"visibility": "DOCUMENT",
"metadataKey": "Sales",
"metadataValue": "2022"
}
}
}
]
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"replies": [
{
"createDeveloperMetadata": {
"developerMetadata": {
"metadataId": METADATA_ID,
"metadataKey": "Sales",
"metadataValue": "2022",
"location": {
"locationType": "ROW",
"dimensionRange": {
"sheetId": SHEET_ID,
"dimension": "ROWS",
"startIndex": 6,
"endIndex": 7
}
},
"visibility": "DOCUMENT"
}
}
}
]
}Прочитать отдельный элемент метаданных
Для получения единственного уникального набора метаданных разработчика используйте метод spreadsheets.developerMetadata.get , указав spreadsheetId , содержащий метаданные, и уникальный metadataId метаданных разработчика.
Запрос
В этом примере мы указываем в запросе идентификатор электронной таблицы и идентификатор метаданных. В ответе возвращаются значения метаданных разработчика для идентификатора метаданных.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/developerMetadata/METADATA_ID
Ответ
{
"metadataId": METADATA_ID,
"metadataKey": "Sales",
"metadataValue": "2022",
"location": {
"locationType": "ROW",
"dimensionRange": {
"sheetId": SHEET_ID,
"dimension": "ROWS",
"startIndex": 6,
"endIndex": 7
}
},
"visibility": "DOCUMENT"
}Прочитать несколько элементов метаданных
Для получения нескольких элементов метаданных разработчика используйте метод spreadsheets.developerMetadata.search . Необходимо указать DataFilter , который соответствует любым существующим метаданным по любой комбинации свойств, таких как ключ, значение, местоположение или видимость.
В этом примере мы указываем в запросе несколько идентификаторов метаданных. В ответе возвращаются значения метаданных разработчика для каждого идентификатора метаданных.
Запрос
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
},
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
]
}Ответ
{
"matchedDeveloperMetadata": [
{
"developerMetadata": {
"metadataId": METADATA_ID,
"metadataKey": "Revenue",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": SHEET_ID
},
"visibility": "DOCUMENT"
},
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
]
},
{
"developerMetadata": {
"metadataId": METADATA_ID,
"metadataKey": "Sales",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": SHEET_ID
},
"visibility": "DOCUMENT"
},
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
]
}
]
}Обновить метаданные
Для обновления метаданных разработчика используйте метод spreadsheets.batchUpdate и укажите объект UpdateDeveloperMetadataRequest . Необходимо указать DataFilter , указывающий на обновляемые метаданные, ресурс spreadsheets.developerMetadata с новыми значениями и маску поля , описывающую поля для обновления.
В этом примере мы указываем в запросе идентификатор метаданных, идентификатор листа и новый ключ метаданных. В ответе возвращаются эти значения метаданных разработчика, а также обновленный ключ метаданных.
Запрос
{
"requests": [
{
"updateDeveloperMetadata": {
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
],
"developerMetadata": {
"location": {
"sheetId": SHEET_ID
},
"metadataKey": "SalesUpdated"
},
"fields": "location,metadataKey"
}
}
]
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"replies": [
{
"updateDeveloperMetadata": {
"developerMetadata": [
{
"metadataId": METADATA_ID,
"metadataKey": "SalesUpdated",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": SHEET_ID
},
"visibility": "DOCUMENT"
}
]
}
}
]
}Удалить метаданные
Для удаления метаданных разработчика используйте метод batchUpdate и укажите объект DeleteDeveloperMetadataRequest . Необходимо указать DataFilter для выбора метаданных, которые вы хотите удалить.
В этом примере мы указываем идентификатор метаданных в запросе. В ответе возвращаются значения метаданных разработчика для этого идентификатора.
Чтобы подтвердить удаление метаданных разработчика, используйте метод spreadsheets.developerMetadata.get , указав идентификатор удаленных метаданных. Вы должны получить ответ с кодом состояния HTTP 404: Not Found и сообщением "Нет метаданных разработчика с идентификатором METADATA_ID ".
Запрос
{
"requests": [
{
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
}
}
]
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"replies": [
{
"deleteDeveloperMetadata": {
"deletedDeveloperMetadata": [
{
"metadataId": METADATA_ID,
"metadataKey": "SalesUpdated",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": SHEET_ID
},
"visibility": "DOCUMENT"
}
]
}
}
]
}Чтение и запись значений, связанных с метаданными.
Вы также можете получать и обновлять значения ячеек в строках и столбцах, указывая связанные метаданные разработчика и значения, которые хотите обновить. Для этого используйте один из следующих методов с соответствующим DataFilter .
Получение значений ячеек по метаданным
Для получения значений ячеек по метаданным используйте метод spreadsheets.values.batchGetByDataFilter . Необходимо указать идентификатор электронной таблицы и один или несколько фильтров данных, соответствующих метаданным.
В этом примере мы указываем идентификатор метаданных в запросе. В ответе возвращаются значения ячеек строки (номер модели, ежемесячные продажи) для указанного идентификатора метаданных.
Запрос
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
],
"majorDimension": "ROWS"
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"valueRanges": [
{
"valueRange": {
"range": "Sheet7!A7:Z7",
"majorDimension": "ROWS",
"values": [
[
"W-24",
"74"
]
]
},
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
]
}
]
}Получить электронную таблицу по метаданным
При извлечении данных из электронной таблицы можно получить подмножество данных, используя метод spreadsheets.getByDataFilter . Необходимо указать идентификатор электронной таблицы и один или несколько фильтров данных, соответствующих метаданным.
Этот запрос функционирует как обычный запрос типа "получить данные из электронной таблицы", за исключением того, что список метаданных, соответствующих указанным фильтрам данных, определяет, какие листы, данные сетки и другие объектные ресурсы с метаданными будут возвращены. Если includeGridData установлен в true , данные сетки, пересекающие указанные диапазоны сетки, также будут возвращены для листа. Поле includeGridData игнорируется, если в запросе задана маска поля .
В этом примере мы указываем идентификатор метаданных и устанавливаем includeGridData в false в запросе. В ответе возвращаются свойства как электронной таблицы, так и листа.
Запрос
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
],
"includeGridData": false
}Ответ
{ "spreadsheetId": SPREADSHEET_ID, "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": SHEET_ID, "title": "Sheet7", "index": 7, "sheetType": "GRID", "gridProperties": { "rowCount": 1000, "columnCount": 26 } } } ], "spreadsheetUrl": SPREADSHEET_URL }
Обновление значений на основе метаданных
Для обновления значений ячеек, соответствующих определенным метаданным, используйте метод spreadsheets.values.batchUpdateByDataFilter . Необходимо указать идентификатор электронной таблицы, valueInputOption и одно или несколько значений DataFilterValueRange , соответствующих метаданным.
В этом примере мы указываем идентификатор метаданных и обновленные значения строк в запросе. В ответе возвращаются как обновленные свойства, так и данные для идентификатора метаданных.
Запрос
{
"data": [
{
"dataFilter": {
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
},
"majorDimension": "ROWS",
"values": [
[
"W-24",
"84"
]
]
}
],
"includeValuesInResponse": true,
"valueInputOption": "USER_ENTERED"
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"totalUpdatedRows": 1,
"totalUpdatedColumns": 2,
"totalUpdatedCells": 2,
"totalUpdatedSheets": 1,
"responses": [
{
"updatedRange": "Sheet7!A7:B7",
"updatedRows": 1,
"updatedColumns": 2,
"updatedCells": 2,
"dataFilter": {
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
},
"updatedData": {
"range": "Sheet7!A7:Z7",
"majorDimension": "ROWS",
"values": [
[
"W-24",
"84"
]
]
}
}
]
}Очистка значений с помощью метаданных
Для очистки значений ячеек, соответствующих определенным метаданным, используйте метод spreadsheets.values.batchClearByDataFilter . Необходимо указать фильтр данных, чтобы выбрать метаданные, которые вы хотите очистить.
Запрос
В этом примере мы указываем идентификатор метаданных в запросе. В ответ возвращается идентификатор электронной таблицы и очищенные диапазоны.
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": METADATA_ID
}
}
]
}Ответ
{
"spreadsheetId": SPREADSHEET_ID,
"clearedRanges": [
"Sheet7!A7:Z7"
]
}Ограничения на хранение метаданных
В электронных таблицах существует ограничение на общий объем метаданных, которые можно хранить. Это ограничение измеряется в символах и состоит из двух компонентов:
| Элемент | выделение лимита хранилища |
|---|---|
| Электронная таблица | 30 000 символов |
| Каждый лист в электронной таблице | 30 000 символов |
В электронной таблице можно хранить до 30 000 символов. Кроме того, для каждого листа в таблице можно хранить по 30 000 символов (30 000 для первого листа, 30 000 для второго и так далее). Таким образом, электронная таблица с тремя листами может содержать до 120 000 символов метаданных.
Каждый символ в полях metadataKey и metadataValue ресурса spreadsheets.developerMetadata учитывается при расчете этого лимита.
Связанные темы
- Примените фильтры к данным в Google Sheets.
- Управляйте видимостью данных с помощью фильтров.
- Ограничения на использование