💾columnName()

GAS ⟩ Classes ⟩ Range ⟩ A1 notation ⟩ columnName()

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

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

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

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

💾 程式

• OneCompiler, replit

/**
 * 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;
}

💈範例：

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

⬇️ 應用

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

📘 手冊

• 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