Android (Java/Kotlin) Google Sheets API: Metadata (Hide note/string in Sheet)

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:

❤️ Is this article helpful?

Buy me a coffee ☕ or support my work via PayPal to keep this space 🖖 and ad-free.

Do send some 💖 to @d_luaz or share this article.

✨ By Desmond Lua

A dream boy who enjoys making apps, travelling and making youtube videos. Follow me on @d_luaz

👶 Apps I built

Travelopy - discover travel places in Malaysia, Singapore, Taiwan, Japan.