【Googleスプレッドシート】APIレスポンスをセルに展開!UrlFetchAppとJSON.parseの基本

Googleスプレッドシートで外部APIからデータを取得し、セルに展開したいと思ったことはありませんか。UrlFetchAppを使えばGoogle Apps ScriptでHTTPリクエストを送信でき、JSON.parseでレスポンスを解析してシートに書き込むことができます。この記事では、基本的なコードの書き方から、実際にセルに展開するまでの手順を解説します。また、よくあるエラーや注意点もまとめていますので、初心者の方でも安心して実装できるでしょう。

UrlFetchAppとJSON.parseの基本的な仕組み

UrlFetchAppはGoogle Apps Scriptが提供するサービスで、HTTPリクエストを送信し、そのレスポンスを取得できます。これにより、外部のWeb APIと通信し、JSON形式のデータを取得することが可能です。JSON形式はキーと値のペアで構成される軽量なデータ構造で、人間にも読みやすい形式です。取得したJSON文字列はJSON.parseメソッドでJavaScriptのオブジェクトに変換することで、各データにアクセスできるようになります。その後、二次元配列に整形してsetValuesでシートに一括書き込みすることで、高速にデータを展開できます。この一連の流れを理解することで、様々なAPIからデータを取り込む応用が効くようになります。

APIレスポンスをセルに展開する具体的な手順

1. スクリプトエディタを開く
   スプレッドシートのメニューから「拡張機能」→「Apps Script」を選択してスクリプトエディタを開きます。新しいプロジェクトが作成されます。
2. 関数を記述する
   エディタに以下のコードを貼り付けます。この例では天気予報APIから東京の気温と天気を取得します。ただし、実際のAPIキーは各自で取得したものに置き換えてください。

   function getWeather() {
     var url = 'https://api.openweathermap.org/data/2.5/weather?q=Tokyo&appid=YOUR_API_KEY';
     var response = UrlFetchApp.fetch(url);
     var json = JSON.parse(response.getContentText());
     var temperature = json.main.temp;
     var data = [['気温', '天気'], [temperature, json.weather[0].description]];
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     sheet.getRange(1,1, data.length, data[0].length).setValues(data);
   }

3. コードの各処理を理解する
   最初にUrlFetchApp.fetchでAPIにリクエストを送信します。response.getContentText()でレスポンス本文を文字列として取得し、JSON.parseでオブジェクトに変換します。その後、必要なデータ(ここでは気温と天気)を抽出して二次元配列に格納します。最後にsetValuesでシートの左上セルから書き込みます。この例では1行目に見出し、2行目にデータを配置します。
4. 関数を実行して結果を確認する
   スクリプトエディタの実行ボタン(▶)をクリックして関数を実行します。初回は権限の承認を求められるので、内容を確認して許可します。承認後、スクリプトが実行され、アクティブなシートに気温と天気が表示されます。うまくいかない場合はログ(表示→ログ)でエラー内容を確認できます。

UrlFetchApp使用時の注意点とエラー対策

APIキーが不正な場合のエラー

APIキーが間違っている場合、401エラー(Unauthorized)が返されます。レスポンスコードをチェックするには、response.getResponseCode()を使用します。200以外の場合は処理を中断するなど、適切なエラーハンドリングを実装しましょう。

レスポンスが大きすぎる場合の対処

APIによっては大量のデータを返すことがあり、そのままsetValuesしようとすると実行時間制限(6分)を超える可能性があります。その場合は、データを分割して書き込むか、Queries APIの利用を検討してください。また、レスポンスサイズを制限するパラメータをAPIに指定できることもあります。

実行時間制限を超える場合

Google Apps Scriptの単一実行には6分の制限があります。長時間かかる処理はトリガーによる定期実行や、処理を複数の関数に分割することで対応します。また、URLFetchAppはタイムアウト設定(muteHttpExceptionsオプション)を利用して、長時間応答しないAPIを回避することも重要です。

UrlFetchAppとImportXMLの比較

この記事では、UrlFetchAppとJSON.parseを使ってAPIレスポンスをセルに展開する基本的な流れを解説しました。コードの各処理を理解することで、天気予報や株価情報など様々なAPIデータをスプレッドシートに取り込めるようになります。さらに、トリガーを設定して定期的にデータを更新する応用も可能です。実際のプロジェクトでぜひ活用してみてください。