GoogleAppsScript
GoogleSpreadSheet

Google Apps ScriptでHLOOKUP的なことをする

Google Spreadsheetを用いて週次で列を並び替えないといけない作業があったので、どうにか自動化したくて頭をひねりました。
AtoZ, ZtoAなどの並び替えであればエクセル初心者の中高生が手作業で行ってもすぐにできますし、簡単なスクリプトもあります。

が、やりたいことは以上の方法では叶わなかったので、色々試しました。

まずテストデータ

日付 交通費 食費
1/1 300 1000
1/2 500 3500
1/3 1000 2000

MECEガン無視でごめんなさい。

これらを、

日付 食費 交通費
1/1 1000 300
1/2 3500 500
1/3 2000 1000

このように列ごとに並べ替えようとしています。

HLOOKUP

エクセルの授業などで習う難しめの関数はVLOOKUPだけだった気がしますが、verticalがあるならhorizonもあります。ということでHLOOKUPを試しました。
主旨に関係ないため詳細は省きますが、上手く行きませんでした。

納得のいかなかった(HLOOKUPに不満を持った)点は以下の2点です。

  • 第2引数で複数行*複数列の二次元範囲を選択できない
    ヘルプを見ると選択できそうなのですがね…。謎です。
    スクリーンショット 2018-03-20 3.41.37.png

  • 第3引数をいちいち書き換えないといけない
    二次元で範囲を選択出来ないことに関しては私の勘違いかもしれません。が、明らかに不満だったのがこの点。結構膨大な量のデータを並べ替えたかったので、指数をひとつずつ書き換えたくありませんでした。
    だからと言って第3引数にrow()を代入するとREFったり…。

Google Apps ScriptでHLOOKUPの拡張的なことをしてみる

そこで、時間はかかりそうだけれど思い切ってGASでデータの並べ替えをしてみることにしました。

シートを2つ作成し、この状態から始めます。

シート名:元データ

スクリーンショット 2018-03-20 3.48.57.png

シート名:並べ替え後

スクリーンショット 2018-03-20 3.49.05.png

スクリプト

function sort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("元データ");
  var newSheet = ss.getSheetByName("並べ替え後");
  for (var j=1; j<=5; j++){      // 元データの1行目の配列からそれぞれ値を取り出す     
    for (var i=1; i<=5; i++){    // 並べ替え後の1行目の配列からそれぞれ値を取り出す(検索用キーワード)
      var query = newSheet.getRange(1, i).getValues();  // 並べ替え後の1行目の配列  
      var data = dataSheet.getRange(1, j).getValue();   // 元データの1行目の配列  
      // 以降、総当たり式で検索
      if(data == query){ // 各列の組み合わせに対して検索キーワードと元データの最上行が一致したら
        for (var k=2; k<=4; k++) {                          // 各行で並び替えを行う
          var value = dataSheet.getRange(k,j).getValue();
          newSheet.getRange(k,i).setValue(value);   // 並べ替え後に出力したいセルを選んで出力
        }
      }
    }
  }
}

実行結果

各費用の並びはそのままに、列ごとにしっかり並べ替えできました。
スクリーンショット 2018-03-20 4.01.57.png

補足

.getValues()などを使ってAPIを叩きまくると処理が遅くなり最悪止まってしまうらしいので、もっと良い方法を考えたいところです。