Read & write developer metadata

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 totals with column D, or responseId = 1234 with row 7.

  • Find all locations and data associated with a particular metadata key or attribute—For example, given the key totals associated with column D or given the responseId, return all rows with the responseId metadata 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 totals return a representation of the values contained in the associated column or row or given a summary return 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 = resp123 with a row
  • 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 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 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 FieldMask describing which fields should 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.