🔰A1 notation

GAS ⟩ Classes ⟩ Range ⟩ A1 notation

⭐️ 重點

試算表的「欄位編號」採用一種很特殊的「數字系統」：

• A = 1, B = 2, ... , Z = 26。

• 沒有「0」這個符號，也無法表示 0 這個數字。

例如：26 只能用 Z 表示，不能用 A0 表示。

ABC = 1*26^2 + 2*26 + 3    // 轉換為 10 進位
Z + A = AA                 // = 26 + 1 = AA
ZZ + A = AAA               // = 26² + 26 + 1 = AAA

💈範例

Sheet1!A1:B2    # first two cells in top two rows of `Sheet1`
Sheet1!A:A      # first column of `Sheet1`
Sheet1!1:2      # first two rows of `Sheet1`
Sheet1!A5:A     # first column (from row 5 onward)
A1:B2           # first two cells in top two rows
Sheet1          # all the cells in `Sheet1`
'My Custom Sheet'!A:A     # first column of sheet named "My Custom Sheet" 
'My Custom Sheet'         # all the cells in "My Custom Sheet"

💡Tip:

• where possible, use distinct names for the objects within your spreadsheets. For example, A1 refers to the cell A1 in the first visible sheet, whereas 'A1' refers to all the cells in a sheet named A1. Similarly, Sheet1 refers to all the cells in Sheet1. However, if there’s a named range titled "Sheet1", then Sheet1 refers to the named range and 'Sheet1' refers to the sheet.

• single quotes are required for sheet names with spaces, special characters, or an alphanumeric combination.

🛠 工具

• columnName() - column index to column name.

• A1Notation() - (row, col) to A1 Notation.

• A1NotationToRowColumn() - A1 notation to { row, col }.

📘 手冊

• Google Sheets API Overview ⟩ Cell ⟩ A1 notation

• Cells - internal data structure of a cell

• Range ⟩

  • getA1Notation() - 回傳類似 "A1:E2" 的字串。

  • Sheet ⟩ getRangeList(a1Notations)

相關試算表指令：

• =ADDRESS() - row/col number to A1/R1C1 notation.

• =ROW() - row number of a cell.

• =COLUMN() - column number of a cell.

📗 參考

• How to Convert Column Number (e.g. 28) to Column Letter (e.g. AB) in Google Sheets ⭐️ - row/column number ⇄ A1 notation