The developer metadata feature lets you associate metadata with various entities and locations in a spreadsheet. You can then query this metadata and use it to find the objects with which it's associated.
You can associate metadata with rows, columns, sheets, or a spreadsheet.
Developer metadata lets you perform operations such as:
Associate arbitrary data with various entities and locations in a spreadsheet—For example, associate
totalswith column D, orresponseId = 1234with row 7.Find all locations and data associated with a particular metadata key or attribute—For example, given the key
totalsassociated with column D or given theresponseId, return all rows with theresponseIdmetadata and the metadata value associated with them.Find all data associated with a particular entity or location—For example, given column D, return all metadata associated with that location.
Retrieve values in a location by specifying associated metadata—For example, given the
totalsreturn a representation of the values contained in the associated column or row or given asummaryreturn a representation of the associated Sheet resource.Update values in a location by specifying associated metadata—For example, instead of updating the values in a row through A1 notation, update values by indicating a metadata ID.
Read & write metadata
The spreadsheets.developerMetadata resource provides access to developer metadata associated with a location or object in a spreadsheet.
About developer metadata
This section describes some key aspects of developer metadata you should consider when working with Sheets API.
Metadata as tags
One use of developer metadata is a tag that names a location in the
spreadsheet using only a key and a location. For
example, you can associate headerRow with a particular row or totals with
a particular column within a sheet. Tags can be used to semantically bind portions of a
spreadsheet to fields in a third-party tool or database, so changes to the
spreadsheet won't break your app.
Metadata as properties
Metadata created by specifying a key, location, and a value acts as a key-value pair associated with that location in a sheet. For example, you can associate:
formResponseId = resp123with a rowlastUpdated = 1477369882with a column.
This lets you store and access custom named properties associated with particular areas or data in a spreadsheet.
Project vs. document visible metadata
To prevent one developer project from interfering with another's metadata, there
are 2 metadata visibility settings: project and document. Using the Sheets API,
project metadata is only visible and accessible from the developer project that
created it. Document metadata is accessible from any developer project with
access to the document.
Queries that don't explicitly specify a visibility return matching document metadata and matching project metadata for the developer project making the request.
Uniqueness
Metadata keys don't have to be unique, but the metadataId must be
distinct. If you create metadata and leave its ID field unspecified, the
API assigns one. This ID can be used to identify the
metadata, while keys and other attributes can be used to identify sets of
metadata.
Create metadata
To create metadata, use the
batchUpdate
method, and supply a
createDeveloperMetadataRequest with a metadataKey, location, and visibility. You can optionally
specify a metadataValue or an explicit metadataId.
If you specify an ID that's already in use, the request will be unsuccessful. If you don't supply an ID, the API assigns one.
Show an example
In this example, we provide a key, value, and a row in the request. The response returns these developer metadata values, plus the assigned metadata ID.
Request
{
"requests": [
{
"createDeveloperMetadata": {
"developerMetadata": {
"location": {
"dimensionRange": {
"sheetId": sheetId,
"dimension": "ROWS",
"startIndex": 6,
"endIndex": 7
}
},
"visibility": "DOCUMENT",
"metadataKey": "Sales",
"metadataValue": "2022"
}
}
}
]
}
Response
{
"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"
}
}
}
]
}
Read a single metadata item
To retrieve a single, distinct developer metadata, use the
spreadsheets.developerMetadata.get
method, specifying the spreadsheetId containing the metadata and the developer metadata's unique metadataId.
Show an example
Request
In this example, we provide the spreadsheet ID and metadata ID in the request. The response returns the developer metadata values for the metadata ID.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/developerMetadata/metadataId
Response
{
"metadataId": metadataId,
"metadataKey": "Sales",
"metadataValue": "2022",
"location": {
"locationType": "ROW",
"dimensionRange": {
"sheetId": sheetId,
"dimension": "ROWS",
"startIndex": 6,
"endIndex": 7
}
},
"visibility": "DOCUMENT"
}
Read multiple metadata items
To retrieve multiple items of developer metadata, use the
spreadsheets.developerMetadata.search
method. You'll need to specify a DataFilter that matches any existing metadata on any
combination of properties such as key, value, location, or visibility.
Show an example
In this example, we provide multiple metadata IDs in the request. The response returns the developer metadata values for each metadata ID.
Request
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
},
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
]
}
Response
{
"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
}
}
]
}
]
}
Update metadata
To update developer metadata, use the
spreadsheets.batchUpdate
method and supply an
UpdateDeveloperMetadataRequest.
You'll need to specify a
DataFilter
that targets the metadata to be updated, a
DeveloperMetadata
object with the new values, and a field mask
describing the fields to be updated.
Show an example
In this example, we provide the metadata ID, sheet ID, and a new metadata key in the request. The response returns these developer metadata values, plus the updated metadata key.
Request
{
"requests": [
{
"updateDeveloperMetadata": {
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
],
"developerMetadata": {
"location": {
"sheetId": sheetId
},
"metadataKey": "SalesUpdated"
},
"fields": "location,metadataKey"
}
}
]
}
Response
{
"spreadsheetId": spreadsheetId,
"replies": [
{
"updateDeveloperMetadata": {
"developerMetadata": [
{
"metadataId": metadataId,
"metadataKey": "SalesUpdated",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": sheetId
},
"visibility": "DOCUMENT"
}
]
}
}
]
}
Delete metadata
To delete developer metadata, use the
batchUpdate
method, and supply a
DeleteDeveloperMetadataRequest.
You'll need to specify a DataFilter to select the metadata you want to
delete.
Show an example
In this example, we provide the metadata ID in the request. The response returns the developer metadata values for the metadata ID.
To confirm the developer metadata is removed, use the spreadsheets.developerMetadata.get
method, specifying the deleted metadata ID. You should receive a receive a 404: Not Found HTTP status code response, with a message stating "No developer metadata with ID metadataId.
Request
{
"requests": [
{
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataId": metadataId
}
}
}
}
]
}
Response
{
"spreadsheetId": spreadsheetId,
"replies": [
{
"deleteDeveloperMetadata": {
"deletedDeveloperMetadata": [
{
"metadataId": metadataId,
"metadataKey": "SalesUpdated",
"metadataValue": "2022",
"location": {
"locationType": "SHEET",
"sheetId": sheetId
},
"visibility": "DOCUMENT"
}
]
}
}
]
}
Read & write values associated with metadata
You can also retrieve and update cell values in rows and columns by specifying the associated developer
metadata and the values you wish to update. To do this, use the appropriate method below with a
matching DataFilter.
Get cell values by metadata
To get cell values by metadata, use the spreadsheets.values.batchGetByDataFilter method. You'll need to specify the spreadsheet ID and one or more data filters that match the metadata.
Show an example
In this example, we provide the metadata ID in the request. The response returns the row cell values (model number, monthly sales) for the metadata ID.
Request
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
],
"majorDimension": "ROWS"
}
Response
{
"spreadsheetId": spreadsheetId,
"valueRanges": [
{
"valueRange": {
"range": "Sheet7!A7:Z7",
"majorDimension": "ROWS",
"values": [
[
"W-24",
"74"
]
]
},
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
]
}
]
}
Get spreadsheet by metadata
When retrieving a spreadsheet, you can return a subset of data by using the spreadsheets.getByDataFilter method. You'll need to specify the spreadsheet ID and one or more data filters that match the metadata.
This request functions as a regular "spreadsheet GET" request except the
list of metadata matched by the specified data filters determines what sheets,
grid data, and other object resources with metadata are returned. If
includeGridData is set to true, grid data intersecting the specified grid ranges
is also returned for the sheet.
Show an example
In this example, we provide the metadata ID and set includeGridData to false in the request. The response returns both the spreadsheet and sheet properties.
Request
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
],
"includeGridData": false
}
Response
{
"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
}
Update values by metadata
To update cell values matching specific metadata, use the
spreadsheets.values.batchUpdateByDataFilter
method. You'll need to specify the spreadsheet ID, valueInputOption, and one or more DataFilterValueRange that matches the metadata.
Show an example
In this example, we provide the metadata ID and updated row values in the request. The response returns both the updated properties and data for the metadata ID.
Request
{
"data": [
{
"dataFilter": {
"developerMetadataLookup": {
"metadataId": metadataId
}
},
"majorDimension": "ROWS",
"values": [
[
"W-24",
"84"
]
]
}
],
"includeValuesInResponse": true,
"valueInputOption": "USER_ENTERED"
}
Response
{
"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"
]
]
}
}
]
}
Clear values by metadata
To clear cell values matching specific metadata, use the spreadsheets.values.batchClearByDataFilter method. You'll need to specify a data filter to select the metadata you want to clear.
Show an example
Request
In this example, we provide the metadata ID in the request. The response returns the spreadsheet ID and the cleared ranges.
{
"dataFilters": [
{
"developerMetadataLookup": {
"metadataId": metadataId
}
}
]
}
Response
{
"spreadsheetId": spreadsheetId,
"clearedRanges": [
"Sheet7!A7:Z7"
]
}
Metadata storage limits
There's a limit on the total amount of metadata you can store in a spreadsheet. This limit is measured in characters and is made up of 2 components:
| Item | Storage limit allocation |
|---|---|
| Spreadsheet | 30,000 characters |
| Each sheet within a spreadsheet | 30,000 characters |
You can store up to 30,000 characters for the spreadsheet. In addition, you can store 30,000 characters for each sheet within a spreadsheet (30,000 for sheet one, 30,000 for sheet 2, and so forth). So a spreadsheet with 3 pages could contain up to 120,000 characters of developer metadata.
Each character in the key and value attributes of a developerMetadata object
count toward this limit.