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 = 0val spreadsheet = service.spreadsheets().get(spreadsheetId).execute()val sheet = spreadsheet.sheets.find { sheet -> sheet.properties.sheetId == sheetId}// check if protected range already existval 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: