Android (Java/Kotlin) Google Sheets API: Protect Column/Cell From Edit

April 7, 2019

Calling setAddProtectedRange multiple times will create multiple protected range of the same, thus we check if the protected range already exist before creating.

val service = ...
val spreadsheetId = ...
val sheetId = 0

val spreadsheet = service.spreadsheets().get(spreadsheetId).execute()

val sheet = spreadsheet.sheets.find { sheet ->
    sheet.properties.sheetId == sheetId
}

// check if protected range already exist
val protectedRangeDescription = "ID Protect"
val protectedRange = sheet?.protectedRanges?.find {
    it.description == protectedRangeDescription
}

if (protectedRange == null) {
    val requests = listOf<Request>(
        // protect column A from edit with warning
        Request().setAddProtectedRange(
            AddProtectedRangeRequest().apply {
                this.protectedRange = ProtectedRange().apply {
                    range = GridRange().apply {
                        this.sheetId = sheetId
                        startColumnIndex = colToIndex('A')
                        endColumnIndex = colToIndex('A') + 1
                    }
                    description = protectedRangeDescription
                    warningOnly = true
                }
            }
        )
    )

    val body = BatchUpdateSpreadsheetRequest()
    .setRequests(requests)

    service.spreadsheets().batchUpdate(spreadsheetId, body).execute()
}
fun colToIndex(char: Char): Int {
    return char.toInt() - 65
}

NOTE: service is Sheets. Refer to Get Google Sheets service instance.

NOTE: Refer to Get Sheet By Id.

References:

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