スプレッドシート列追加で配列indexがズレるのを防ぐ:GAS/Python

タイトルだけでは意図を説明しきれているか微妙なので、まずはどういう状況を想定しているか、から。

GASやPythonのgspreadで、例えば以下のようにスプレッドシートを操作・管理していたとして、

ソースコード内で部署列(index=2)を扱っている箇所があるとする。

Google Apps Scriptfunction processBusho() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('xxx');
  const rows = sheet.getDataRange().getValues();

  for (let i = 1; i < rows.length; i++){
    // 社員の部署が表示される
    Logger.log(`部署: ${rows[i][2]}`);
    // 部署データで何かする
    doSomething(rows[i][2]);
  }
}

ところが、以下のように「どうしても(最後の列ではなく)列と列の間に"性別"を追加しないといけない...」という場合。

「部署」列が3列目(index=2)から4列目(index=3)に移動するので、ソースコード上でindexの書き換えをしないといけません。

Google Apps Scriptfunction processBusho() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('xxx');
  const rows = sheet.getDataRange().getValues();

  for (let i = 1; i < rows.length; i++){
    // ↓2 を 3 に要修正...
    Logger.log(`部署: ${rows[i][2]}`);
    // ↓2 を 3 に要修正...
    doSomething(rows[i][2])
  }
}

これは非常に簡単な例なので1ヶ所しかない2を3に書き換えるだけですが、

• 10ヶ所・20ヶ所あるとなると修正箇所を見逃しバグる可能性
• 列数が多い場合、右にズレた列数分だけ修正箇所が増える

などなど、めちゃくちゃ負担が大きい作業になってしまいます。

また、以下のようにスプレッドシートのレンジ取得のお作法である指定(1行目/1列目は0ではなく1)の場合や、

sheet.getRange(2, 3).getValue()

以下のようにアルファベットで列指定している場合でも、

sheet.getRange("C:C").getValues()

列を追加するとズレてしまいますよね。

これらが入り乱れていたりすると、修正箇所が膨大になりやる気をなくしてきます...。

・・・

という本記事の想定する状況を書いたところで、列を追加しても修正箇所をごくわずかにとどめる方法を書いていきます。

シートに列追加で配列参照ズレを防ぐには、オブジェクトで列管理

(Pythonなら辞書ですが)このように列名と列indexをオブジェクトで定義しておくことで、

columnIndex = {
  "ID": 0,
  "名前": 1,
  "部署": 2,
}

1. スプレッドシートに列を追加する
2. 列に合わせてオブジェクトを修正する

というひと手間の修正のみで済むようになります。

以下例ではGASを想定して書いていきます。

配列インデックス指定の場合

以下のように(GASの場合)オブジェクトで列名とindexを紐づけて定義しておき、

部署列を参照するindexに[columnIndex.部署]のように書いておく。

Google Apps ScriptcolumnIndex = {
  "ID": 0,
  "名前": 1,
  "部署": 2,
}
// 途中省略
for (let i = 1; i < rows.length; i++){
  Logger.log(`部署: ${rows[i][columnIndex.部署]}`);
  doSomething(rows[i][columnIndex.部署]);
}

▼そうすれば、新たな列が部署列の左側に追加されてもオブジェクトを修正するだけでOK。実処理に含まれるindexの修正は不要。

Google Apps ScriptcolumnIndex = {
  "ID": 0,
  "名前": 1,
  "性別": 2, // 追加
  "部署": 3,
}
// 途中省略
for (let i = 1; i < rows.length; i++){
  // オブジェクトを修正するだけで"部署"を参照し続けてくれる
  Logger.log(`部署: ${rows[i][columnIndex.部署]}`);
  doSomething(rows[i][columnIndex.部署]);
}

また、このようにオブジェクトで管理することで、

• rows[i][2]
  → 2列目って何のデータだっけ？
• rows[i][columnIndex.部署]
  → あ、ここは部署列ね

というふうに、後になってパッと見で何のデータ分かりやすい効果もあります。

sheet.getRange(2, 3)〜のような記法の場合

getRange(行数, 列数)のようにindexではなく1行目・2行目のように指定する方法の場合。

列名と1から始まる列数をcolumnNumberで紐づけて定義しておき、columnNumber.部署と書いて参照する。

Google Apps Scriptconst columnIndex = {
  "ID": 0,
  "名前": 1,
  "性別": 2, // 追加
  "部署": 3,
}
const columnNumber = {
  "ID": 1,
  "名前": 2,
  "性別": 3, // 追加
  "部署": 4,
}
// 途中省略
// ↓ オブジェクトを修正するだけで"部署"列を参照し続けてくれる
sheet.getRange(2, columnNumber.部署).getValue()

アルファベットで列指定する場合

sheet.getRange("C:C").getValues()

このようにアルファベットで列指定している場合も、C列より左間に列を追加するとズレてしまいます。

ズレ回避のため、列名とアルファベットをcolumnSymbolで紐づけて定義しておいて、("D:D")としていたところを(`${columnSymbol.部署}:${columnSymbol.部署}`)とする。

Google Apps Scriptconst columnIndex = {
  "ID": 0,
  "名前": 1,
  "性別": 2, // 追加
  "部署": 3,
}
const columnNumber = {
  "ID": 1,
  "名前": 2,
  "性別": 3, // 追加
  "部署": 4,
}
const columnSymbol = {
  "ID": "A",
  "名前": "B",
  "性別": "C", // 追加
  "部署": "D",
}
// 途中省略
// ↓ オブジェクトを修正するだけで"D:D"を参照し続けてくれる
sheet.getRange(`${columnSymbol.部署}:${columnSymbol.部署}`).getvalues()