Google スプレッドシートを JSON 形式に変換する方法（サンプルコード付）[2022年版]

2022-01-082022-09-16

プログラムではデータを JSON 形式で扱いたいけど、管理はスプレッドシートでやっていきたい！
そんなことが結構ありましたので、方法を紹介していきます。

手軽さでは App Script 推しですが、どんな方法があるかも合わせて紹介していきます ('ω’)ノ

Contents

1. 対象
2. 方法① 手作業で頑張る
3. 方法② 正規表現で変換
4. 方法③ App Script で変換 [おススメ！]
5. 方法④ App Script 以外でプログラムする
6. おまけ：スプレッドシートに数字を書くときの注意

対象

今回は、これをJSON形式にしてみます。

方法① 手作業で頑張る

地道にタイピングする方法です。
やっぱり、仕事は 温かみのある方法 が一番！

評価：いやいや、無理！超たいへん！

これは、強靭な忍耐と精神を持ち、時間と気力があるエリートのための方法です。

方法② 正規表現で変換

エディタにコピーして、正規表現を使って変換する方法です。
息を吸うように書けると、すごい人に見えて評価があがるかも？

評価：毎回やるのは大変！

たとえば「秀丸」だと、こんな感じです。

タイプが1つしかないポケモンもいらっしゃいますので、2回にわけて置換します。

手順 ①

変換前： ^([^\t\n]+)\t([^\t\n]+)\t([^\t\n]+)$
変換後： { "num": "\1", "name": "\2", "type1": "\3" },

手順 ②

変換前： ^([^\t\n]+)\t([^\t\n]+)\t([^\t\n]+)\t([^\t\n]+)$
変換後： { "num": "\1", "name": "\2", "type1": "\3", "type2": "\4" },

手順 ③

先頭に [ を書いて、末尾のレコードの , を ] に変えて完成です。

方法③ App Script で変換 [おススメ！]

今回、私がおすすめする方法です！

評価：作業も管理もかんたん！

スプレッドシートの「拡張機能」から「Apps Script」を押します。

出てきた画面にこのコードをペタっと貼ります。

function myFunction() {
  const inputSheetName = 'シート1'; // 入力データがあるシートの名前です。
  const outputSheetName = 'JSON'; // JSONを出力するシートの名前です。

  const inputSheet = SpreadsheetApp.getActive().getSheetByName(inputSheetName);

  // 出力先のシートがなければ作ります。
  let outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(outputSheetName);
  if (!outputSheet) {
    outputSheet = SpreadsheetApp.getActiveSpreadsheet();
    outputSheet.insertSheet(outputSheetName);
  }

  // SpreadSheet は I/O が早くないので、できるだけデータをまとめて get します。
  var rows = inputSheet.getDataRange().getValues();

  outputSheet.getRange("A1").setValue('[');
  for (var itr = 0; itr < rows.length; ++itr) {
    const row = rows[itr];
    if (row[0] == "") {
      break;
    }

    const item = {
      num: row[0] || "",
      name: row[1] || "",
      type1: row[2] || "",
    };
    if (row[3]) {
      Object.assign(item, { type2: row[3] });
    }

    outputSheet.getRange("A" + (itr + 2)).setValue(JSON.stringify(item) + ((itr === rows.length - 1) ? "" : ","));
  }
  outputSheet.getRange("A" + (rows.length + 2)).setValue(']');
}