はじめに
本記事はプロもくチャット Adevent Calendar2023の16日目です
ああ...それにしてもJSONをパースしたい...!!
GAS触っているとバックエンド側で保持してる値をスプレッドシートで確認したいシーンってありますよね(トリガー使った逐次処理の途中経過とか)。かといって、それだけのためにセルに合わせてデータを整形するのも面倒です
私のように怠惰な人間はJSON.stringifyした文字列をぶん投げたあたりでやる気がゼロになります
(:3_ヽ) _ <もう疲れちゃって全然動けなくてェ...
つくってみた
やはりスプシに出力する値はスプシで取り回した方が都合が良いよねと思い
特定のセルに格納されたJSON形式の文字列をparseする関数
を作ってみました。まずは完成形をご覧ください
=ByRow(
TransPose(
Split(
RegExReplace(引数1,"[{|}|""]", ""),
","
)
),
Lambda(row, Split(row, ":"))
)
それなりに複雑な作りですが、ここで登場する関数は
使いこなすとスプレッドシートの扱いが1段階レベルアップ出来るものばかりです 
以下に各関数で何をしているか？をステップ毎に解説していくので、最初は分からなくても繰り返し眺めてみることをオススメします
いつか「あ！ あのときQiitaでやったところだ！」
となることを祈っております
解説
例としてこんなサンプルJSONを使います
文字列が入力されたら...
| JSON |
|---|
{"max":4,"update_to_len":0,"state":"done","message":"done","updated_at":1702603186476} |
このようなデータにして返したいということですね
| A | B | |
|---|---|---|
| 1 | max | 4 |
| 2 | update_to_len | 0 |
| 3 | state | done |
| 4 | message | done |
| 5 | updated_at | 1702603186476 |
ノイズになる文字を取り除く
まず、波括弧とダブルクォーテーションはノイズとなるので除去します
=ByRow(
TransPose(
Split(
+ RegExReplace(引数1,"[{|}|""]", ""),
","
)
),
Lambda(row, Split(row, ":"))
)
スプレッドシートでは、文字列内で
""でエスケープすると
ダブルクォーテーションを指定できます
サンプル文字列だとこんな変換が行われます。{}"の３種類の記号が削除されましたね
- {"max":4,"update_to_len":0,"state":"done","message":"done","updated_at":1702603186476}
↓ {}"を除去
+ max:4,update_to_len:0,state:done,message:done,updated_at:1702603186476
REGEXREPLACE関数
- 特定の正規表現にマッチする文字列を置き換えます
プロパティ分割
つづいて、,で文字列を分割して、プロパティの配列データに変換します
複雑になるので、本関数でプロパティの値として想定しているものはプリミティブな値のみです
=ByRow(
TransPose(
+ Split(
+ RegExReplace(引数1,"[{|}|""]", ""),
+ ","
+ )
),
Lambda(row, Split(row, ":"))
)
サンプルだとこんな変換が行われます。
,が消えて、キーと値のペアが配列の要素になりました
- max:4,update_to_len:0,state:done,message:done,updated_at:1702603186476
↓ ","で分割
+ [ max:4 update_to_len:0 state:done message:done updated_at:1702603186476 ]
SPLIT関数
- 文字列を特定の区切り文字で分割します
配列の向きを変える
Splitの出力は横方向に展開されているので、これを縦方向に転置します
=ByRow(
+ TransPose(
+ Split(
+ RegExReplace(引数1,"[{|}|""]", ""),
+ ","
+ )
+ ),
Lambda(row, Split(row, ":"))
)
サンプルだとこんな変換が行われます
キーと値のペアが１つだけ含まれた配列が縦に５つ並ぶようになりました
- [ max:4 update_to_len:0 state:done message:done updated_at:1702603186476 ]
↓ 行列を転置
+ [ max:4 ]
+ [ update_to_len:0 ]
+ [ state:done ]
+ [ message:done ]
+ [ updated_at:1702603186476 ]
TRANSPOSE関数
- 配列の行と列を入れ替えます
1行ずつループ処理
キーと値のペアを順番に処理するために
ByRowとLambdaで、rowという変数に格納します
+=ByRow(
TransPose(
Split(
RegExReplace(引数1,"[{|}|""]", ""),
","
)
),
+ Lambda(row, Split(row, ":"))
)
BYROW関数
- 指定したデータを1行ずつループ処理します。処理の中身はLAMBDA関数で指定します
rowに格納されたキーと値のペアを、先ほど登場したSplit関数で分割します
=ByRow(
TransPose(
Split(
RegExReplace(引数1,"[{|}|""]", ""),
","
)
),
+ Lambda(row, Split(row, ":"))
)
サンプルだとこんな変換が行われます
キーと値のペアが1つだけ含まれた配列が、[key value]という形式の2要素の配列に変換されました
- [ max:4 ]
- [ update_to_len:0 ]
- [ state:done ]
- [ message:done ]
- [ updated_at:1702603186476 ]
↓ ":"で分割
+ [ max 4 ]
+ [ update_to_len 0 ]
+ [ state done ]
+ [ message done ]
+ [ updated_at 1702603186476 ]
スプレッドシートは数式の出力が配列の場合、自動的に複数セルに展開してくれます。
結果として上記のデータは5行 x 2列のセル範囲に出力されるので、求めたい出力が得られました
おわりに
JSON文字列を特定のセル範囲に展開する数式を紹介しました。
この辺の操作が寝起きの頭で出来るようになれば、
あなたも立派なエクセル職人です 
胸を張ってエクセル・スプシを改良しまくりましょう
何かのご参考になれば幸いです。ご覧いただきありがとうございました🦍
注意
本記事で紹介している関数は、全てのJSON文字列を解析できることを保証しておりません。あくまでもシンプルなオブジェクトを対象としていることをご留意いただけますようお願いします