スプレッドシートの列番号と A, B, C を変換する

※本記事はプロモーションを含みます

目次

Google Apps Script（GAS）でスプレッドシートの集計やデータ整形の自動化を組んでいると、スクリプトの内部では列の位置を 1 や 28 といった数値のインデックスで処理しているのに、最終的にセルへ書き込む数式や範囲指定の都合でどうしても「A」や「AB」といった列アルファベットが必要になる場面によく突き当たります。二次元配列のループ処理では添字やオフセットを使った数値計算が最も扱いやすい反面、シート側が求めるA1記法や関数文字列は記号列を前提としているため、この二つの表現の橋渡しがスムーズにいかないと無駄な分岐や手計算のコードが増えてしまいがちです。ここでは、GASの標準メソッドである getA1Notation を活用して安全かつ手短に数値から列記号を取得する方法と、その処理を実務の大量処理に組み込む際に知っておくべき速度面や設計上の注意点をまとめました。

数値の列番号のままではA1記法の文字列が作れずに立ち止まる場面

スプレッドシートのセル操作をスクリプトから行う際、大半の更新処理は sheet.getRange(row, column, numRows, numColumns) のように行番号と列番号をそれぞれ 1 始まりの整数で指定する API を使えば問題なく完結します。たとえば「5行目の3列目」であれば sheet.getRange(5, 3) と書けば確実に目的のセルを捉えることができます。なので二次元配列から一括で値を流し込む場合でも行数や列数はすべて数値で計算できるため、わざわざ列の英字記号を意識する必要は本来ありません。GAS 列番号 列アルファベット 変換 というキーワードで方法を探すことになるのは、シートをただ読み書きする段階を超えて、別の機能と連携させようとした瞬間です。

変換が必要になる典型的なケースは、セルの中に SUM 関数や INDIRECT 関数、VLOOKUP 関数などの数式を setFormula や setFormulaR1C1 を使って動的に埋め込みたい場合です。R1C1 形式で完結できるケースもあります。ただし既存の業務ルールや引き継ぎの都合上、どうしても生成される数式を「=SUM(C2:C50)」のように人間が見て直感的にわかりやすい A1 記法の文字列としてセルに残さなければならない案件は現場に多く存在します。というのは、getRangeList を使って飛び飛びの列範囲をまとめて操作したいときや、名前付き範囲（NamedRange）をスクリプトから自動生成・再定義したいときにも、「C:C」や「AA2:AC10」といったアルファベット混じりの範囲文字列が要求されるからです。

この変換を行おうとする前、多くの人が直面するのは「列が 26 まではアルファベットの配列を用意してインデックス参照すれば動くが、27 列目を超えて AA や AB が出てきた途端にインデックスの計算が破綻する」という問題です。自力でアルファベットの配列を作って剰余計算（% 26）や商の計算を組み合わせようとすると、A を起点とした 1 始まりの数体系と、0 を起点とするプログラミング言語の配列添字、さらには 26 の倍数になった瞬間に桁上がりがずれる現象が複雑に絡み合います。なのでテストケースを作っても境界値で予期しない文字が出力されるバグを抱え込みやすくなります。

この問題を解消したあとは、スクリプト内部では何列目であっても純粋な数値計算（たとえば「現在処理中の列番号 + オフセット値」）として扱います。なので外部へ出力する直前のワンステップだけで列記号の文字列へと安全に変換できるようになります。列番号が 1 であっても 27 であっても 702 であっても、同じ変換関数を一行通すだけで適切なアルファベット記号が得られる状態を作っておくことが、堅牢なシート自動化の第一歩となります。

標準メソッドのgetA1Notationを介して一発でアルファベットを取り出す

数値から列記号を求めるための最もシンプルで間違いのないアプローチは、スプレッドシート自身が持っているセルアドレス生成処理、すなわち getA1Notation() メソッドをそのまま利用することです。GAS の Range オブジェクトは、自身がシート上のどこに位置しているかを「A1」や「BC1」といった文字列表現で返す仕組みをすでに備えています。というのは、調べたい列番号の 1 行目にあるセルを仮定して Range を取得し、その文字列表現から行番号である「1」を取り除くだけで、目的のアルファベット記号を正確に抽出できるからです。

function getColumnLetter(colIndex) {
  const sheet = SpreadsheetApp.getActiveSheet();
  return sheet.getRange(1, colIndex).getA1Notation().replace(/[0-9]/g, "");
}

このコードは、指定された列番号 colIndex に対し、シートの 1 行目のセル（sheet.getRange(1, colIndex)）を一度参照しています。たとえば colIndex に 1 を渡せばセル範囲「A1」が、27 を渡せば「AA1」が Range オブジェクトとして得られます。そのオブジェクトに対して getA1Notation() を呼び出すとそれぞれ文字列の “A1” や “AA1” が返ってきます。なので正規表現 replace(/[0-9]/g, “”) を使って末尾の行番号である数字部分を空文字に置換して消去し、残った “A” や “AA” を関数の戻り値として返却しています。

この実装を使う最大のメリットは、26 進数に似たアルファベットの繰り上がりルール（Z の次が AA になり、ZZ の次が AAA になるという特殊な加算ルール）を自前のスクリプトで愚直に計算しなくて済む点にあります。一般的な 10 進数や 16 進数の基数変換であれば 0 から始まるため単純な除算と剰余で記述できます。ただしスプレッドシートの列名は「0 に対応する文字が存在しない」「26 で割り切れる数値のときに商の繰り下げが必要になる」という数学的な癖を持っています。自前で書いたロジックは Z や AZ、BZ といった特定の境界列で想定外の文字を吐き出す事故を起こしがちです。ただし getA1Notation() を使う方法であれば Google スプレッドシートの仕様そのものに準拠しているため、列記号が狂う余地がありません。

ただし、このコードをそのまま組み込む際に注意しなければならないのは「引数として渡す列番号は必ず 1 以上の整数でなければならない」という点です。JavaScript の配列処理に慣れていると、つい配列の添字である 0 をそのまま渡してしまいがちです。なので sheet.getRange(1, 0) を実行するとスプレッドシートの API は例外エラーを投げてスクリプト全体の実行を止めてしまいます。ループのカウンター変数 i をそのまま渡す場合は、必ず i + 1 に補正して関数を呼び出すように徹底しましょう。

ループ処理内でgetA1Notationを連打したときの処理速度と回避策

標準の getA1Notation を使った変換は極めて簡潔で安全です。ただし実務で巨大な表データを扱う際には実行速度の壁にぶつかることがあります。SpreadsheetApp を経由して sheet.getRange() を呼び出す処理は、Google のサーバー側と通信を伴う重い操作です。単発で「集計列の記号を 1 つだけ取得したい」という程度であれば一瞬で終わるため何も問題ありません。ただし、たとえば数百列に及ぶマトリクス表を 1 列ずつ走査しながら、列ごとにこの関数を何度も呼び出してしまった場合は、スクリプトの実行時間が目に見えて膨らんでいきます。

GAS には「1 回の実行時間は最長 6 分間」という厳格な実行時間制限が設けられています。セルへのアクセス回数が増えれば増えるほど処理は遅くなります。なので通信オーバーヘッドだけで数十秒を浪費してしまうことも珍しくありません。もし列変換を何千回、何万回と繰り返すループの中で実行しなければならない状況であれば、毎回シートの Range を叩きにいく設計は見直しましょう。

この速度低下を回避するための現実的な手段は二つあります。一つは「一度変換した結果をスクリプト内のメモリ（オブジェクトや Map）に一時保存しておくキャッシュの手法」です。処理の開始時に一度だけ getColumnLetter(colIndex) を呼び出します。なので得られた結果をキャッシュ用の変数に保存しておけば、2 回目以降に同じ列番号が登場した際はシート API を介さずにメモリ上の値を即座に再利用できます。同一の列番号を何度も参照するような帳票出力処理では、これだけでも実行時間を劇的に削ることができます。

もう一つのアプローチは、API を一切呼ばずに JavaScript の算術演算だけで完結する計算ロジックを用意しておくことです。以下のように、純粋な数値計算として商と余りを処理する補助関数を作っておきます。というのは、スプレッドシートのサーバーとの通信が完全にゼロになり、マイクロ秒単位で結果が得られるからです。

function getColumnLetterPure(colIndex) {
  let letter = "";
  let temp = colIndex;
  while (temp > 0) {
    let mod = (temp - 1) % 26;
    letter = String.fromCharCode(65 + mod) + letter;
    temp = Math.floor((temp - mod) / 26);
  }
  return letter;
}

この純粋計算の関数では、String.fromCharCode(65 + mod) を使って 0〜25 の数値を ASCII コードの ‘A’（文字コード 65）から ‘Z’ までの文字に直接変換しています。計算の過程で temp – 1 としているのがポイントです。というのは、これによって 1 始まりの列番号を一時的に 0 始まりに補正し、26 で割り切れる境界でも正しい桁上がりが起こるように調整しているからです。日常的なスクリプトでは前述の getA1Notation を使った短いコードで十分に事足ります。ただし「列数が非常に多くて処理時間を 1 秒でも削りたい」「スタンドアロンのスクリプトでシートオブジェクトを即座に開けない」といったシビアな状況では、こちらの算術アプローチへ切り替えるという選択肢を持っておくと安心です。

アルファベットから列番号への逆変換が必要になったときの処理設計

業務改善の現場では、列番号からアルファベットを求めるだけでなく、そのまったく逆の操作、つまり「”C” という文字から列番号の 3 を割り出したい」「”AB” という文字列を 28 という数値に変換して二次元配列のインデックス計算に使いたい」という要求もしばしば発生します。ユーザーが設定シートのセルに「参照列：AD」のように文字で設定値を入力してくる運用があります。なので外部システムから出力された CSV の列ヘッダー仕様が「列 C から列 F までを取り込む」と記号ベースで規定されている場合などがこれに該当します。

アルファベットから数値への逆変換を行う場合も、順変換と同様にスプレッドシートの Range オブジェクトを使う方法が存在します。sheet.getRange(letter + “1”).getColumn() を呼び出せば、指定したアルファベット文字列に 1 行目を結合してセル範囲を作成します。なのでその Range が持つ getColumn() メソッドによって正確な列番号を整数として取得できます。こちらも Google の標準機能に解釈を任せます。ただし小文字で “ab” と渡されても自動的に “AB” と解釈してくれたり、極めて安全に処理を完了できる利点があります。

しかし、逆変換においてもシート API を叩くコストは同じように発生します。また、ユーザーが設定セルに誤って「12」や「AAA100」のような無効な列記号を入力していた場合、getRange に不正な文字列が渡されて実行時エラーを引き起こすリスクも考慮しなければなりません。文字から数値への変換を自前で実装する場合は、文字列を左から 1 文字ずつ走査しながら 26 倍していく多項式計算を適用するのが一般的です。

function getColumnIndexFromLetter(letter) {
  const cleanLetter = letter.trim().toUpperCase();
  let colIndex = 0;
  for (let i = 0; i < cleanLetter.length; i++) {
    colIndex = colIndex * 26 + (cleanLetter.charCodeAt(i) - 64);
  }
  return colIndex;
}