スプレッドシートのQUERY関数にてデータ列を指定する方法

はじめに

GoogleスプレッドシートにおいてQUERY関数を使用すると、SQL文を使ってテーブルを操作することができます。
https://support.google.com/docs/answer/3093343

QUERY関数を使用するとデータベースとビューを分離させることができます。
これはスプレッドシートのフィルタ機能と比較して、データの変更後にビューの設定をやり直す必要が無いという利点があります。

以下に'table'シートに記入されたデータベースから、QUERY関数を使って'dept'列でフィルタする例を記します。

'table'シートにおけるデータベース

QUERY関数によるクエリと結果

	A
1	=QUERY(table!A:H, "where B = 'Sales'")

QUERY関数における問題点

QUERY関数のクエリにおいて列名はレター(A,B,..)を使用しなければならず、テーブルの列名を使うことができません。
このため、データベースに列が挿入された場合にクエリを修正しなければならない可能性があるのと、クエリの可読性が低くなってしまうという問題点があると思われました。
そこで本記事においては、セル式を使って列名からレターを求める方法と、GASを使ってクエリ内の列名をレターに変換する方法を検討します。

列名を計算する方法

下記の式を用いて列名からレターを計算することができます。

	A	B	C
1	=SUBSTITUTE(ADDRESS(1, B1, 4), 1, "")	=MATCH(C1, table!A1:H1, 0)	dept
2	=QUERY(table!A:H, "where "&A1&" = 'Sales'")

Col1記法による方法

QUERY関数の第1引数にてセル範囲を波括弧で括った配列式で指定すると、列名をCol1記法で指定することができるので、列番号をレターに変換する手間が少し減ります。

	A	B	C
1	="Col"&B1	=MATCH(C1, table!A1:H1, 0)	dept
2	=QUERY({table!A:H}, "where "&A1&" = 'Sales'")

GASによって列名を置換する方法

上記まではシート関数のみで列名を計算する手法ですが、相変わらずクエリの可読性がよくありません。
そこでGAS(GoogleAppScript)を使った関数を用いて、クエリ内の列名をレターに変換する方法を下記に記します。

マクロ.gs

function Query_ReplaceColumn(header_addr, query, coltype=['A','Col1'][0]) {
  let column_toLetter = function(col) {
    const letters = "ZABCDEFGHIJKLMNOPQRSTUVWXY";
    return col > 0 ? arguments.callee(Math.floor((col-1)/26)) + letters[col%26] : '';
  }
  let str_replaceAll = (str, substr, newSubstr) => str.split(substr).join(newSubstr);

  // 'header_addr'からテーブルヘッダの範囲を求める
  let header_addr_range = SpreadsheetApp.getActiveSheet().getRange(header_addr);
  let header_range = (header_addr_range.getValues().length == 1 
                      ? header_addr_range.getDataRegion(SpreadsheetApp.Dimension.COLUMNS) 
                      : header_addr_range);

  // 'coltype'から列名変換の関数を選択
  let column_letter = (function(){
    let A_offset = header_range.getColumn();
    let Col1_offset = 1+header_range.getColumn()-header_addr_range.getColumn();
    return {'A'   : ((index) => column_toLetter(index+A_offset)),
            'Col1': ((index) => 'Col'+(index+Col1_offset))
           }[coltype];
  })();
  if (!column_letter){
    throw new Error(`パラメータ3は'${coltype}'です。次のいずれかを設定してください: 'A', 'Col1'`);
  }

  // 'query'の[列名]を置換
  let column_names = header_range.getValues()[0];
  column_names.forEach(function(column_name, index){
    if (column_name){
      let letter = column_letter(index);
      query = str_replaceAll(query, `[${column_name}]`, letter);
    }
  });

  return query;
}

Query_ReplaceColumnは第一引数にテーブル列名のセル範囲を指定します。第二引数にはクエリを指定しますが、列名を角括弧[]で括って記入すると列名をレターに変換したクエリを返すので、これをQUERY関数に与えて使用します。

	A	B	C
1	=Query_ReplaceColumn(CELL("address", table!A:H), "where [dept] = 'Sales'")
2	=QUERY(table!A:H, A1)