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 look up the objects with which it is associated.
Developer metadata lets you perform operations such as:
Associate arbitrary data with various entities and locations in a spreadsheet. For example, associating "totals" with column D, or "responseId -> 1234" with row 7.
Look up all locations and data associated with a particular metadata key or attribute. For example, given the key "totals" return that it is associated with column D, or given "responseId" return the set of all rows with "responseId" metadata, and the metadata value associated with it.
Look up 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 "totals" return a representation of the values contained in the associated column or row; or given "summary" return a representation of the associated Sheet resource.
Update values in a location by specifying associated metadata. For example, instead of updating values in a row by providing the A1 range, update values by indicating a metadata ID.
You can associate data with rows, columns, sheets, or the spreadsheet.
Reading and writing metadata
About developer metadata
This section describes some important aspects of developer metadata that you should consider when working with the API.
Metadata as tags
One use of developer metadata is as a tag that names a location in the spreadsheet; metadata that does this specifies only a key and a location. For example, you can associate "headerRow" with a particular row; or "totals" with a particular column. This 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, as well as a value behaves as a key-value pair associated with that location; for example, you can do things like:
- associate "formResponseId -> resp123" with a row
- associate "lastUpdated -> 1477369882" with 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 two metadata visibilities, project and document. Using the API, project metadata is only visible and accessible from the developer project that created it; document metadata will be accessible from any developer project with access to the document.
Queries that don't explicitly specify a visibility will return matching document metadata as well as matching project metadata for the developer project making the request.
Metadata keys do not have to be unique, however metadata IDs do have to be unique. If you create new metadata and leave its ID field unspecified, then the API creates and assigns one. This ID can be used to uniquely identify the metadata, while keys and other attributes can be used to identify sets of metadata.
Creating new metadata
To create metadata, use the batchUpdate method, and supply a createDeveloperMetadataRequest. You'll need to provide at least a key and a location. You can also specify a value, visibility, or an explicit id.
If you specify an ID that is already in use the, request will fail. If you don't supply an ID, one is generated for you.
Reading a single metadata item
To retrieve a single, unique developer metadata, use the spreadsheets.developerMetadata.get method, specifying a unique spreadsheetId and metadataId.
Reading multiple metadata items
To retrieve multiple items of developer metadata, use the spreadsheets.developerMetadata.search method, specifying a DataFilter that matches existing metadata on any combination of properties such as key, value, location, or visibility.
To update metadata, use the batchUpdate method, and supply an UpdateDeveloperMetadataRequest. You'll need to specify a DataFilter that targets the metadata to be updated, a DeveloperMetadata object containing the new values, and a FieldMask describing which fields should be updated.
Reading and writing values associated with metadata
You can also update cell values in rows and columns by specifying associated metadata and the values they wish to update.
Retrieving values and objects associated with metadata
You can retrieve cell values and object resources associated with specific
metadata. To do this, use the appropriate method (see below) with a
DataFilter that matches the metadata.
Get values by metadata
To get cell values by metadata, use the spreadsheets.values.batchGetByDataFilter method, specifying a DataFilter that matches the metadata.
Get spreadsheet by metadata
When retrieving a spreadsheet, you can target the subset of data to be returned by providing a data filter. Use the spreadsheets.getByDataFilter method, specifying a DataFilter that matches the metadata.
This request functions as a normal "spreadsheet GET" request except that the
list of metadata matched by the specified data filters determines what sheets,
grid data (and other objects with metadata) are returned. If
includeGridData is set to true, grid data intersecting the specified ranges
is also returned for the sheet.
Updating values by metadata
To update the value of cells that match specific metadata, use the spreadsheets.values.batchUpdateByDataFilter method, specifying a DataFilter that matches the metadata.
Metadata storage limits
There is a limit on the total amount of metadata that you may store in a spreadsheet. This limit is measured in characters and is made up of two components:
|Item||Storage limit allocation|
|Each sheet in the spreadsheet||30,000 characters|
You can store up to 30,000 characters for the spreadsheet. In addition to that, you can store an additional 30,000 for each sheet in the spreadsheet: 30,000 for sheet one, 30,000 for sheet two, and so forth. So a spreadsheet with three pages can store up to 120,000 characters of developer metadata.
Each character in the "key" and "value" attributes of the DeveloperMetadata counts towards this limit.