はじめに

はじめまして。株式会社iimonで主にバックエンドを担当している木暮です。業務の中でスプレッドシートにまとめたデータからJsonファイルをダウンロードできる機能を作りました。スプレッドシートに対してGoogle Apps Scriptを通して処理を行ったのは初めての経験だったので、やったことの整理も兼ねて共有します。

Google Apps Scriptとは

Google Apps Script は、Google Workspaceと統合されるビジネスアプリケーションを迅速かつ簡単に作成できる、アプリケーション開発プラットフォームです。最新の JavaScript でコードを記述し、Gmail、カレンダー、ドライブなどのお気に入りの Google Workspace アプリケーション用の組み込みライブラリにアクセスできます。何かをインストールする必要はありません。ブラウザから直接コードエディタが用意され、スクリプトは Google のサーバーで実行されます。

https://developers.google.com/apps-script/overview?hl=ja

要するに？どういうこと？

googleが用意してくれた環境とライブラリを使ってjavascriptを用いた拡張機能を組み込める。

実際に作ってみる

今回はスプレッドシートの値を元にJsonの作成してブラウザからダウンロードする一連の処理を簡単に実装していきます。

前提

googleアカウントが作成済みであること
スプレッドシートより新規ファイルを作成済みであること

以降使用するクラスのリファレンス

HtmlService https://developers.google.com/apps-script/reference/html/html-service?hl=ja

SpreadsheetApp https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=ja

onOpen https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder?hl=ja

成果物のイメージ

test.json

[
  { "key1": "value1", "key2": ["value2", "value3"] },
  { "key1": "value4", "key2": ["value5", "value6"] }
]

実装

1.画面に独自メニューを追加

拡張機能からApps Scriptを選択。

開発環境が開く。

HTMLファイルを用意する。+をクリックしてHTMLを選択。

任意のファイル名（例ではdllink.html）でHTMLを作成。

以下のコードのBodyに対して以下のaタグを追加。 aタグのdownload属性に指定したファイル名がダウンロード時のファイル名になります。

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
</head>
<body>
    <a id="download" href="#" download="test.json" onclick="#">ダウンロード</a>
</body>
</html>

追加後、実行ログボタン左横のセーブアイコンをクリック、またはcmd + s(macの場合)でHTMLファイルを保存する。

コード.gsに以下のコードを追加。

function download() {
  var dl_html = HtmlService.createTemplateFromFile("dllink").evaluate();
  SpreadsheetApp.getUi().showModalDialog(dl_html, "JSONファイルをダウンロード");
}

function onOpen() {
  var subMenus = [
    {
    name : "test.jsonをダウンロード",
    functionName : "download"
    }
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("JSON", subMenus);
};

OnOpen()

スプレッドシートを開いたタイミングで自動実行される関数です。 menuに追加する独自メニューの名前と独自メニュークリック時に動作するfunctionの定義してSpreadsheetApp.getActiveSpreadsheet().addMenu(name,subMenus)を使い画面に取得したシートのメニューに対して追加しています。

download()

OnOpen()内のmenuで定義しているfunctionの実体です。コードエディタ内のファイルから新しい HtmlTemplate オブジェクトの作成と作成したHtmlTemplateオブジェクトを使ってモーダルダイアログの表示をしています。

※HtmlTemplateとは https://developers.google.com/apps-script/reference/html/html-template?hl=ja

上記のコードを追加後、保存をする。

スプレッドシートに戻り画面を更新するとダウンロードのボタンが追加されます。 ※描画に数秒かかります

test.jsonをダウンロードをクリックしたときに承認ダイアログが表示される場合は続行をクリックしてスクリプトの実行を許可して下さい。

承認後、再度実行するとダウンロードダイアログが表示されます。 Jsonデータ作成処理を実装していないのでダウンロードができるファイルの中身はまだJsonファイルではありません。

2.Json作成元データの準備

以下のデータを元にJsonを作成します。

3.Json作成処理を実装

コード.gsに以下のコードを追加。

function getJsonData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var maxRow = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

  var jsonData = [];
  var spredSheetValueList = sheet.getRange(1,1, maxRow, 3).getValues();

  for (var key in spredSheetValueList) {
    jsonData.push(build(spredSheetValueList[key]));
  }

  return JSON.stringify(jsonData);
}

function build(row) {
  return  {
    "key1": row[0],
    "key2": [row[1],row[2]]
  };
}

getJsonData() Google スプレッドシート内の値を元にJSONデータを作成して返す関数です。
1. スプレッドシートのアクティブなシートを取得する
2. シートの最大行数を取得する
3. データが存在する範囲を取得する
4. データを1行ずつ build() 関数に渡して、JSONオブジェクトに変換する
5. 変換したJSONオブジェクトを配列に追加する
6. 全ての行の処理が完了したら、配列をJSON文字列に変換して返す。
こちらの処理のポイントはデータの存在する範囲のデータを一度に取得する点です。１行ずつデータを取得することもできますか、それだと毎回スプレッドシートにアクセスが走るので処理時間がとても長くなってしまいます。
build() 行ごとのJsonデータを作成し返却します。この関数の処理は作りたいJsonの形式によって異なりますので簡単な説明に留めます。

追加後、保存をします。

最初に用意したHTMLに機能を追加します。 scriptタグ内の追加とonclick時の定義の追加です。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type='text/javascript'>
      function handleDownload() {
        var json = <?= getJsonData(); ?>;
        var blob = new Blob([json], { "type": "application/json" });
        document.getElementById("download").href = window.URL.createObjectURL(blob);
      }
    </script>
  </head>
  <body>
    <a id="download" href="#" download="test.json" onclick="handleDownload()">ダウンロード</a>
  </body>
</html>

handleDownload()

ダウンロードリンクをクリックしたときに実行されます。スプレッドシートからJSONデータを取得し、Blobオブジェクトとしてエクスポートします。 ※Blobオブジェクトはファイルのバイナリデータを表すためのJavaScript標準オブジェクトですダウンロードリンクのhref属性にBlobオブジェクトのURLを設定し、ブラウザがダウンロードを行います。

上記スクリプトが記載できたら保存してください。

実行してみる

実装ができたので実際に動かしてみます。

メニューよりtest.jsonをダウンロードをクリック。

しばらく待つとモーダルが表示されるのでダウンロードをクリック。

ファイルを開くとJsonファイルができていることが確認できます。

最後に

スプレッドシートでもVBAのようにスクリプトを書いて処理を行うことができることを今回初めて知りました。また、VBAと比べてとても書きやすく、すんなりと処理を起こすことができ業務短縮につながり助かりました。シートにまとめられたデータを処理してダウンロードを行いたい場合にとても便利ですのでぜひ活用してみてください。