You can embed Metadata (hidden note/string) in a sheet.
If you are looking of for adding note to cell, refer to Add Note to Cell.
Create Metadata
service is Sheets. Refer to Get Google Sheets service instance.
Refer to Get Sheet Id.
val METADATA_KEY = "version" // you might want to make this unique to avoid conflict with othersval METADATA_VALUE = "1"private fun createMetadata(service: Sheets, spreadsheetId: String, sheetId: Int) { val requests = listOf( Request().apply { // https://developers.google.com/sheets/api/guides/metadata#creating_new_metadata createDeveloperMetadata = CreateDeveloperMetadataRequest().apply { // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.developerMetadata#DeveloperMetadata developerMetadata = DeveloperMetadata().apply { // Cannot add developer metadata with ID [1] because developer metadata with that ID already exists. // metadataId = 1 metadataKey = App.Sheet.VERSION_KEY metadataValue = App.Sheet.VERSION_VALUE // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.developerMetadata#DeveloperMetadata.DeveloperMetadataVisibility visibility = "DOCUMENT" // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.developerMetadata#DeveloperMetadata.DeveloperMetadataLocation // Invalid requests[0].createDeveloperMetadata: developerMetadata.location.locationType is readonly and must not be specified. location = DeveloperMetadataLocation().apply { // locationType = "SHEET" this.sheetId = sheetId } } } }, Request().apply { createDeveloperMetadata = CreateDeveloperMetadataRequest().apply { developerMetadata = DeveloperMetadata().apply { metadataKey = METADATA_KEY metadataValue = METADATA_VALUE visibility = "DOCUMENT" location = DeveloperMetadataLocation().apply { this.sheetId = sheetId } } } } ) val body = BatchUpdateSpreadsheetRequest().setRequests(requests) service.spreadsheets().batchUpdate(spreadsheetId, body).execute()}NOTE: If you don't specify a metadataId, an id shall be generated for you. I prefer not to specified/hardcode a metadataId, thus I have to make sure my metadataKey is really unique as I search for this key by name instead.
NOTE: Before create metadata, you might want to check if the metadataId or metadataKey haven't already exist.
Get Metadata by ID
If you know the MetadataId
fun readMetadata(service: Sheets, spreadsheetId: String, metadataId: Int): DeveloperMetadata? { val metadata = service.spreadsheets().developerMetadata().get(spreadsheetId, metadataId).execute() Timber.d("metadataKey=${metadata.metadataKey}, metadataValue=${metadata.metadataValue}") return metadata}Search Metadata by Key Name
This example search for 2 keys
val METADATA_KEY_VERSION = "version"val METADATA_KEY_IS_ADMIN = "is_admin"class MetadataResult(val version: String, val isAdmin: Boolean)fun getMetadata(service: Sheets, spreadsheetId: String, sheetId: Int): MetadataResult? { val filters = listOf( // https://developers.google.com/sheets/api/reference/rest/v4/DataFilter DataFilter().apply { // https://developers.google.com/sheets/api/reference/rest/v4/DataFilter#DeveloperMetadataLookup developerMetadataLookup = DeveloperMetadataLookup().apply { metadataKey = METADATA_KEY_VERSION metadataLocation = DeveloperMetadataLocation().apply { this.sheetId = sheetId } } }, DataFilter().apply { developerMetadataLookup = DeveloperMetadataLookup().apply { metadataKey = METADATA_KEY_IS_ADMIN metadataLocation = DeveloperMetadataLocation().apply { this.sheetId = sheetId } } } ) val body = SearchDeveloperMetadataRequest() .setDataFilters(filters) // https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.developerMetadata/search val result = service.spreadsheets().developerMetadata().search(spreadsheetId, body).execute() var version: String? = null var isAdmin: Boolean? = null if (result.matchedDeveloperMetadata != null) { for (item in result.matchedDeveloperMetadata) { when (item.developerMetadata.metadataKey) { METADATA_KEY_VERSION -> version = item.developerMetadata.metadataValue METADATA_KEY_IS_ADMIN -> isAdmin = item.developerMetadata.metadataValue == "1" } } } if (version != null && isAdmin != null) { return MetadataResult(version, isAdmin) } return null}References: