【Googleスプレッドシート】JSONデータをURLから取得して表に展開!UrlFetchAppでの解析

Web APIや外部サービスからJSON形式のデータを取得して、スプレッドシートに一覧表示したいと思ったことはありませんか。Google Apps ScriptのUrlFetchAppを使えば、任意のURLからJSONデータを取得し、表形式に展開できます。この記事では、UrlFetchAppの基本的な使い方から、取得したデータをスプレッドシートのセルに書き込む手順までをわかりやすく解説します。実際のコード例も紹介しますので、ぜひご自身のシートで試してみてください。

UrlFetchAppでJSONデータを扱う仕組み

Google Apps Scriptには、外部のWebサービスと通信するためのUrlFetchAppクラスが用意されています。このクラスのfetchメソッドを使うと、HTTPリクエストを送信してレスポンスを受け取れます。レスポンスは通常、JSON形式の文字列として返ってくるため、JSON.parse()メソッドでJavaScriptオブジェクトに変換します。変換後のオブジェクトは配列や連想配列になっており、これをスプレッドシートの行と列に合わせた二次元配列に加工します。最後に、シートオブジェクトのgetRange().setValues()メソッドでセルに一括書き込みします。この一連の流れを理解すれば、さまざまなAPIデータをスプレッドシートに取り込めるようになります。なお、UrlFetchAppの呼び出し回数には1日あたりの上限があるため、大量のデータを取得する場合は注意が必要です。

JSONデータを表に展開する具体的な手順

ここでは、実際にコードを書いてJSONデータをシートに展開する手順を説明します。例として、JSONPlaceholderのユーザー情報APIを使用します。

1. スクリプトエディタを開く
   スプレッドシートのメニューから「拡張機能」→「Apps Script」をクリックします。新しいスクリプトファイルが開きます。
2. 関数を定義する
   デフォルトの myFunction を削除し、以下のように関数名を fetchJSON としてコードを記述します。関数名は任意の名前に変更できます。
3. URLを指定してデータを取得
   コード内で var url = 'https://jsonplaceholder.typicode.com/users'; のように対象のURLを指定します。UrlFetchApp.fetch(url) でリクエストを送信し、レスポンスを取得します。
4. JSONデータをパースする
   response.getContentText() でレスポンスボディを文字列として取得し、JSON.parse() でオブジェクトに変換します。
5. 二次元配列に変換する
   data が配列の場合、そのまま二次元配列に変換します。連想配列の場合は、必要なキーを取り出して各行を作成します。例えば、data がオブジェクトの配列なら、map関数を使って各オブジェクトから必要なプロパティを抽出します。
6. シートに書き込む
   アクティブなシートを取得し、書き込み先の範囲を指定します。getRange(1, 1, rows.length, rows[0].length).setValues(rows) で一括書き込みします。

function fetchJSON() {
  var url = 'https://jsonplaceholder.typicode.com/users';
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var rows = data.map(function(user) {
    return [user.id, user.name, user.email, user.address.city];
  });
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

このコードを実行すると、シートの1行目からユーザーのID、名前、メール、市区町村が書き込まれます。データの列数や取得するプロパティは自由に変更できます。

よくあるエラーと対処法

URLの指定ミスやアクセス権限の不足

URLが間違っていると404エラーが返ってきます。また、一部のAPIは認証が必要なため、ヘッダーにアクセストークンを含める必要があります。UrlFetchApp.fetch()の第二引数にオプションオブジェクトとして headers を指定します。例えば、var options = {'headers': {'Authorization': 'Bearer YOUR_TOKEN'}}; のように指定します。

JSONパース時の型エラー

APIから返ってくるデータが想定した構造と異なる場合、JSON.parse()でエラーが発生します。事前にレスポンスをログ出力して確認しましょう。Logger.log(response.getContentText()) で確認できます。また、nullや空の値が含まれる可能性もあるため、map内で条件分岐を入れると安全です。

setValuesの範囲不一致エラー

二次元配列の行数と列数が、getRangeで指定した範囲と合わないとエラーになります。rows.length と rows[0].length を正しく使いましょう。また、空のデータが混入すると列数が不定になるため、map内で常に同じ列数の配列を返すようにします。想定外のデータが含まれる場合は、filterで除外するなどの処理を入れます。

UrlFetchAppの呼び出し制限

無料アカウントでは1日あたりのURL fetch回数に制限があります(おおよそ20,000回)。大量のデータを繰り返し取得する場合は、トリガーの実行間隔を調整するか、一度に取得するデータ量を増やすことで回数を減らせます。また、CacheServiceを利用して同じデータを繰り返し取得しない工夫も有効です。

UrlFetchAppと他のデータ取得方法の比較

まとめ

Google Apps ScriptのUrlFetchAppを使うことで、URLからJSONデータを柔軟に取得し、スプレッドシートの表形式に展開できます。この記事で紹介した手順をもとに、実際のAPIデータを取り込んでみてください。例えば、天気予報や為替レートなど、定期的に更新されるデータをトリガーで自動取得する応用も可能です。さらに、POSTリクエストやOAuth認証にも対応できるため、高度なAPI連携にも挑戦してみるとよいでしょう。