GAS|スプレッドシートの列番号とアルファベット列名称を変換する。

GASで列を指定する際は、基本的には列番号のほうが便利です。足し算引き算もできますし。

でも、シートのある列の全体を取得したいときには、列名称を使って次のように書くしかなかったりと、ちょこちょこ、列番号と列名称を変換したいタイミングがあります。

let clmB = sheet.getRange("B:B").getValues();

列番号 → 列名称

/**
 * シートの列番号をアルファベットの列名称に変換する。
 * 例: 5 => "E", 100 => "CV"
 * @param {number} clmNum 列番号
 * @return {string} アルファベットの列名称
 */
function getClmName(clmNum) {
  //数字ではなかった場合はエラー
  if(isNaN(clmNum)) throw new Error(`列番号が数字ではありません。 clmNum: ${clmNum}`)
  //アルファベット文字数26文字なので、列番号を26進数の扱いに直す。
  let syo = Math.floor(clmNum / 26); //26で割った商
  let amari = clmNum % 26; //26で割った余り
  //余りが0の場合は、微調整する。
  if (amari === 0) {
    syo--;
    amari = 26;
  }
  //列名称2桁目(以上)のアルファベットを特定する。
  let parent = syo > 26
    ? getClmName(syo) //商の値が26を超える場合は、ループ処理する。
    : syo > 0
      ? String.fromCharCode('A'.charCodeAt(0) + syo - 1) //商の値が0より大きく26以下の場合は、対応するアルファベット1文字に変換する。
      : ''; //商が0の場合は空欄とする。
  //列名称1桁目のアルファベットを特定する。
  let child = String.fromCharCode('A'.charCodeAt(0) + amari - 1); //余りの値に対応するアルファベット1文字に変換する。
  return parent + child;
}

名称 → 列番号

/**
 * シートのアルファベットの列名称を列番号に変換する。
 * 例: "E" => 5, "CV" => 100
 * @param {string} clmName アルファベットの列名称
 * @return {number} 列番号
 */
function getClmNum(clmName) {
  let clmNum = 0;
  //列名称を文字ごとに分割した配列を作成し、順番を逆転させた上で、末尾の文字より該当する数字に変換する。
  String(clmName).split("").reverse().forEach((val, idx, arr) => {
    //半角アルファベットでない場合はエラー
    if(/[^A-Za-z]/.test(val)) throw new Error(`半角アルファベット以外の文字が含まれています。 clmName: ${clmName}`)
    //大文字化した上で、「A」の文字コードとの差を数字で取得する。
    let num = val.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0) + 1;
    //列名称末尾からの位置に応じて、26のべき乗を準備し、それに先ほどの数字をかける。その値を列番号に加算する。
    clmNum += (26 ** idx) * num;
  })
  return clmNum;
}

あまりおススメではない方法

たまに次のようなコードを見かけますが、本来不要であるsheetクラスのメソッドを用いており、あまりおススメではありません。

function getColName(num) {
  //アクティブシートシート取得
  var sheet = SpreadsheetApp.getActiveSheet();
  //対象列の1行目セルを取得
  var result = sheet.getRange(1, num);
  //そのセルセルのA1表記表記の文字列を文字列を取得。「B1」みたいな。
  result = result.getA1Notation();
  //数字部分を削除
  result = result.replace(/\d/,'');
  return result;
}

列名称は完全に規則的に付けられているものなので、わざわざ無関係なsheetのメソッドを呼び出さなくても変換できます。スタンドアローンのGASプロジェクトだと、そもそもアクティブシートも取得できなかったりします。

できるだけシンプルに行きましょう!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です