๐Ÿ’พcolumnName()

GAS โŸฉ Classes โŸฉ Range โŸฉ A1 notation โŸฉ columnName()

่ฉฆ็ฎ—่กจ็š„ใ€Œๆฌ„ไฝ็ทจ่™Ÿใ€ๆŽก็”จไธ€็จฎๅพˆ็‰นๆฎŠ็š„ใ€Œๆ•ธๅญ—็ณป็ตฑใ€๏ผš

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

  • ๆฒ’ๆœ‰ใ€Œ0ใ€้€™ๅ€‹็ฌฆ่™Ÿ๏ผŒไนŸ็„กๆณ•่กจ็คบ 0 ้€™ๅ€‹ๆ•ธๅญ—

ไพ‹ๅฆ‚๏ผš26 ๅช่ƒฝ็”จ Z ่กจ็คบ๏ผŒไธ่ƒฝ็”จ A0 ่กจ็คบใ€‚

/**
 * 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"
PreviousA1 notationNextA1Notation()

Last updated

Was this helpful?