# columnName()

[GAS](https://lochiwei.gitbook.io/web/appendix/gas) ⟩ [Classes](https://lochiwei.gitbook.io/web/appendix/gas/classes) ⟩ [Range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range) ⟩ [A1 notation](https://lochiwei.gitbook.io/web/appendix/gas/classes/range/a1-notation) ⟩ columnName()

{% hint style="info" %}
試算表的「欄位編號」採用一種很特殊的「數字系統」：

* A = 1, B = 2, ... , Z = 26
* 沒有「0」這個符號，也無法表示 0 這個數字

例如：26 只能用 Z 表示，不能用 A0 表示。
{% endhint %}

{% tabs %}
{% tab title="💾 程式" %}

* [OneCompiler](https://onecompiler.com/javascript/3y9jn85fj), [replit](https://replit.com/@pegasusroe/columnNamecol#index.js)

```javascript
/**
 * convert column index to column name (A1 Notation).
 *
 * @param {number} col - column number of cell. (A = 1)
 * @returns {string} 
 *
 * @example
 *
 *   columnName(4)           // "D"
 *   columnName(27)          // "AA"
 *   columnName(26*26 + 26)  // "ZZ"
 *
 */
function columnName(col) {

    // integer division
    function intDiv(a, b){
        return [ Math.floor(a / b), a % b ];    // quotient, remaider
    }
    
    // 1 -> A, 2 -> B, ... 26 -> Z
    function alphabet(i){
        if ( i === 0 ) i = 26;    // ⭐️ 0 -> Z
        return String.fromCharCode(i - 1 + 'A'.charCodeAt()) 
    }

    const Z = 26;                     

    let name = ``;

    // example 1: n = ABC = A*Z² + B*Z + C
    //   n % Z = C 
    //   n / Z = A*Z + B = AB
    //
    // example 2: n = ZZ = Z*Z + Z
    //   n % Z = 0 (⭐️ which means the last digit is 'Z')
    //   n / Z = Z + 1 (⭐️ this extra 1 must be removed)
    let n = col;

    while (n >= 1) {
        const [q, r] = intDiv(n, Z);
        name = alphabet(r) + name;
        n = q - (r === 0 ? 1 : 0);    // ⭐️ 
    }

    return name;
}
```

💈範例：

```javascript
columnName(4),            // "D"
columnName(27),           // "AA"
columnName(26*26 + 26),   // "ZZ"
columnName(26**2 + 2*26 + 3),    // "ABC"
```

{% endtab %}

{% tab title="⬇️ 應用" %}

* [a1notation](https://lochiwei.gitbook.io/web/appendix/gas/classes/range/a1-notation/a1notation "mention") - <mark style="color:blue;">`(row, col)`</mark> to [](https://lochiwei.gitbook.io/web/appendix/gas/classes/range/a1-notation "mention").
  {% endtab %}

{% tab title="📘 手冊" %}

* [Google Sheets API Overview](https://developers.google.com/sheets/api/guides/concepts) ⟩ Cell ⟩ A1 notation
* [Cells](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells) - internal data structure of a cell
* [Range](https://developers.google.com/apps-script/reference/spreadsheet/range) ⟩
  * [getA1Notation()](https://developers.google.com/apps-script/reference/spreadsheet/range#geta1notation) - 回傳類似 <mark style="color:yellow;">`"A1:E2"`</mark> 的字串。
  * Sheet ⟩ [getRangeList(a1Notations)](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangelista1notations)

相關試算表指令：

* [=ADDRESS()](https://support.google.com/docs/answer/3093308?hl=en) - <mark style="color:yellow;">**row/col number**</mark> to <mark style="color:orange;">**A1/R1C1**</mark> notation.
* [=ROW()](https://support.google.com/docs/answer/3093316?hl=en\&ref_topic=3105472) - <mark style="color:yellow;">**row number**</mark> of a cell.
* [=COLUMN()](https://support.google.com/docs/answer/3093373?hl=en\&ref_topic=3105472) - <mark style="color:yellow;">**column number**</mark> of a cell.
  {% endtab %}

{% tab title="📗 參考" %}

* [x] [How to Convert Column Number (e.g. 28) to Column Letter (e.g. AB) in Google Sheets](https://www.labnol.org/convert-column-a1-notation-210601/) ⭐️ - row/column number ⇄ A1 notation
  {% endtab %}
  {% endtabs %}