GoogleスプレッドシートでJSONを扱ってみる

なんとなくスプレッドシートでカラムにJSONのテキストが入っている時には関数が用意されているのかなと思ったのですが、なさそうだったのでゴールデンウィーク中なのもあって簡単なものを作ってみようということで試しに作ってみました。 もしかしたら、一つのカラムに複数の値[1,2,3]のようなものを入れたいときには便利かもしれませんね。

作成する関数は、Googleスプレッドシートのメニューの[ツール] > [スクリプトエディタ]を選ぶとスクリプト編集の画面が開くのでそこに以下の関数を張り付ければ準備完了です。

function JsonQuery(json, query) {
  let queries = query.split('.');
  let current = null;

  let re = RegExp("[\[](.*?)]", 'g');

  for (let i = 0; i < queries.length; i++) {
    let q = queries[i];

    let key = q.split('[')[0];

    if (key == '$') {
      current = JSON.parse(json);
    } else {
      current = current[key];
    }

    let matches = q.matchAll(re);

    for (const m of matches) {
      current = current[parseInt(m[1])];
    }
  }

  return current;
}

そのあとは、シートのカラムA1などに{"message": "hello"}を入力してから、他のカラムに次を入力します。

=JsonQuery(A1, "$.message")

これでhelloと表示されれば問題なく動いています。 $.messageの部分ですがJSON Pathと呼ばれるものの簡易版になっています。ワイルドカード(*)や範囲選択などには対応していません。

スプレッドシートで実際に実行すると次のようになります。

JSON Pathの例

書いた後に気が付いたのですが、以下のページに解説だけではなくてJavaScript版も公開されていましたね😂 goessner.net