kotlingoogle-sheets-api

How do you convert A1 Notation into GridRange for Google Spreadsheet API

The Google Sheet API, in some cases, requires the expression of range using a GridRange, for example when you wish to format some cells, as shown here: https://developers.google.com/sheets/api/samples/formatting

However, I cannot find any conversion utility from A1 notation, e.g. Sheet2!A3:B4 into GridRange.

There are two difficulties, the first is knowing what sheetId corresponds to the named sheet and the second encoding the range into 0-based indices

Solution

  • First you need a live lookup to discover the sheetId.

    In Kotlin:

    import com.google.api.services.sheets.v4.Sheets

fun getSheetId(sheets: Sheets, spreadsheetId: String, sheetName: String): Int {
    val spreadsheet = sheets.spreadsheets().get(spreadsheetId).execute()
    val sheet = spreadsheet.sheets.firstOrNull { it.properties.title == sheetName }
    requireNotNull(sheet) {
        "Sheet $sheetName not found, only found these sheets: " +
                spreadsheet.sheets.map { it.properties.title }.joinToString()
    }
    return sheet.properties.sheetId
}

    Then to translate the coordinates I used a dependency from Apache POI. A better solution would be recode the 2 classes you need from there, since POI is big and brings other dependencies in turn you may not want:

    import com.google.api.services.sheets.v4.Sheets
import com.google.api.services.sheets.v4.model.GridRange
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.ss.util.CellReference

fun convertToGridRange(sheets: Sheets, spreadsheetId: String, range: String): GridRange {
    val sheetName = CellReference(range.substringBefore(":")).sheetName
    val cr = CellRangeAddress.valueOf(range)
    return GridRange().apply {
        sheetId = getSheetId(sheets, spreadsheetId, sheetName)
        startRowIndex = cr.firstRow
        startColumnIndex = cr.firstColumn
        endRowIndex = cr.lastRow + 1
        endColumnIndex = cr.lastColumn + 1
    }
}

    Unit Test

    @Test
fun convertToGridRangeTest() {
    val gr = googleSpreadsheetService.convertToGridRange(TEST_SHEET, "Sheet2!A3:B4")
    gr.sheetId shouldBe 413044831
    gr.startRowIndex shouldBe 2
    gr.startColumnIndex shouldBe 0
    gr.endRowIndex shouldBe 4
    gr.endColumnIndex shouldBe 2
}

    Dependencies are: