Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

Google Apps Scriptでスプレッドシートのデータを配列に格納して操作するテクニックについてシリーズでお伝えしています。

前回の記事はコチラ。

Google Apps Scriptで100以上の便利関数が使える定番ライブラリUnderscore for GAS

Underscore for Google Apps Scriptは100を超える様々な便利関数が多数パッケージ化されている定番ライブラリです。今回はその特徴、導入方法、使い方などをお伝えします。

tonari-it.com

2017.03.29

配列やオブジェクトなどの便利機能を搭載するライブラリUnderscore for GASの紹介と導入の仕方についてお伝えしました。

さて、なんでわざわざUnderscore for GASを導入したかというと、今回使うzipメソッドが使いたい！というのが最大の理由です。

スプレッドシートのデータは二次元配列として操作することができますが、zip.applyメソッドを使うとその行と列の入れ替えが簡単にできます。

行列転置？…ややこしそうだな～…なんて思わずに、騙されたと思ってみてみて下さい。それはもう配列操作の世界が劇的に広がります。

ということで、今回はGoogle Apps ScriptでUnderscoreのzip.applyメソッドで二次元配列の行と列を入れ替える方法とその意義についてお伝えします。

では、行ってみましょう！

GASでVLOOKUP関数的な検索をしたい

GASでスプレッドシートの「VLOOKUP関数的な検索」をしたいことって結構あります。

つまり、指定した列の中から値を検索して、マッチしたレコードの行番号（または別の列の値）を引っ張ってくるという操作です。

例えば、以下のようなシート「成績表」があったとします。

このシートについて、B列の名前が「Lucas」ってやつの行番号って何番だっけ？

それなら、5行目だよ。

という処理をしたいということです。

スプレッドシートAPIを使って検索する

これをGASでやろうとすると、以下のようなスクリプトを作成することになります。

//sheet:対象シート
//val:検索値
//col:検索対象とする列番号
function findRow(sheet,val,col){

  var lastRow=sheet.getDataRange().getLastRow(); //対象となるシートの最終行を取得

  for(var i=1;i<=lastRow;i++){
    if(sheet.getRange(i,col).getValue() === val){
      return i;
    }
  }
  return 0;
}

Sheetオブジェクト、検索値、検索対象の列番号を渡すと、最初にマッチした行の行番号を返すというものです。

for文の中にスプレッドシートのAPIが含まれてしまっているので、処理速度的に痛々しいですね。

シートのデータを二次元配列に格納して検索する

速度を上げるために、配列でやることもできますね。以下のようなスクリプトになります。

//sheet:対象シート
//val:検索値
//col:検索対象とする列番号
function findRow(sheet,val,col){

  var dat = sheet.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得

  for(var i=1;i<dat.length;i++){
    if(dat[i][col-1] === val){
      return i+1;
    }
  }
  return 0;
}

APIを使わなくなるので、処理速度は速くなります。

ここまでの話、以下の記事で詳しく紹介していますので、ご興味があればご覧ください。

Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）

Google Apps Scriptで「スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい」ということがよくあります。頻度が高いので、汎用的に使える関数を作っていきます。

tonari-it.com

2016.10.03

配列で検索ならindexOf関数を使えばよい？

ですが、こんな処理、いっぱい使いますもんね。わざわざ関数を作らずとも、もっと簡潔にやりたいじゃないですか。

そこで、パッと思いつくのが、前々回の記事でお伝えしたindexOfメソッドです。

Google Apps Scriptで配列の検索をするindexOfメソッドとlastIndexOfメソッド

Google Apps Scriptでスプレッドシートのデータを配列に格納して操作をする方法をお伝えしています。今回は配列を検索するindexOfメソッド、lastIndexOfメソッドの使い方です。

tonari-it.com

2017.03.25

indexOfメソッドでは、以下のようにすることで、配列の先頭の要素から値を検索し、見つかったらそのインデックス番号を返します。

ただ、よく考えてみて下さい。

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('成績表');
var arrData = mySheet.getDataRange().getValues();

これで取得した二次元配列arrDataは

[
     [ID, 名前, 国語, 数学,英語],
     [1, Noah, 52, 43, 95, ],
     …,
     [10, Georgia, 37, 68, 41 ]
]

という構成になります。

ですから、indexOfメソッドを使おうと思っても

Logger.log(arrData[0].indexOf('国語')); //2・・・見出し行から「国語」を検索
Logger.log(arrData[4].indexOf(72)); //3・・・Lucasの成績から72を検索

こんな行方向の検索しかできないのです。

いらね～って感じです。

つまり、そのままの二次元配列でindexOfメソッドを使った列方向の検索はできない、ということです。

二次元配列を行列転置する意義

そこで登場するのが行と列を入れ替える、つまり行列転置です。二次元配列arrTransが

[
     [ID, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
     [名前, Noah, Ethan, Logan, Lucas, Hildegarde, Shane, Siever, Gauvain, Electra, Georgia],
     …,
     [英語, 95, 95, 66, 34, 64, 42, 93, 31, 77, 41]
]

となってくれているのであれば、名前がごそっと格納されている配列arrTrans[1]に対してindexOfメソッドを使うことで

Logger.log(arrTrans[1].indexOf('Lucas')); //4・・・Lucasのインデックス番号

と、名前をキーにしてインデックス番号を求めることができます。このインデックス番号は、行番号-1（配列のインデックスは0から始まるのでマイナス1）ですよね。

このように、シートデータを格納した二次元配列は列方向で操作したいときもたくさんあるのです。そして、そんな時に行と列を入れ替えることができるなら、配列操作の幅がグッと広がります。

zip.applyメソッドで行と列を入れ替える

そこで、いよいよ登場するのがUnderscore for GASのzipメソッドです。

配列の行列を入れ替えるをするには、zip.applyメソッドを使って以下のように書きます。

ちょっと？な書き方ですが、このまんまバシっと覚えちゃってください。

これだけで指定したArrayオブジェクトを行列転置した配列が取得できます。

例えば以下のようなスクリプトを実行してみましょう。

function searchSheet(){

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('成績表');
  var arrData = mySheet.getDataRange().getValues();

  var _ = Underscore.load();
  var arrTrans = _.zip.apply(_, arrData);
  Logger.log(arrTrans[1].indexOf('Lucas')); //4

}

実行すると、ちゃんとLucasの行番号-1、つまり「4」とログ出力されるはずです。

Underscoreのロードや、行列転置などの準備が要りますが、それを含めてもたったの三行。実際に行方向の検索についてはindexOfメソッドによるたった一行で実現できちゃいます。

まとめ

Google Apps Scriptで二次元配列の行と列を入れ替える方法をお伝えしました。

行と列を入れ替えるには、zip.applyメソッドを使いましたが、このメソッドのためだけに、Underscore for GASライブラリを使っても良いくらいの、意義と効果があると思っています。

次回以降、行と列を入れ替えるとUnderscore for GASのいくつかのメソッドが劇的に使えるようになりますので、順次お伝えしていきたいと思います。

どうぞお楽しみに！

連載目次：GASで6分の壁と戦うための配列を扱うテクニック

Google Apps Scriptでは配列操作が非常に重要です。というのも、SpreadsheetサービスのAPIを使えば使うほど、「Google Apps Scriptの6分の壁」にぶつかるからです。このシリーズでは、GASでスプレッドシートのデータを配列に格納して処理をする様々なテクニックをお伝えします。

1. Google Apps Scriptで配列を使ってスプレッドシートにデータ行を追加する方法
2. Google Apps Scriptでスプレッドシートにデータ行を挿入する２つの方法
3. Google Apps Scriptでスプレッドシートの行を削除する２つの方法
4. Google Apps Scriptで配列の検索をするindexOfメソッドとlastIndexOfメソッド