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

April 28, 2019

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 others
val 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:

This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.