APIで取得したJSONをいい感じにスプレッドシートに展開したい

SlackなどのAPIからJSONデータを取得したあとそれをスプレッドシートに展開する場合、オブジェクトのkeyを一個ずつ指定して二次元配列を作ってシートに展開...みたいなことをやっていました。

しかし、いちいちkeyを指定してやるのが面倒だしコードも見やすくはないのでkeyを全てシートの1行目に展開、対応する値を行ごとにセットする方法を考えてみました。

やりたいこと

画像左のSlackAPIで取得したユーザデータなどをスプレッドシート上にテーブルとして展開したい。

しかも もとのJSONのパターンに関わらず使い回せるように作っておきたい。

しかし、単純に配列をループしてObject.keysをループしても profile 以下のデータまでループする作りではないので以下画像のとおりオブジェクトのままセットされちゃいます。

えー、じゃあObjectの中のObject.keysもループしてさらに下の...うーん。

ネストされたJSONをテーブルにするため「フラット化」する

これ 配列みたいにフラット化すればいいんじゃね？と考えました。

しかし、配列をフラット化する Array.prototype.flat() はあってもObjectをフラット化するものは無いんですよね。多分。だってkeyが重複してエラーになるもの。

ということで、 profile.image_32 は profile.image_32 というkeyにセットしてObjectを生成し直す（フラット化する）関数を作って使い回すことにしました。

今回の肝になる flattenObj 関数

渡されたObjectの中でネストされているObjectをフラット化する関数です。

2階層よりも更に下であっても自分自身を呼び出して掘り下げていくので、階層の深いところのデータもテーブル化できます。

/**
 * ネストされたオブジェクトをフラットにする
 * @param {Object} obj ネストされたオブジェクト
 * @return {Object} フラット化されたオブジェクト
 */
const flattenObj = (obj) => {
  const result = {};

    // rootにあるkeyごとに処理
  for (const key in obj) {

    // 値を取得
    const value = obj[key];

    // value がObjectだった場合はflattenObj(自分自身)を呼び出して処理
    if (typeof value === "object") {
      const flatObj = flattenObj(value);

      // key と subkey を結合して root の key とする
      for (const subKey in flatObj) {
        result[`${key}.${subKey}`] = flatObj[subKey];
      }
    } else {
      result[key] = value;
    }
  }
  return result;
};

SlackAPIのユーザ情報をスプレッドシートに展開してみる

完成形はこちら。テストなので省略した json を直接定義してますが、SlackAPIの処理結果を渡せば全ての値がフラット化されて展開されます。

const json = [
    {
      id: "U01XXXXXXXXX",
      name: "alice",
      profile: {
        image_32: "https://a.slack-edge.com/xxxxx/img/icon_32.png",
        image_48: "https://a.slack-edge.com/xxxxx/img/icon_48.png"
      }
    },
    {
      id: "U02XXXXXXXXX",
      name: "bob",
      profile: {
        image_32: "https://a.slack-edge.com/xxxxx/img/icon_32.png",
        image_48: "https://a.slack-edge.com/xxxxx/img/icon_48.png"
      }
    }
  ];

// 配列内のObjectをフラット化
const flatJson = json.map((e) => flattenObj(e));

// 1つ目の配列のkeyをヘッダーとして定義
const headers = Object.keys(flatJson[0]);

// headers をループして生成した配列を body 配列にまとめる
const body = flatJson.map((row) => {
  return headers.map((key) => row[key] || "");
});

// headers と body を結合
const table = [headers].concat(body);

// setValues
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadSheet.getActiveSheet();
sheet.getRange(1, 1, table.length, table[0].length).setValues(table);

各ステップの解説

配列内のObjectをフラット化

現状のGASではES6シンタックスが有効なので Array の map メソッドを活用してフラット化されたObjectの配列を生成。

const flatJson = json.map((e) => flattenObj(e));

処理結果

// flatJson => 
[
    {
      id: "U01XXXXXXXXX",
      name: "alice",
      "profile.image_32": "https://a.slack-edge.com/xxxxx/img/icon_32.png",
      "profile.image_48": "https://a.slack-edge.com/xxxxx/img/icon_48.png"
    },
    {
      id: "U02XXXXXXXXX",
      name: "bob",
      "profile.image_32": "https://a.slack-edge.com/xxxxx/img/icon_32.png",
      "profile.image_48": "https://a.slack-edge.com/xxxxx/img/icon_48.png"
    }
]

1つ目の配列のkeyをヘッダーとして定義

flatJson の1つ目のObjectに対して Object.keys でヘッダー行を生成。

const headers = Object.keys(flatJson[0]);

処理結果

// headers => 
["id", "name", "profile.image_32", "profile.image_48"]

headers をループして生成した配列を body 配列にまとめる

flatJson をループ、さらに headers をループ処理してテーブルのbody部分を生成します。

row[key] || "" は値がない場合は空白をセットする if(row[key]) { return row[key]} else {return ""} のショートハンド。

const body = flatJson.map((row) => {
  return headers.map((key) => row[key] || "");
});

処理結果

// body => 
[
 ["U01XXXXXXXXX", "alice", "https://a.slack-edge.com/xxxxx/img/icon_32.png", "https://a.slack-edge.com/xxxxx/img/icon_48.png"],
 ["U02XXXXXXXXX", "bob", "https://a.slack-edge.com/xxxxx/img/icon_32.png", "https://a.slack-edge.com/xxxxx/img/icon_48.png"]
]

headers と body を結合

Array の concat メソッドを使って配列同士を結合します。

このとき body は二次元配列なのに対して headers は二次元配列ではないのでここで [headers] のようにして二次元配列同士を結合させる形にしています。

const table = [headers].concat(body);

処理結果

// table => 
[
 ["id", "name", "profile.image_32", "profile.image_48"],
 ["U01XXXXXXXXX", "alice", "https://a.slack-edge.com/xxxxx/img/icon_32.png", "https://a.slack-edge.com/xxxxx/img/icon_48.png"],
 ["U02XXXXXXXXX", "bob", "https://a.slack-edge.com/xxxxx/img/icon_32.png", "https://a.slack-edge.com/xxxxx/img/icon_48.png"]
]

setValues

ここはGASに慣れている方ならお馴染みだと思いますが、 getRange の範囲として table.length (行数) と table[0].length (列数) を指定して setValues で一括セットします。

const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadSheet.getActiveSheet();
sheet.getRange(1, 1, table.length, table[0].length).setValues(table);

処理結果

あとがき

多分他のAPI叩いたときも使えるはずなのでこれで楽できるはず！

最近はシステム間の連携開発することが多いので使い倒して行きたいですね。