如何将A1符号转换为谷歌电子表格API的GridRange

Question

Google Sheet API在某些情况下需要使用GridRange来表示范围，例如，当您希望格式化某些单元格时，如下所示：https://developers.google.com/sheets/api/samples/formatting

然而，我找不到任何从A1表示法(例如Sheet2!A3:B4(到GridRange的转换实用程序。

有两个困难，第一个是知道sheetId对应于命名的表，第二个是将范围编码为基于0的索引

Answer 1

回答我自己的问题。首先，您需要实时查找才能发现sheetId

在科特林：

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
}

然后，为了转换坐标，我使用了Apache POI的依赖项。一个更好的解决方案是从那里重新编码您需要的2个类，因为POI很大，并带来了其他您可能不想要的依赖项：

• https://poi.apache.org/apidocs/dev/index.html?org/apache/poi/ss/util/CellReference.html
• https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/CellRangeAddress.html

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
}
}

单元测试

@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
}

依赖项为：

• https://mvnrepository.com/artifact/com.google.apis/google-api-services-sheets
• https://mvnrepository.com/artifact/org.apache.poi/poi