Google Apps Scriptを使ってスプレッドシートをD3.jsで可視化する

データアナリティクス事業本部、池田です。
設計作業の中で、各処理の関連性（先行・後続、依存関係など）を有向グラフとして可視化することがありました。 始めは PlantUMLのオブジェクト図 で作図をしていたのですが、 表示する要素やそれらをつなぐ線の数が多くなると見づらくなってしまいました。

そこで、 D3.js を使用して情報の可視化 を試みました。 加えて、情報の更新を容易にするため、Google スプレッドシートを併用することにしました。

（細かいGoogle Apps Scriptの操作説明は省略しておりますので、 弊社の他のブログ などを参考にして下さい。 また、本文では以降それぞれ、GAS・スプレッドシート・d3と表記致します。）

作成結果

ブラウザ上で、3ファイル作るだけ で可視化できました。

こんなの（PlantUML）が

↓

こうなる（d3）

スクリーンショットにしてしまうと、どちらもなんとも微妙ですが… d3を使用した実際のアプリの方は、マウスオーバーで関連要素が強調表示され、 それらをドラッグで動かすことができます。（次節に動かせるサンプルを掲載します。） そして、スプレッドシートの内容を変更し、画面をリロードすることでその変更がすぐに反映されます。

Force Layoutというタイプのd3を使用しています。 こちら を基に追加の実装をしています。
（今回は私の表したいイメージに近いサンプルがd3のバージョンv3を使用していたので、 v3を使用していますが、現在はv4やv5があるようです。 Force Layoutは、v4からforceSimulationというものに置き換わったようですので注意が必要です。）

動作サンプル

（↓PCのChromeブラウザのみ動作確認をしています。）

それぞれの図形をマウスオーバーすると、アニメーションが発生します。 クリックすると、コンソールにその図形の情報が出力され、 クリップボードに英語名称がコピーされます。もうスペルミスしなくてすみます。

(ブログ掲載の都合上、スプレッドシートは使わずにHTMLにデータを埋め込んで掲載しています。 また、このサンプル以外は全てスクリーンショットを掲載しています。)

次章以降ではこちらのサンプルをもとに作成方法を説明していきます。

構成

ざっくり役割のイメージです。

• スプレッドシート
  1. 表示する要素（node）と、それらのつながり（link）の情報を保持
  2. d3がnodeやlinkとして読み込めるように、関数を使って情報を整形
• GAS
  1. 各スプレッドシートのどの部分を読み込むか、複数の設定セットを保持
  2. WEBアプリとして公開
  3. パラメータで指定されたスプレッドシートの情報を読み込む
  4. 読み込んだスプレッドシートの情報をHTMLに連携
  5. d3を実装したHTMLを表示

GAS内にスプレッドシートごとの設定を保持して、それらをGETパラメータで選択できるようにすることで、 複数のスプレッドシートを可視化 できるようにしています。 例えば、 https://{GASアプリのURL}?file={スプレッドシートAのID} でスプレッドシートAの内容を表示します。

スプレッドシート

GAS側でうまく読み込み・変換できるよう、JSONの形式で情報を整形します。 スクリーンショットでは入力部分のヘッダーをオレンジ色、計算式部分を灰色にしています。

node部分

• シート名： 定義
• D2計算式：

  ="{""id"":"""&B2&""",""label"":"""&A2&""",""type"":"""&C2&""",""title"":"""&B2&""",""size"":2000,""shape"":"&IF(C2="月","""rect""","""ellipse"",""height"":0.5")&"}"

  • id： 一意にする。図形をクリックした時にクリップボードにコピーされる
  • label： 図形の中に表示する文言
  • title： ツールチップに表示する文言
  • size： 図形の大きさ（今回の実装だと2000くらいがちょうど良いと思います）
  • shape： 図形の形。 ellipse （楕円）か rect （長方形）を選択
    • このサンプルでは月を長方形、曜日を楕円で表現
  • height： ellipseの時の横に対する縦の比率。1にすると円、1未満だと横長の楕円

表示する要素（node）を定義しています。 GASではD列のみ読み取ります。
GAS側で正しく読み取れるかは、 JSON.parse('{読み取るセル（D列のセル）}') で パースできるかで確認できます。

link部分

• シート名： 関係
• B2計算式： =VLOOKUP(A2,'定義'!$A:$B,2,FALSE)
• D2計算式： =VLOOKUP(C2,'定義'!$A:$B,2,FALSE)
• E2計算式： ="{""source"":"""&D2&""",""target"":"""&B2&"""}"
  • source： 矢印の元のnodeのid
  • target： 矢印の先のnodeのid

前のシートで定義したidを使ってnodeのつながり（link）を定義しています。 GASではE列のみ読み取ります。

GAS

複数のスプレッドシートで共用で使用するので、バンドルではなく独立したGASプロジェクトとして作成します。 （スプレッドシートからではなく、Google ドライブ上でGAS単独で作成。）

2つのファイルを作成します。

とりあえず動かしたい場合は、コードをそのままコピーして main.gsのスプレッドシートの設定箇所だけ調整して公開すれば動くはずです。

スクリプト部分（main.gs）

スプレッドシートから情報を読み取って整形し、HTMLを表示します。

クリックでコードを表示する/折りたたむ

GitHub

ポイントのみ説明します。

/**
 * 設定
 */
var SETTINGS = {
    // ファイルIDごとに設定を記載する
    "{サンプル1のスプレッドシートID}": { // サンプル1（月と曜日）
        // スプレッドシート読み込み設定
        data: [{
                sheetName: "定義",
                nodeCell: [
                    [2, 4] // [2行目、4列目]（D2セル）から行末まで
                ],
                linkCell: []
            },
            {
                sheetName: "関係",
                nodeCell: [],
                linkCell: [
                    [2, 5] // [2行目、5列目]（E2セル）から行末まで
                ]
            }
        ],
        // d3の描画の設定
        display: {
            svgSize: 900,
            labelFontSize: "16px"
        }
    },
    "…省略…"
};

• data： スプレッドシートの情報をシートごとの配列で記載
  • sheetName： シートの名前
  • nodeCell： node情報の記載が始まるセルの位置
  • linkCell： link情報の記載が始まるセルの位置
• display： 描画の設定
  • svgSize： 描画領域のサイズ
  • labelFontSize： 図形の中に表示する文言の大きさ

{サンプル1のスプレッドシートID} にIDを記載して下さい。IDはスプレッドシートのURLなどから取得できます。

↓getData()メソッド

        sheet.nodeCell.forEach(function(cell) {
            var defs = dataSheet.getRange(cell[0], cell[1], dataSheet.getLastRow()).getValues();
            defs.forEach(function(jsonStr) {
                if (jsonStr[0]) {
                    nodes.push(JSON.parse(jsonStr));
                }
            });
        });

• getRange() と getValues() で、行末 getLastRow() までセル情報を取得
• JSON.parse() で読み取ったデータをオブジェクトにして、別配列に格納

    links = linksById.map(function(l) {
        var s, t;
        for (var i = 0; i < nodeLen && (s === undefined || t === undefined); i++) {
            if (nodes[i].id === l.source) {
                s = i;
            } else if (nodes[i].id === l.target) {
                t = i;
            }
        }

        return {source: s, target: t};
    });

• linkはnodeのインデックスで表記する必要があるので、ここで変換

/**
 * getリクエストを受ける
 */
function doGet(e) {
    // …省略…

    html = HtmlService.createTemplateFromFile("index");
    // パラメータをhtml側に引き渡す
    html.param = JSON.stringify({
        data: data,
        display: SETTINGS[fileId].display
    });

    // …省略…

    return html.evaluate();
}

• GETリクエストがあると doGet() メソッドが呼び出される
• index.htmlファイルを指定してHTMLを生成し、パラメータを引き渡し、それを結果として返す

HTML（d3）部分(index.html)

d3部分です。

クリックでコードを表示する/折りたたむ

GitHub

もともとのサンプルから変更した箇所にはコード中にコメントを記入しています。 （アロー関数など、追加実装部分の記法が浮いているのはご了承下さい。）
こちらもポイントのみ説明します。

• 冒頭付近の <?=param?> の記述で、GAS側からparamという名称で引き渡されたスプレッドシートの情報を受け取り
• force 変数： Force Layout 全体の設定（引き合う力や、linkの長さなど）
• svg, link, node, label 変数： 描画領域やnodeなどの設定
• tick 関数： ドラッグされた時にラベル位置などを再設定
• mouseOverFunction などの関数： nodeにイベントリスナとして登録し、各挙動を設定

公開

GASの実装ができたら、ブラウザで表示できるよう公開します。

1. 『公開』タブから『ウェブアプリケーションとして導入…』
   

2. 公開範囲などを用途に合わせて適切に設定
   

3. アクセス権を承認
   

4. 公開完了
   

作成したWEBアプリのURLが表示されます。 パラメータが必要なので、ここで表示されたURLの末尾に ?file={スプレッドシートのファイルID} を加えてブラウザ（Chrome）で接続します。

完成です。

---

余談（PlantUML版）

これくらいの量であれば、PlantUMLでも見やすく表示できます。 使い分けをお勧めします。

作成例

試しに作ってみたのを掲載します。

例1

始めに掲載した処理間の関係を示すd3です。

スプレッドシート側

（量が多いため一部のみ掲載します。）

GAS側設定部分

var SETTINGS = {
    "{サンプル2のスプレッドシートID}": { // サンプル2（処理とアウトプット）
        // スプレッドシート読み込み設定
        data: [{
                sheetName: "定義_処理",
                nodeCell: [
                    [2, 3] // [2行目、3列目]（C2セル）から行末まで
                ],
                linkCell: []
            },
            {
                sheetName: "関係_処理",
                nodeCell: [],
                linkCell: [
                    [2, 5] // [2行目、5列目]（E2セル）から行末まで
                ]
            },
            {
                sheetName: "定義_アウトプット",
                nodeCell: [
                    [2, 3] // [2行目、3列目]（C2セル）から行末まで
                ],
                linkCell: []
            },
            {
                sheetName: "関係_処理-アウトプット",
                nodeCell: [],
                linkCell: [
                    [2, 5] // [2行目、5列目]（E2セル）から行末まで
                ]
            }
        ],
        // d3の描画の設定
        display: {
            svgSize: 2400,
            labelFontSize: "16px"
        }
    }
};

例2（失敗作）

星とか可視化してみたらおしゃれかと思ったのですが、太陽が大き過ぎました… （背景が全て太陽です 。）

今回の実装では、各node間の距離などを個別に設定できるようにはしなかったので、 こういったタイプを表したい場合はコード修正が必要そうです。

スプレッドシート側

GAS側設定部分

var SETTINGS = {
    "{サンプル3のスプレッドシートID}": { // サンプル3（星）
        // スプレッドシート読み込み設定
        data: [{
                sheetName: "定義",
                nodeCell: [
                    [2, 4] // [2行目、4列目]（D2セル）から行末まで
                ],
                linkCell: []
            },
            {
                sheetName: "関係",
                nodeCell: [],
                linkCell: [
                    [2, 5] // [2行目、5列目]（E2セル）から行末まで
                ]
            }
        ],
        // d3の描画の設定
        display: {
            svgSize: 2000,
            labelFontSize: "16px"
        }
    }
};

例3

少年マンガ？RPGゲーム？っぽいのを作ってみました。 こちらのサンプルは前述のものと異なり、各nodeの位置を指定し、かつ固定して形を保つようにしています。

スプレッドシート側

• G2計算式：

  =IF(A2="","","{""x"":"&900*E2/(MAX($E:$E)+1)&",""y"":"&900*F2/(MAX($F:$F)+1)&",""fixed"":1,""id"":"""&B2&""",""label"":"""&A2&""",""title"":"""&B2&""",""size"":2000,""shape"":""ellipse""}")

  • x： 横の位置
  • y： 縦の位置
    • E・F列に相対的な位置を記入してMAX関数などで座標を計算（900は描画する領域のサイズ）
  • "fixed":1 ： 位置を固定させる

GAS側設定部分

var SETTINGS = {
    "{サンプル4のスプレッドシートID}": { // サンプル4（戦闘）
        // スプレッドシート読み込み設定
        data: [{
                sheetName: "データ",
                nodeCell: [
                    [2, 7] // [2行目、7列目]（G2セル）から行末まで
                ],
                linkCell: [
                    [2, 8] // [2行目、8列目]（H2セル）から行末まで
                ]
            }
        ],
        // d3の描画の設定
        display: {
            svgSize: 900,
            labelFontSize: "16px"
        }
    }
};

参考文献

• Force layout graph with colour-coded node neighbours.
  • d3部分はこちらを基にして実装しています
• D3.jsを使ってかっこいい相関図を書きたい