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

zip

photo credit: ElleFlorio A Bag of Colors – Redux via photopin (license)

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

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

前回の記事はコチラ。

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

配列やオブジェクトなどの便利機能を搭載するライブラリ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で「スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい」ということがよくあります。頻度が高いので、汎用的に使える関数を作っていきます。

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

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

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

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

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

Arrayオブジェクト.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メソッドを使って以下のように書きます。

_.zip.apply(_, Arrayオブジェクト)

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

これだけで指定した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でスプレッドシートにデータ行を挿入する2つの方法
  3. Google Apps Scriptでスプレッドシートの行を削除する2つの方法
  4. Google Apps Scriptで配列の検索をするindexOfメソッドとlastIndexOfメソッド

コメント

  1. あいす より:

    初めまして、あいすと申します。
    スクリプトを書くのはGASが初めてな初心者ですが、いつも当ブログを拝見させていただいていて大変為になっております。ありがとうございます。
    そんな中で以前よりタカハシ様がお書きになったコード内にありますval,colが指し示すものが何かが理解が出来ず、最近の列行に関してもうまく理解が進んでおりませんで
    もし差し支えなければval,colが指し示すものが何かご教示いただけませんでしょうか。

    ちなみになのですが現在自分は列情報からの行の特定についは
    getValuesで列の情報を取得してから教えていただいたindexOfを用いて特定の名前があった場合には0、無い場合には-1が出てそれをifで条件分岐をしてどの行に存在したかを判定しております。
    どうやら普通はもっと別のやり方をするのですね。

    • あいすさん

      コメントありがとうございます!

      もし差し支えなければval,colが指し示すものが何かご教示いただけませんでしょうか。

      valは検索値、colは検索対象とする列番号ですね。
      記事内でもリンク貼っていますが、以下記事で詳細お伝えしています。

      http://tonari-it.com/gas-spreadsheet-find/

      列情報からの行の特定についは
      getValuesで列の情報を取得してから教えていただいたindexOfを用いて特定の名前があった場合には0、無い場合には-1が出てそれをifで条件分岐をしてどの行に存在したかを判定しております。
      どうやら普通はもっと別のやり方をするのですね。

      むしろそのほうがシンプルに書けるときも多いと思いますので、もちろん、その方法でも良いと思います!

      • あいす より:

        御早いご返信ありがとう御座います。
        情けない話ですがここまで教えて頂いたにもかかわらず
        リンク先も含め直ぐ見て理解出来たとまでは行きませんでしたが
        これからも当ブログを拝見させていただき徐々に理解を深めて行きたいと思います。
        それから自分のやり方でもまあ大丈夫だったのですね。
        回りに教えていただける者が居らず自信が有りませんでしたが少し勇気をいただけました。重ねて御礼申し上げます。

タイトルとURLをコピーしました