【Googleスプレッドシート】JSONファイルをパース!Apps Script経由でのオブジェクト展開

GoogleスプレッドシートでJSONデータを扱いたいとお考えではありませんか。APIから取得したJSONファイルをシートに取り込むには、専用の関数がなく戸惑われることも多いでしょう。この記事では、Apps Scriptを利用してJSONファイルをパースし、オブジェクトとして展開する方法を詳しく解説します。スクリプトの書き方から実行手順までを順を追って説明しますので、初めての方でも安心して実践いただけます。

JSONファイルをパースする仕組み

JSON(JavaScript Object Notation)は軽量なデータ交換形式です。多くのWeb APIがJSON形式でデータを返します。Apps ScriptはJavaScriptをベースにしているため、標準のJSON.parse()メソッドを使用してJSON文字列をオブジェクトに変換できます。変換後は、オブジェクトのプロパティにドット記法でアクセス可能です。例えば、data.nameやdata.items[0].idのように値を取得します。ファイルはGoogle Drive上にある場合、DriveAppで読み込むか、URLからUrlFetchAppで取得する方法があります。本記事ではDrive上のJSONファイルを読み込む手順を中心に説明します。

Apps ScriptでJSONファイルをパースする手順

以下の手順で、Google Drive内のJSONファイルをパースし、スプレッドシートにデータを展開します。

1. スクリプトエディタを開く

1. スプレッドシートを開く
   対象のスプレッドシートを開きます。新規作成する場合は、Googleドライブから新規スプレッドシートを作成してください。
2. 拡張機能メニューからスクリプトエディタを起動する
   メニューバーの「拡張機能」をクリックし、「Apps Script」を選択します。新しいタブでスクリプトエディタが開きます。

2. スクリプトコードを記述する

1. 関数名を定義する
   デフォルトのmyFunctionを削除し、parseJSONFile()という名前の関数を作成します。
2. JSONファイルを読み込む
   DriveApp.getFileById()でファイルを取得し、getBlob().getDataAsString()で文字列として読み込みます。ファイルIDは、Drive上のファイルURLの一部です。例:var file = DriveApp.getFileById('YOUR_FILE_ID');
var jsonString = file.getBlob().getDataAsString();
3. JSONをパースする
   var data = JSON.parse(jsonString);でオブジェクトに変換します。
4. シートに展開するための配列を準備する
   データが単純なキーと値のオブジェクトの場合、ヘッダー行とデータ行を配列に格納します。例えば、以下のように記述します。

   var sheet = SpreadsheetApp.getActiveSheet();
   var keys = Object.keys(data);
   var values = keys.map(function(key) { return data[key]; });
   sheet.getRange(1, 1, 1, keys.length).setValues([keys]);
   sheet.getRange(2, 1, 1, values.length).setValues([values]);

5. 配列データの場合はループ処理を行う
   データが配列(例:ユーザーリスト)の場合、各要素を1行として書き出します。例:

   var rows = [];
   var headers = Object.keys(data[0]);
   rows.push(headers);
   data.forEach(function(item) {
     var row = headers.map(function(h) { return item[h]; });
     rows.push(row);
   });
   var range = sheet.getRange(1, 1, rows.length, headers.length);
   range.setValues(rows);

3. 関数を実行する

1. 関数を選択して実行する
   スクリプトエディタのツールバーで「parseJSONFile」を選択し、実行ボタンをクリックします。
2. 権限を承認する
   初回実行時、スクリプトがスプレッドシートとDriveへのアクセスを要求するダイアログが表示されます。内容を確認し、「許可」をクリックします。
3. 結果を確認する
   元のスプレッドシートに戻ると、データが展開されていることを確認できます。

注意点とよくあるエラー

JSONのフォーマットが不正な場合

JSON構文に誤りがあると、JSON.parse()がエラーをスローします。ファイルが有効なJSON形式であることを事前に確認してください。オンラインのバリデーターでチェックするか、小さなデータでテストすることをおすすめします。エラーが発生した場合は、try…catchブロックで例外をキャッチし、エラーメッセージをログに出力すると原因特定が容易です。

大規模データのパフォーマンスの問題

大量のデータ(数十万行)をsetValues()で一度に書き込むと、スクリプトの実行時間制限(6分)に達する可能性があります。その場合は、データを分割して書き込むか、シートに直接インポートできるCSV形式に変換する方法を検討してください。また、スプレッドシートのセル数制限(1000万セル)も考慮する必要があります。

権限エラーが発生するケース

DriveApp.getFileById()を使用する場合、ファイルのアクセス権限が必要です。ファイルが自分以外のユーザーと共有されている場合でも、スクリプトを実行するアカウントがそのファイルにアクセスできる必要があります。権限不足の場合は「ファイルが見つかりません」というエラーが表示されます。ファイルIDが正しいか、共有設定を確認してください。

ネストされたオブジェクトへの対応

JSONが入れ子構造(オブジェクトの中にオブジェクト)の場合、単純なヘッダーと値のマッピングでは不十分です。再帰関数を使ってすべてのレベルを展開するか、特定のパスを取り出すようにコードを工夫する必要があります。例えば、ドット区切りのキーに変換してフラットにする手法が考えられます。

VLOOKUPやQUERYとの比較

まとめ

この記事では、GoogleスプレッドシートでJSONファイルをパースする方法を解説しました。Apps ScriptのJSON.parse()を活用すれば、APIから取得したデータも自在にシートに展開できます。特に、setValues()による一括書き込みやループ処理のテクニックは、業務効率化に役立ちます。次は、UrlFetchAppを使ってWeb APIから直接データを取得する方法にも挑戦してみてください。スクリプトを応用すれば、定期的なデータ更新も自動化できます。