GoogleSpreadSheetのQUERY関数でフィールド名を使う【GAS】

はじめに

Googleのスプレッドシート、皆さん使っていますか？複数人でデータ整理の共同作業したりするのがとっても便利です。
また、ExcelにはないQUERY関数を使って、集計処理まで簡単にできちゃいます。

大きな問題点（使い勝手の悪さ）

えぇ、集計処理・・・できるのですが・・・本格的なSQL文として、SQL ServerやOracle、Accessのように使えるものではありません。

なぜなら、 SELECT文やWHERE句で「フィールド名」が使えない のです。
「フィールド」を指定するには、シートの「A列」、「B列」のように 列を直接 指定しなければなりません。

例

例えば、以下のような表があった場合に、

A	B	C	D
name	dept	salary	age
John	Eng	1000	35
Dave	Eng	500	27
Sally	Eng	600	30
Ben	Sales	400	32
Dana	Sales	350	25
Mike	Marketing	800	24

こんな感じで、deptがEngの人だけのnameとsalaryをageの昇順で抽出してみます。
=QUERY(A1:D7, "select A,C where B = 'Eng' order by D asc", 1)

こんな結果が出ます。

name	salary
Dave	500
Sally	600
John	1000

ダメな記述

ですが、こうはかけません。

=QUERY(A1:D7, "select name,salary where dept = 'Eng' order by age asc", 1)

これは、「#VALUE!」って怒られます。
んー、くぬぬ。

表に列を追加しちゃった！

で、ここで、問題になる点です。もし、QUERYの計算式を作った後に、後から表が変わったら・・・

A	B	C	D	E
name	dept	lunchTime	salary	age
John	Eng	12:00:00	1000	35
Dave	Eng	12:00:00	500	27
Sally	Eng	13:00:00	600	30
Ben	Sales	12:00:00	400	32
Dana	Sales	12:00:00	350	25
Mike	Marketing	13:00:00	800	24

「C列」に新しい列「lunchTime」を追加したので、salaryとageがズレてしまいました。
こうなると、QUERY関数のクエリを書き直しです・・・・。

#ネットで調べた解決方法
本当にこれできねーの？？って、思ったので調べてみましたが、ADDRESS関数やMATCH関数を使って、SQL文の文字列を加工しながら文字列連結しまくって、ものすごく長い計算式を書くような解決方法しか見つけることができませんでした。
探し方が悪くて、もしできる方法があるようでしたら、教えてください。

#GASで何とかしてみる
そこで、試行錯誤した結果、以下のようなGoogleAppsScript（GAS）で解決してみました。

方向性としては、QUERY関数に渡すクエリ文字列を、渡す前に加工してやれ、ってヤツです。
正直、やってることは、ADDRESS関数やMATCH関数で解決するのと変わりません。
ただ、いくぶんスマートに見えると思います。

解決方法実施後の記述例

さっきのダメクエリも、少し書き換えて、こんな風に使えるようになります。

=QUERY(A1:D7, ReplaceQueryFiledName("select `name`,`salary` where `dept` = 'Eng' order by `age` asc"), 1)

フィールド名を列名に置換するため、 バッククオート「`」でフィールド名を囲む ことをルールとしました。（スクリプトの正規表現を書き換えれば、ほかの記号でもいけます）

どうです？これだけで、SQL文っぽく扱えるようになりませんか？
また、先の問題だった「列追加による表改変」がおきても、クエリを変更する必要がなくなりました。

ちょっとだけ、便利そうに見せました。

ごめんなさい。ただ、上記の書き方は、いくつかの条件がそろわないとダメです（後述）。
実際にはReplaceQueryFiledName()は、３つの引数を受け取らないと動かせません。ちゃんと指定すると、こんな感じになります。

=QUERY(A1:D7, ReplaceQueryFiledName("select `name`,`salary` where `dept` = 'Eng' order by `age` asc", "シート1", "A1:D1"), 1)

「シート名」と「タイトルを格納しているセル範囲」を知らなければ、GASから引っ張って検索することが出来ません。「参照先とQUERY計算式が同じシートにあり、かつ、１行目にフィールド名が記載されている」場合にのみ省略できます。

用途を考えると、たいていの場合は、シート名ぐらいは省略せずに書くことになると思います。

ReplaceQueryFiledName()のソースコード

function ReplaceQueryFiledName(queryString, srcSheetName, srcFieldRangeString) {
  var result = queryString;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = null;
  if (typeof srcSheetName === "undefined") {
    sheet = spreadsheet.getActiveSheet();
  } else {
    sheet = spreadsheet.getSheetByName(srcSheetName);
  }

  var range = null;
  if (typeof srcFieldRangeString === "undefined") {
    var lastColumn = sheet.getLastColumn();
    range = sheet.getRange(1, 1, 1, lastColumn);
  } else {
    range = sheet.getRange(srcFieldRangeString);
  }

  var fields = range.getValues();
  var maxRow = fields.length;
  var maxColumn = fields[0].length;

  for(var row = 0; row < maxRow; row++) {
    for(var column = 0; column < maxColumn; column++) {
      var target = fields[row][column];
      if(target != null && target != "") {
        var cell = range.getCell(row + 1, column + 1);
        var a1 = cell.getA1Notation();
        var field = a1.match(/[a-zA-Z]+/);
        result = result.replace(/(`([^`]+)`)/g, function(match, group1, group2) {
          var rep = group1;
          if(group2 == target) {
            rep = field;
          }
          return rep;
        });
      }
    }
  }
  return result;
}

#スクリプトの説明（使い方）
ということで、ReplaceQueryFiledName()の説明です。（といっても、１行ごと説明はしません）
名前が長いって突っ込む人は、勝手に名前を変えちゃってくださいネ。

戻り値 [string]

フィールド名を列名に置き換えた文字列を返却します。

第一引数 queryString [string] （必須項目）

フィールド名を バッククオート「`」で囲んでいる SQL文を受け取ります。
シートを検索して、フィールド名を見つけ、該当する列名へ置き換えます。
フィールド名を見つけられなかった場合は、バッククオートが置き換えられずに残ったまま戻り値に返却されます。

第二引数 srcSheetName [string] （条件により省略可）

参照先のシート名を文字列で指定します。
省略した場合は、getActiveSheetで取得したシートに対して検索処理を行います。

第三引数 srcFieldRangeString [string] （条件により省略可）

フィールド名が格納されているセル範囲（Range）を文字列で指定します。
省略した場合は、"A1:xxx1"(列はgetLastColumnで取得)を指定したものとみなします。

※SpreadSheetの最大列数が256個らしいという情報を見て決めただけです。実際にシート上に列を作ってみたら500列とか普通に作れるので古い仕様かも？ただ、そこまで多い列を使うケースはないだろうと思います。
※2018/08/05 修正
パラメータ省略時の処理を256列固定で指定していましたが、getLastColumn()で取得すれば値が入っている最大列が取得できるの忘れてました。
このため上記スクリプトを修正しました。（タイトル行が１行目でない場合もあるので、範囲指定パラメータは必要だと判断してます）

#課題
本当は、QUERY式自体を置き換えてしまったほうがいいのでは？と思いましたが・・・

• QUERY式をGAS内部で呼び出して、結果を取得するスマートな方法がわからなかった。（新シート作って適当なセルにsetFormula？これは処理速度が気になる）
• GASのfunctionにQUERY式の第一引数を渡した場合、Rangeオブジェクトじゃなく、値の配列が入ってきた（っぽい：しっかり確認してない。汗）（※2018/07/26追記：Google Sheets APIを使えばできそうな気がする。調べてはいない）
• 文字列加工までに留まったほうが、扱いやすい？変換結果確認もしやすそう？

なんていう理由で、この形に落ち着きました。

もっと、素晴らしく使いやすい形にだれか改変してくれると嬉しいです。また、バグがあったら遠慮なくお知らせください。
それでは！