GASでのスプレッドシートの列操作に欠かせないアルファベットをインデックスに変換する関数

2023年2月8日 GAS活用法

今日は、久しぶりにGoogle Apps Script（GAS）を書きました。というお話です。所属している学習コミュニティ「ノンプロ研」で、こんな質問がありました。

GASでCSVデータを読み込んで、CSVデータの特定の列(A,B,C,F,G,I列)をスプレッドシートに書き出したい！

GASでのcsvファイルの操作は、以下の記事で少し触れています。

Googleドライブのフォルダ内にある複数のCSVファイルを一括でスプレッドシートにインポートしたい

ということで、今回は特定の列（A,B,C,F,G,I列）のみを書き出したいという部分にフォーカスしてみました。

通常この場合だと、与えられたcsvデータが読みだした2次元配列から特定列に該当するインデックス（0スタートの数字）を指定して欲しい列のデータのみで新しい2次元配列を作ります。

function blogColumnIndexOf01() {
  const ary2D = [[], [], []]; // データの入った適当な2次元配列が与えられているとする
  let ary2DNew = [];
  for (const rowData of ary2D) {
    ary2DNew.push([
      rowData[0],
      rowData[1],
      rowData[2],
      rowData[5],
      rowData[6],
      rowData[7]
    ]); // 列A, B, C, F, G, Iを配列ary2DNewに追加
  }
}

ただ、アルファベットで与えられている列をインデックスで指定するというのが読みづらいし、メンテしづらいのが欠点です。

ということで、列のアルファベットを指定したら対応するインデックスを返してくれる関数を作りましたというのが本日の本題です。

Chat GPTに聞いてみた

作りました…と言いつつ、実は最近はやりのChat GPTに「Aで始まるアルファベットを0で始まる数値インデックスに変換するJavaScriptコードを出力して」みたいにしてイケてるコードを出力してもらいました。

質問を微調整しつつ、何回目かに出てきたのが

というもので、文字列に対して使用できるcharCodeAt(index)メソッドを使ったもの。これはさすがという感じ。

charCodeAt() メソッドは、指定された位置にある UTF-16 コードユニットを表す 0 から 65535 までの整数を返します。
https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/String/charCodeAt

というメソッドで、要は文字に予め振られた番号を返してくれるメソッドです。このメソッドを利用して、引数で与えられたある文字とアルファベットのAとの差分を出力してくれるスクリプトをChat GPTが教えてくれました。

A-Zまでのシンプルなアルファベットの各文字は連続した番号が振られているので、例えばFというアルファベットの番号（仮に6とする）からAの番号（仮に1とする）を引けば、該当のアルファベットの（Aを起点に0で始まる）数値インデックスがわかります（例の場合は5）。

2文字以上のアルファベットで表される列はどうするか

ただ、このスクリプトだけではZ以降の列に発生するAAとか、BGとか2文字以上のアルファベットで表される列には対応できません。

上記のアイデアをもとに2文字以上のアルファベットで表される列に対応させました。

function testColumnIndexOf() {
  console.log(columnIndexOf_('A')); // 0
  console.log(columnIndexOf_('Z'));  // 25
  console.log(columnIndexOf_('AA')); // 26
  console.log(columnIndexOf_('ZZ')); // 701
  console.log(columnIndexOf_('AAA')); // 702
}

/**
 * 引数に与えられたアルファベットから該当する列のインデックス（0スタート）を返す関数
 * @param   {string}  letter - スプレッドシートの列を表す任意のアルファベット
 * @return  {number}  indexColumn - 列のインデックス（0スタート） 
 */

function columnIndexOf_(letter) {
  const length = letter.length; // 文字列の長さ
  let numberColumn = Number(); // 列番号（1スタート）カウント用の変数
  for (let i = 0; i < length; i++) {
    const countRight = length - i; // 引数で与えられた文字列の右側から数えて何番目（1スタート）か
    const indexRight = countRight - 1; // 引数で与えられた文字列の右側から数えて何番目（0スタート）か 
    numberColumn += (letter.charCodeAt(i) - 'A'.charCodeAt(0) + 1) * (26 ** indexRight);
  }
  const indexColumn = numberColumn - 1; // 列のインデックス（0スタート）
  return indexColumn;
}

ポイントとなるのは、アルファベットは26文字ありその26文字を駆使して列を増やしていくので、26進数だと考えられる点です。

与えられた文字の右側から2番目のAは26文字のアルファベットが1周したと考えられます。

A = 1
Z = 26
AA = 26 * 1 + 1

同じように右側から3番目のAは、26文字のアルファベットが26周を1周したということになります。

ZZ = 26 * 26 + 26
AAA = 26 * 26 * 1 + 26 * 1 + 1

苦手の数学の世界に突入してしまいうまく説明できませんが、とにかく右側から一文字ごとにアルファベットを数字に変換していき、足し合わせれば答えが出そうです。

ABC = 26 * 26 * 1 + 26 * 2 + 3
ABCD = 26 * 26 * 26 * 1 + 26 * 26 * 2 + 26 * 3 + 4

この計算を（あまりスプレッドシートの実用シーンでは考えられませんが）文字列が長くなっても拡張してくために、まず文字列のアルファベットの数をカウントしています。

const length = letter.length;

続いて、列番号（1スタート）カウント用の変数を用意し

let numberColumn = Number();

文字列の一番右側のアルファベットの桁を0として、左側に順番に1,2,…と桁のインデックスを割り当てていきます。ここでは久しぶりにカウント変数を使うfor文を登場させました。

for (let i = 0; i < length; i++) {
  const countRight = length - i;
  const indexRight = countRight - 1;
  numberColumn += (letter.charCodeAt(i) - 'A'.charCodeAt(0) + 1) * (26 ** indexRight);
}

ポイントはcharCodeAt(index)メソッドの引数で、文字列”ABC”の一番左側（先頭）Aの文字コードを判定したいときは引数を0とします。次のBは引数が1、Cは2と文字列の位置を引数で指定しています。

文字列の左側の文字から

アルファベットAと位置の差分を取得する
右側から何桁目の文字列であるかを判定して桁数に応じて乗算（で数学的に言葉あっているかな？）する

ことである桁のアルファベットが表す数値を取得しています。

定数countRightは、右から何文字目（1スタート）のアルファベットかを求めており、定数indexRightはそれを右から何番目のインデックス（0スタート）かに変換しています。

これは定数・変数名の付け方のマイルールなのですが、numberやcountなどは1スタート、indexは0スタートと決めて、それぞれを区別できるようにしています。

for文内最後の

* (26 ** indexRight)

で、一番右側のアルファベットの桁は26の0乗、その1桁左側は26の1乗、また1桁左側は26の2乗…と計算してアルファベットが何列目を表しているのかを求めています。

ここのポイントは、26 ** 0 が 1 になるところですね。以下の記事を参考にしました。

0乗が1になることの3通りの説明

最後に変数numberColumnは1スタートの列番号なので、これを配列の要素を指定できるように0スタートのインデックスに変換して完成です。

おわりに

ということで、Google Apps Scriptでスプレッドシートのデータを配列で操作する時に、列の指定がアルファベットでできるようになりました。インデックスで指定するよりもリーダブルになってメンテナンスもしやすくなるかなと思います。

/* 特定の列（A,B,C,F,G,I列）のみを書き出したい */
function blogColumnIndexOf02() {
  const ary2D = [[], [], []]; // データの入った適当な2次元配列が与えられているとする
  let ary2DNew = [];
  for (const rowData of ary2D) {
    ary2DNew.push([
      rowData[columnIndexOf_('A')],
      rowData[columnIndexOf_('B')],
      rowData[columnIndexOf_('C')],
      rowData[columnIndexOf_('F')],
      rowData[columnIndexOf_('G')],
      rowData[columnIndexOf_('I')]
    ]); // 列A, B, C, F, G, Iを配列ary2DNewに追加
  }
}