外部APIのデータをスプレッドシートに自動蓄積するGASパターン

結論から先に: 時間主導トリガー→UrlFetchApp→シート追記という骨格自体は数行で組めます。実用に耐える形にするために効いたのは、その先に足した「想定件数からの乖離を検知する」という薄い異常検知レイヤーでした。

課題

外部の公開APIから定期的にデータを取得し、時系列で蓄積していきたい、という要望は個人開発でもよく出てきます。天気・為替・公共交通の運行情報など、対象は色々ありますが、共通しているのは「毎回手作業で見に行くのは非現実的」という点です。

最初は思いついたときにブラウザでAPIのレスポンスを開き、必要な値だけをスプレッドシートに手入力する、ということをしばらく続けていました。数日は続くのですが、忙しい日が2〜3日挟まると記録が抜け、あとで見返したときに「あの時どうだったか」が分からなくなる。これでは「蓄積する」という本来の目的を果たせていません。

本記事では、こうした「定期取得→時系列蓄積」を自動化する定番パターンを、解説用に無料で使える一般的な公開APIを想定した構成で整理します。実運用のエンドポイントやAPIキーは含めず、すべてダミー値で説明します。

余談ですが、最初のうちは「1日に数回チェックすれば十分だろう」と高をくくっていました。ところが実際にやってみると、変化のタイミングは自分の都合とはまったく無関係にやってきます。結局、気になったときに毎回スマホでブラウザを開いて確認する癖がついてしまい、これはこれで別の意味で時間を取られていました。

完成形

時間主導トリガーで定期的に起動し、外部APIから最新データを取得して、スプレッドシートの末尾に1行ずつ追記していく、シンプルな時系列蓄積の仕組みです。

シートを開けば、いつ・どんな値だったかが1行ずつ並んでおり、後から見返すのも、グラフ化するのも簡単になりました。日々の変化をぼんやり眺めるだけでも、手作業で確認していた頃には気づかなかった傾向がふと見えてくることがあります。

AIとの作り方

最初にAIへ「外部APIからデータを取ってシートに書き込む処理を書いて」とだけ頼んだところ、たしかに動くコードは出てきたのですが、レスポンスのどの階層に必要な値があるかをAIが勝手に推測しており、実際のAPIのレスポンス構造とは微妙にずれていました。結果として、実行してみるまでエラーの有無が分からない状態でした。

そこで、実際のAPIレスポンス例(ダミー値に置き換えたJSON)をそのままプロンプトに貼り、「この構造から日時と値だけを抜き出してシートに追記する関数を書いて」と依頼するようにしました。レスポンス例を見せてから頼むと、パース処理の勘違いがほとんどなくなり、一発で動くコードが出てくる確率が明らかに上がりました。

もう1つ工夫したのは、「取得件数が想定と違うときに気づけるようにしたい」という要望を、実装より先に伝えたことです。先に「異常検知の仕様」を言葉で決めてから実装をAIに任せると、あとから検知処理を継ぎ足すよりも綺麗にまとまりました。

サンプルコード

const SHEET_NAME = "records"; // ダミーのシート名
const API_ENDPOINT = "https://example-public-api.test/data"; // 解説用のダミーエンドポイント
const NOTIFY_EMAIL = "[e-mail]";

function fetchAndAppend() {
  const response = UrlFetchApp.fetch(API_ENDPOINT);
  const json = JSON.parse(response.getContentText());

  if (json.value === undefined || json.value === null) {
    notifyAnomaly("取得した値が空でした");
    return;
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  sheet.appendRow([new Date(), json.value]); // 取得時刻と値を追記

  checkRecentCount(sheet);
}

function checkRecentCount(sheet) {
  // 直近24時間分の行数を数え、想定より極端に少ない場合だけ通知する
  const data = sheet.getDataRange().getValues();
  const oneDayAgo = new Date(Date.now() - 24 * 60 * 60 * 1000);
  const recentCount = data.filter(row => row[0] instanceof Date && row[0] > oneDayAgo).length;

  if (recentCount < 20) { // 1時間おき想定で24件のはずが大きく下回る場合
    notifyAnomaly(`直近24時間の記録が${recentCount}件しかありません`);
  }
}

function notifyAnomaly(message) {
  GmailApp.sendEmail(NOTIFY_EMAIL, "[要確認] データ取得の異常", message);
}

function setHourlyTrigger() {
  ScriptApp.newTrigger('fetchAndAppend')
    .timeBased()
    .everyHours(1)
    .create();
}

checkRecentCountは簡易的なものですが、「想定件数から大きく外れたら通知する」というだけで、記録が静かに止まっていることに気づけないという事態はかなり防げます。

なお、checkRecentCountのしきい値や集計期間はサンプル用の一例です。対象のAPIの更新頻度に応じて、24時間ではなく1週間単位で見るなど、実際の運用に合わせて調整してください。

ハマりどころ

外部APIが一時的にレート制限にかかったり、メンテナンスで応答しなかったりすると、そのままではエラーで処理が止まってしまいます。最初はこのエラーに気づかないまま数日間放置してしまい、後から見返したときにデータが丸ごと欠けている期間があるのに気づいて青ざめたことがあります。

1回の取得失敗が致命的にならないよう、取得処理は指数バックオフ付きのラッパー関数(前回紹介したもの)を通して呼び出すようにしました。それでも失敗が続く場合は諦めて通知だけする、という割り切りも必要です。全部を自動で解決しようとすると、リトライ処理自体が複雑になりすぎて、かえってバグの温床になります。

もう1つ、checkRecentCountのしきい値(今回は20件)を最初から厳密に決めようとして時間を使いすぎたことがあります。実際には「まず適当な値で走らせてみて、誤検知が多ければ緩める」くらいの気軽さで始めた方が早く実用段階に進めました。

もう1つ、地味ながら手間取ったのがタイムゾーンのずれです。GASのnew Date()はスクリプトのタイムゾーン設定に依存するため、プロジェクトの設定を確認せずに使うと、意図した時刻と数時間ずれて記録されることがあります。スクリプトのプロパティで明示的にタイムゾーンを設定し、記録された日時を最初の数件だけ手動で見比べて確認する、という地味な検証作業を挟んでからでないと、後になって「あれ、この時刻おかしいな」と気づいて過去分を全部疑い直す羽目になります。

まとめ + 次回予告

「決まった間隔でデータを取りに行き、シートに積み上げる」という定番パターンを一度作っておくと、対象のAPIを差し替えるだけで様々な用途に転用できます。ただし、取得できて当たり前だと思っていると、静かに止まっていることに気づかないまま何日も過ぎてしまうので、簡単でいいので異常検知だけは早い段階で組み込んでおくのがおすすめです。

また、こうした定期取得の仕組みは一度動き出すと安心してしまいがちですが、「動いていること」自体を定期的に目視で確認する習慣も合わせて持っておくと、より安心して運用できます。今回のようなパターン回は気負わず、定番として1つ押さえておくくらいの気持ちで十分です。

次回は、シートに蓄積したデータがさらに増えてきたときの選択肢として、GAS×BigQueryの入門を紹介します。

参考・関連リンク

• GAS UrlFetchApp 公式リファレンス: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
• GAS ScriptApp (トリガー) 公式リファレンス: https://developers.google.com/apps-script/reference/script/script-app

前回は「LLMに構造化JSONを返させる実務テクニック」について、次回は「GAS×BigQuery入門: シートの限界を超える」について書く予定です。

• note(作った経緯・地味な失敗談はこちら): https://note.com/kar8