そうだ。Google App ScriptでスプレッドシートからJSONファイルを生成してみよう！

あるWEBサイトリニューアルで、CMSで管理していないエクセルデータをもとに、各レコードにある年度情報に従ってコンテンツ表示を切り替えられるようにする実装を行いました。
そこで、Google App Scriptで使って、非常に工数を削減できたお話をしたいと思います。

Google App Scriptとは？

Googleが開発し、提供しているプログラミング言語のことで、「JavaScript」をベースに開発された言語となっています。
Google Apps Scriptでは、一般的に下記のことが実装できます。

• プログラムの自動化

• Webサイトからの情報収集

• Webアプリケーション開発

またGoogleが提供しているGmail、Google Analyticsなどのサービスと連携を行えば、自動的にスプレッドシートで情報をまとめたり、Googleカレンダーへの登録など、日々の業務に活用できることばかりです。

スプレッドシートからJSONファイルを生成したい！

今回、実装内容として、スプレッドシートでまとめている膨大なデータからJSONファイルを生成した後、そのデータをJavascriptで描写するというのが今回のゴールでした。
「スプレッドシートでまとめている膨大なデータをJSONファイルに生成」
この部分をGoogleAppScriptを用いて試行錯誤した一部始終をご紹介します！

1. スプレッドシートでデータを整理する。

スプレッドシートでは、年度ごとに上記のようなデータ構造で管理をしていました。
※上記のデータは例となります。

2. 必要なJSON形式を確認する。

表示には、デザインとギミック上、下記のような構造のJSON配列が必要となりました。

 {
    "year": "2024",
    "item": [
      {
        "title": "タイトル2024-1",
        "link": [
          {
            "linkText": "2024-1の概要",
            "url": "<https://2024example.com>"
          },
          {
            "linkText": "2024-1の計画",
            "url": "<https://2024example.com>"
          }
        ]
      },
      {
        "title": "タイトル2024-2",
        "link": [
          {
            "linkText": "2024-2の概要",
            "url": "<https://2024example.com>"
          }
        ]
      }
     
    ]
  },
  {
    "year": "2023",
    "item": [
      {
        "title": "タイトル2023-1",
        "link": [
          {
            "linkText": "2023-1の概要",
            "url": "<https://2023example.com>"
          }
        ]
      },
      {
        "title": "タイトル2023-2",
        "link": [
          {
            "linkText": "2023-2の概要",
            "url": "<https://2023example.com>"
          }
        ]
      }
     
    ]
  },
  {
    "year": "2022",
    "item": [
      {
        "title": "タイトル2022-1",
        "link": [
          {
            "linkText": "2022-1の概要",
            "url": "<https://2022example.com>"
          }
        ]
      },
      {
        "title": "タイトル2022-2",
        "link": [
          {
            "linkText": "2022-2の概要",
            "url": "<https://2022example.com>"
          }
        ]
      }
     
    ]
  },

3. Google App Scriptを編集する。

早速 Google App Scriptの編集を行っていきます。
まず、スプレッドシートから「拡張機能」→「Apps Script」に移動してください。

次に、「コード.gs」というファイルが編集できるようになるので、必要なコードを追記していきます。
今回のコードは下記となります。

＜コード.gs＞

function generateJson() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4);
  const data = dataRange.getValues();

  const jsonData = [];

  data.forEach(row => {
    const [year, title, linkText, url] = row;
    const yearString = year.toString();

    let existingYear = jsonData.find(item => item.year === yearString);

    if (!existingYear) {
      existingYear = {
        year: yearString,
        link: []
      };
      jsonData.push(existingYear);
    }

    let existingTitle = existingYear.item.find(item => item.title === title);

    if (!existingTitle) {
      existingTitle = {
        title: title,
        link: []
      };
      existingYear.item.push(existingTitle);
    }

    const linkData = {
      linkText: linkText,
      url: url
    };

    existingTitle.link.push(linkData);
  });

  const json = JSON.stringify(jsonData, null, 2);
  return json;
}

function downloadDialog() {
  const html = HtmlService.createTemplateFromFile("dialog").evaluate();
  SpreadsheetApp.getUi().showModalDialog(html, 'json形式でダウンロード');
}
 
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('json')
    .addItem('json形式でダウンロード', 'downloadDialog')
    .addToUi();
};

今回はスプレッドシートに「json」というメニューを追加して、誰でも簡単にJSONを取得できるようにしています。

function downloadDialog() {
  const html = HtmlService.createTemplateFromFile("dialog").evaluate();
  SpreadsheetApp.getUi().showModalDialog(html, 'json形式でダウンロード');
}
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('json')
    .addItem('json形式でダウンロード', 'downloadDialog')
    .addToUi();
};

またこちらの関数によって、「json形式でダウンロード」をクリックすることで、スプレッドシートにダイアログを表示する処理を行っています。

ダイアログ

次にdialog.htmlというファイルを追加して、ダイアログの中身を編集していきます。

• エラーが起きた時の再度ダウンロードボタン

• ダウンロードするJSONファイルの名前の指定

＜dialog.html＞

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <button onclick="dl()">ダウンロードが始まらない場合はクリック</button>
 
    <script type='text/javascript'>
      function dl() {
        const json = <?= generateJson(); ?>;
        const blob = new Blob([json], {
          "type" : "application/json"
        });
        const link = document.createElement('a');
        link.href = window.URL.createObjectURL(blob);
        link.download = 'data.json';
        link.click();
      }
      dl();
  </script>
  </body>
</html>

4. 完成！！

Google App Scriptいかがだったでしょうか？
Google App ScriptでJSONを出力が可能となれば、CMSなどの要件がなく、データをとりあえず管理や確認をお願いしたい場合でも、スプレッドシートに見やすくまとめておくことができたり、急なデータ変更にも対応しやすくなります。
他にもGoogle App Scriptを使った様々な効率化方法もありますので、また続報を楽しみに待っていてください。