Googleスプレットシートから入れ子(ネスト)構造のJSONを書き出してみる

Googleスプレットシート便利ですよね。便利すぎて幸福度爆上がりです。

入れ子(ネスト)なしJSONを書き出すのは難しくないのですが、**入れ子(ネスト)ありのJSON**を書き出す必要があるので死ぬ気で調べました:skull:

あまりにもヒットしなさすぎて死ぬかと思った:weary:

# スプレットシートからJSONを書き出したい
**400点以上あるデザインアイテムを管理**しているのですが、その結果が**列A〜AYの51列、495行ものスプレットシート**。
今後も追加していくので、間違いなく成長しますw（MacBook Pro16インチで見るのは大変キビシイ:rolling_eyes:）

近々メンテをする予定なのですが、実際のスプレットシートを大公開するわけにもいかず、同じようなサンプルデーターが落ちてないかな？と考えていると。

あったんですよ。

『[**青空文庫**](https://www.aozora.gr.jp/)』の作家一覧データベースが。

今回はこれを例に記事を書こうと思います。

## スプレットシートにやって欲しいこと
次の事をスプレットシートにはお願いしたい。
1. **情報を一元化したい**
1. 一元化されたデーターから**作家別に書籍情報を抽出**してスプレットシートを作りたい
1. 書籍情報は**入れ子(ネスト)ありのJSON**で書き出したい
1. **複数の作家別シートをひとつにまとめて**JSONに書き出したい

『1』に関しては『[青空文庫｜公開中　作家リスト：全て](https://www.aozora.gr.jp/index_pages/person_all.html)』からCSVをダウンロードして、スプレットシートにインポートします。
ExcelでCSVを開くとファイル形式やら何やら尋ねられますが、GoogleドライブにCSVを放り込むと簡単にインポートしてくれます。

サブメニューを出し、**アプリで開く > Google スプレットシート**
![CSVをインポート](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44795/f115fc4c-4f52-7f51-c5de-ec8ac107bc5b.jpeg)
なんて便利な！:blush:

すでに整理されているデーターなので、今回用にメチャクチャになるようにソートしましたw
このCSVをDLした時点で**17798作品**あるらしく、すごい量のデーターを見ることができますw
たまにスプレットシートが処理を停止するぐらいに重いw

:open_file_folder: [青空文庫CSV - Google スプレッドシート](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0)

## 書き出して欲しいJSON形式
書籍名・発行元・発行日・著者・収録作品ID・収録作品タイトル・図書カードURLをこんな感じにできるとうれしい。
```JSON:こんな感じにJSON出力したいな
[
    {
        "originalText": "現代日本文學全集　第三〇篇　芥川龍之介集",
        "publisher": "改造社",
        "issueDate": "1928（昭和3）年1月9日",
        "author": {
            "lastName": "芥川",
            "firstName": "竜之介"
        },
        "titles": [
            {
                "id": 24453,
                "title": "蜜柑",
                "url": "https://www.aozora.gr.jp/cards/000879/card24453.html"
            },
            {
                "id": 24454,
                "title": "藪の中",
                "url": "https://www.aozora.gr.jp/cards/000879/card24454.html"
            }
        ]
    }
]
```

# Query関数を使って別シートに内容を抽出する
全作品が入っている [:open_file_folder: 青空文庫CSV](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0) から、直接JSONを取り出すとめちゃくちゃ時間かかりそうだし、今回は特定の作家以外の作品は排除したい。
 [:open_file_folder: 青空文庫CSV](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0)にソートをかけてもいけど、**原本には手を入れたくない**。

なので、**作家別**のシートを作ろうと思います。

基本、抽出する時はこんな感じ。
```title:スプレットシート関数
= QUERY('スプレットシート名'!参照範囲,"クエリ(検索項目)",[見出し行数])
```
このままだと [:open_file_folder: 青空文庫CSV](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0)と同じ内容になるので、**select句**を使ってJSONに書き出したい内容の列を指定します。
```title:スプレットシート関数
= QUERY('青空文庫CSV'!A3:BD,"select A, B, N, P, Q, Z, AB, AC, AD",1)
```

## 芥川龍介の著作一覧を作りたい
「芥川」の著作だけを抜き出したいので、**where句**で条件抽出します。

```title:スプレットシート関数
= QUERY('青空文庫CSV'!A3:BD,"select A, B, N, P, Q, Z, AB, AC, AD where P = '芥川'",1)
```
 :open_file_folder: [芥川一覧](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=640284483&fvid=686159074)ではB2にクエリを書いて、B5の関数は```=QUERY('青空文庫CSV'!A4:BD,B2,1)```としています。

「**竜之介じゃない人**」フィルタで確認すると、**紗織さん**・**多加志さん**も**一緒に抽出**している事がわかります。

通常のフィルタを使った場合、共同編集者の表示にもフィルタがかかるので、利用者によってはとても迷惑です。
しかし、**名前をつけたフィルタを使用した場合、共同編集者の表示に影響しない**ので非常に便利です。
![竜之介じゃないひとフィルタ](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44795/c79db62e-9508-1f2d-0afc-a52f987ede97.gif)

今回は**竜之介さん**の著作一覧を作りたいので複数条件にします。

```diff:スプレットシート関数
- = QUERY('青空文庫CSV'!A3:BD,"select A, B, N, P, Q, Z, AB, AC, AD where P = '芥川'",1)
+ = QUERY('青空文庫CSV'!A3:BD,"select A, B, N, P, Q, Z, AB, AC, AD where P = '芥川' and Q = '竜之介'",1)
```
また、[:open_file_folder: 青空文庫CSV](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0) は**作品ID**からリストが始まっていますが、今回は書籍ごとにまとめる予定なので、列の表示順も変更します。

さらに、出版年ごとにソートしたいのですが、**Query関数で抽出した一覧では「フィルタ」によるソートができません**。
クエリにソートを追加します。
今回は**底本初版発行年1**と**底本名1**で昇順にします。
```diff:スプレットシート関数
- = QUERY('青空文庫CSV'!A3:BD,"select A, B, N, P, Q, Z, AB, AC, AD where P = '芥川' and Q = '竜之介'",1)
+ = QUERY('青空文庫CSV'!A3:BD,"select AB, AC, A, B, AD, N, P, Q where P = '芥川' and Q = '竜之介' order by AD asc, AB asc",1)
```

で、ここで気づいたのですが、『[**青空文庫**](https://www.aozora.gr.jp/)』では「**芥川龍之介**」を 「**芥川竜之介**」と表記しています。
誤植じゃないの？ってTwitterで呟いている人を見かけたのですが、どうやら**誤植ではない**様子。

:::note info
引用：[「竜」と「龍」はどちらが正しいの？ | 生徒の広場 | 浜島書店](https://www.hamajima.co.jp/rekishi/qa/a23.html)
> 2002年から使用する歴史の教科書では，次のようになっていますよ。
> 坂本竜馬…東京書籍，教育出版，日本書籍，帝国書院，清水書院
> 坂本龍馬…大阪書籍
> 芥川竜之介…東京書籍
> 芥川龍之介…大阪書籍，帝国書院
:::

「竜」は「龍」を簡単にした漢字で、前者は**常用漢字**です。また、「龍」は**人名用漢字**ではありません。
そのため、**国語審議会の方針に従う**パターンと**本人が使っていた漢字を優先する**パターンで表記が異なるようです。
『[**青空文庫**](https://www.aozora.gr.jp/)』は「**芥川竜之介**」パターンを採用しているため、この記事内も「**竜之介**」表記で行こうと思います。

この関数を使用した :open_file_folder: [芥川竜之介一覧｜翻訳作品あり](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=2124855855)を確認してみると、**昇順**にした関係で、**底本初版発行年1**と**底本名1**が空欄の作品が一番最初に表示されます。

詳細を調べてみたところ、「**後世**」という作品の**英訳版**が『[**青空文庫**](https://www.aozora.gr.jp/)』に登録されていることがわかりました。
「**後世**」自体は著作権が失効してパブリックドメインになっていますが、翻訳者の著作権が発生し「[クリエイティブ・コモン4.0](https://creativecommons.org/licenses/by/4.0/deed.ja)」による公開がなされています。
今回のJSONには翻訳者の項目を設けなかったので、英訳版「**後世**」はJSONに追加しない事にしました。

そこで、この項目を取り除くため、「**底本名1**」が空欄だった場合、その項目は抽出しない事にします。
```diff:スプレットシート関数
- = QUERY('青空文庫CSV'!A3:BD,"select AB, AC, A, B, AD, N, P, Q where P = '芥川' and Q = '竜之介' order by AD asc, AB asc",1)
+ = QUERY('青空文庫CSV'!A3:BD,"select AB, AC, A, B, AD, N, P, Q where AB is not null and P = '芥川' and Q = '竜之介' order by AD asc, AB asc",1)
```
やっと「**芥川竜之介**」のパブリックドメイン作品のみを抽出できました。→  [:open_file_folder: 芥川竜之介一覧](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=1426951531)
正しく抽出されているかは、[:open_file_folder: 青空文庫CSV](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=0) シートの「**芥川竜之介**」フィルタを使うと確認できます。

# JSONを書き出すためのGASを追加する
JSONを書き出すために、**Apps Script**に3つのファイルを追加します。
![追加するファイル](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44795/65417bb7-36ca-7f9d-9d36-ab528b3b01fc.jpeg)

これ、つらかった…めっちゃ辛いし、めっちゃ切り貼りした！:confounded:

ファイル名や関数名とかは不問で！不問でお願いします！
プログラムに弱いデザイナーには辛すぎる苦行…そうして出来た切り貼りGASがこちらです:japanese_ogre:

## JSONを作るGAS
```javascript:json_akutagawa.gs
//JSON生成
 function getJSON() {
  //スプレットシート(芥川竜之介一覧)の情報を取得
  const sheet = SpreadsheetApp.getActive().getSheetByName('芥川竜之介一覧'); 

  const data = sheet.getDataRange().getValues();
  const titleColumns = data[3]; //Keyが行4からデータが始まってるため。Keyが1から始まってる場合はdata[0];

//書き出したい項目
  const iOriginalText = titleColumns.indexOf('originalText');
  const iPublisher = titleColumns.indexOf('publisher');
  const iId = titleColumns.indexOf('id');
  const iTitle = titleColumns.indexOf('title');
  const iIssueDate = titleColumns.indexOf('issueDate');
  const iUrl = titleColumns.indexOf('url');
  const iLastName = titleColumns.indexOf('lastName');
  const iFirstName = titleColumns.indexOf('firstName');
  
  //JSON形式配列
  let dataArray = [];
  let dataDic = {};

  for(let i = 5; i < data.length; i++) { //行6からデータが始まってるため。本来はvar i = 1
    let rowData = data[i]; 
    let key = rowData[iOriginalText]+ '|' + rowData[iPublisher]+ '|' + rowData[iIssueDate];
    if (dataDic[key] !== undefined) {
      dataArray[dataDic[key]]['titles'].push(
             {
              "id":rowData[iId],
              "title":rowData[iTitle],
              "url":rowData[iUrl]
              }
      );
    } else {
      dataDic[key] = dataArray.push(
        {
          "originalText":rowData[iOriginalText],
          "publisher":rowData[iPublisher],
          "issueDate":rowData[iIssueDate],
          "author": {
            "lastName":rowData[iLastName],
            "firstName":rowData[iFirstName]
          },
          "titles": [
             {
              "id":rowData[iId],
              "title":rowData[iTitle],
              "url":rowData[iUrl]
             }
          ]
        }
      ) - 1;
    }
  }
  //JSON整形なし
  //return JSON.stringify(dataArray);

  //JSON整形あり 
  return JSON.stringify(dataArray, null, '\t')
}
```
## JSONをダウンロードするGAS
生成されたJSONのダウンロードを実行するために、**カスタムメニューに「芥川竜之介JSON」を追加**します。
また、ダウンロード用のダイアログを出すのですが、**いちいちクリックするのが面倒**なので、「**芥川竜之介JSON**」を選んだら**勝手にダウンロード**して**ダイアログを閉じる**ようにします。

```json_akutagawa.gs```の冒頭に**カスタムメニューを呼び出す処理**と**ダウンロードダイアログ表示**を追加

```javascript:json_akutagawa.gs
// カスタムメニューから呼び出す処理
function createJson() {
  const blob = dl_dialog();
  return blob;
}

//ダウンロードダイアログ表示
function dl_dialog() {
  //ダイアログテンプレート読み込み
  let html = HtmlService.createTemplateFromFile("dl_dialog").evaluate();

  //ダイアログ表示
  SpreadsheetApp.getUi().showModalDialog(html, "JSONファイルをダウンロード");
}

===== ここから下は「JSONを作るGAS」と同じ =====
//JSON生成
 function getJSON() {
  //アクティブなスプレットシート内の「芥川竜之介一覧」タブを取得
  const sheet = SpreadsheetApp.getActive().getSheetByName('芥川竜之介一覧'); 
⁝
```
ダウンロードする時に呼び出すテンプレートHTMLを追加
```html:dl_dialog.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type='text/javascript'>
      window.onload = function(){
        handleDownload();
      }

      //ダウンロード実行
      function handleDownload() {
        //JSONデータの取得
        const content = <?= getJSON(); ?>;

        //コンテンツタイプ
        const contentType = 'text/plain';

        //保存名
        const fileName = 'akutagawa.json'

        //blob生成
        const blob = new Blob([ content ], { "type" :  contentType});

        // aタグを生成
        const link = document.createElement('a');

        // ダウンロード
        link.download = fileName;
        link.href = window.URL.createObjectURL(blob);
        link.click();

        // ダイアログを閉じる
        google.script.host.close();
      }
  </script>
  </head>
  <body>
  </body>
</html>
</html>
```
カスタムメニューに「**芥川竜之介JSON**」を追加するスクリプト
```javascript:json_akutagawa.gs
function onOpen() {
  var ui = SpreadsheetApp.getUi()
  
  //メニュー名を決定
  var menu = ui.createMenu("JSON書き出し");
  
  //メニューに実行ボタン名と関数を割り当て
  menu.addItem("芥川竜之介JSON", "createJson");

  //menu.addSeparator(); 区切り線を追加
  
  //スプレッドシートに反映
  menu.addToUi();
}
```
こんな感じになります。
![JSONダウンロード](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/44795/5a78f36d-4e35-3e8f-9f77-00a15b8322c2.gif)

# 複数のシートをひとつにまとめて一覧を作りたい
複数作家の一覧を合体するのはあまりなさそうですが、「**カテゴリー**」ならあり得そう。
**芥川竜之介**を激推ししていた事で有名な**太宰治**と一緒にしてみます。

```title:スプレットシート関数
=QUERY({'芥川竜之介一覧'!B5:I;'太宰治一覧'!B6:I},"where Col1 is not null order by Col5 asc")
```
[:open_file_folder: 芥川竜之介&太宰治一覧](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=90001167)
シートの名前と抽出する範囲を```;```で繋げると幾つでも合体させることができます。「**太宰治一覧**」が```B6```から始まっているのは、項目タイトルを除外したためです(除外しないと行の一番下に表示されます）。

今までと違い**列の名前**ではなく**抽出する範囲から何番目のセル**という指定になります。
わかりやすいように「**底本初版発行年1**」の昇順にしてみました。

# ちょっと便利な関数
## 年月日付の年だけ取り出したい
『[**青空文庫**](https://www.aozora.gr.jp/)』に掲載されている作品の元になった書籍を「底本」と呼ぶのですが、違う版を参考にした作品もあるせいか、次のような表示もあります。

> 1968（昭和43）年11月15日、1984（昭和59）年12月25日38刷改版

これを年だけ表記にできないかなあ…と考えたわけです。
サンプルで作っているものなので、本来なら記載されたすべての西暦を抜き出すところですが、冒頭の「年」だけ抜き出す事にします。
[**REGEXEXTRACT**](https://support.google.com/docs/answer/3098244?hl=ja)関数を使って、**正規表現**で抜き出せればいいんだけどなあ。

```J6```に次の関数追加します。

```title:スプレットシート関数
=IFERROR(ArrayFormula(REGEXEXTRACT($F6:F, "(\d+)")),)
```
[:open_file_folder: 芥川竜之介&太宰治一覧](https://docs.google.com/spreadsheets/d/1CQiAy-VIhkElJ8kUV4NIgHhzoM7VB8g5TkqiNHDaeJA/edit#gid=90001167)

年月日をセルで分割するなら、こんな感じになるはず。
```title:スプレットシート関数
=IFERROR(ArrayFormula(REGEXEXTRACT($F6:F, "(\d+)（(.+)）.(\d+)月(\d+)日")),)
```
…どちらも全然うまくいってないw

ひとつのセルに2つ以上の年月日が入っている作品もあるし、年のみ記載しているものもあったりします。
書き方が一定ではないので、本格的にやるならもう少し捻りが必要。
もう疲れたので、今回は諦めたw

どちらかというと **[ArrayFormula](https://support.google.com/docs/answer/3093275?hl=ja)関数が便利**だよという方が大きかったりします。

１行づつ同じ関数を入れなくても```J6```に記載するだけで、**指定した範囲に適用**してくれます。
関数のコピペミスも防げて、超幸せになれるはず:angel:

正規表現云々よりも、これが便利だったから中途半端な状態で書いただけですw

# もう少しやりたかったこと

毎度おなじみ時間切れです:santa::christmas_tree::santa_tone1::christmas_tree::santa_tone2::christmas_tree::santa_tone3::christmas_tree::santa_tone4::christmas_tree::santa_tone5:

今回はスプレットシートもJSONのファイル名も指定して書き出しをしていますが、選択しているスプレットシート（タブ）を取得して、タブの名前をJSONファイルの名前として取得すれば、ひとつのGASで処理できると思います。
スクリプトにベタ書きすると横展開が面倒になるので、時間がある時にでもやりたいなあ。

…年末はめちゃくちゃ忙しいし、今日はクリスマス:gift:なのに、なんでこんな事してるんだろう:rolling_eyes:

# 参考リンク
- [JSON整形＆構文チェック：jsonデータ、jsonファイルをフォーマットする | ラッコツールズ🔧](https://rakko.tools/tools/63/)
- [スプレッドシートをJSON形式に出力する方法 - CARTA TECH BLOG](https://techblog.cartaholdings.co.jp/entry/archives/1377)
- [GASでJSONを任意の出力形式にしたい](https://teratail.com/questions/230170)
 
https://qiita.com/Yami_37/items/b4ed2bd2e20ffe7dfbca