【Googleスプレッドシート】OAuth接続のCSV取込!認証付きURLの扱い

認証が必要なURLからCSVデータをスプレッドシートに取り込もうとしたとき、IMPORTRANGEではエラーになり困った経験はありませんか。OAuth認証が設定されたWebサービスからデータを取得するには、通常の関数だけでは対応できません。この記事では、Google Apps Scriptを使って認証付きURLからCSVをインポートする具体的な手順を解説します。アクセストークンの取得方法からシートへの書き込みまで、一連の流れをわかりやすく説明しますので、ぜひ最後までご覧ください。

認証付きURLが直接取り込めない理由

スプレッドシートのIMPORTRANGE関数は、公開されているデータのみを取得するように設計されています。そのため、OAuth認証が必要なAPIエンドポイントや認証付きのWebページからCSVファイルを直接読み込むことはできません。セキュリティ上の理由から、スプレッドシートはユーザーが明示的に認証情報を提供しない限り、外部の認証が必要なリソースにアクセスしません。また、QUERYやIMPORTDATAなどの他のインポート系関数も同様で、認証付きURLには対応していません。そのため、認証が必要なCSVデータを取り込むには、Google Apps Scriptを使用してHTTPリクエストを自分で構築する必要があります。

Apps ScriptでOAuth認証付きCSVを取得する手順

この手順では、OAuth2.0のアクセストークンを使用してAPIからCSVデータを取得し、スプレッドシートに書き込む方法を説明します。事前にAPIのクライアントIDとクライアントシークレットを取得しておいてください。ここでは例として、架空の「データAPI」からCSVを取得するケースを想定します。

手順1: Apps Scriptエディターを開く

1. スクリプトエディターを起動する
   スプレッドシートを開き、メニューから「拡張機能」→「Apps Script」を選択します。新しいタブでスクリプトエディターが開きます。
2. プロジェクト名を設定する
   左上の「無題のプロジェクト」をクリックし、わかりやすい名前(例「OAuthCSV取り込み」)を付けておきます。

手順2: OAuth2.0ライブラリを追加する

1. ライブラリを追加する
   スクリプトエディターの左側にある「ライブラリ」の「+」アイコンをクリックします。
2. OAuth2ライブラリのスクリプトIDを入力する
   「スクリプトID」フィールドに「1B7FSrk5Zi6L1rSXXTDyDEpsG3O_3G6tG3PP3n9E3G7kE1G3RgG3G3G3G3G3G3G3G3G3」と入力します。実際のスクリプトIDはGoogleが提供するOAuth2ライブラリのものを使用してください。バージョンは最新(例「43」)を選択し、「追加」をクリックします。
3. ライブラリをコード内で使用可能にする
   スクリプトエディターでコードを書き始める前に、スコープを適切に設定します。メニューから「ファイル」→「プロジェクトのプロパティ」を開き、「スコープ」タブで必要なAPIスコープを追加しておきます。

手順3: OAuth認証とCSV取得のコードを記述する

1. スクリプトエディターにコードを貼り付ける
   以下のコードをコピーしてエディターに貼り付けます。APIのエンドポイントURLと認証情報は実際のものに置き換えてください。

// OAuth2.0設定
var CLIENT_ID = 'YOUR_CLIENT_ID';
var CLIENT_SECRET = 'YOUR_CLIENT_SECRET';
var TOKEN_URL = 'https://api.example.com/oauth/token';
var CSV_URL = 'https://api.example.com/data.csv';

function getService() {
  return OAuth2.createService('example')
    .setAuthorizationBaseUrl('https://api.example.com/oauth/authorize')
    .setTokenUrl(TOKEN_URL)
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('read:data');
}

function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('認証が完了しました。タブを閉じてスプレッドシートに戻ってください。');
  } else {
    return HtmlService.createHtmlOutput('認証に失敗しました。');
  }
}

function fetchCSV() {
  var service = getService();
  if (!service.hasAccess()) {
    // 認証URLを表示
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('次のURLにアクセスして認証してください: ' + authorizationUrl);
    // 認証を促すUIを表示する場合はHtmlServiceを使用
    var html = HtmlService.createHtmlOutput('<a href="' + authorizationUrl + '" target="_blank">認証する</a>');
    SpreadsheetApp.getUi().showModalDialog(html, '認証が必要です');
    return;
  }
  
  // アクセストークンを使用してCSVを取得
  var options = {
    headers: {
      'Authorization': 'Bearer ' + service.getAccessToken()
    },
    muteHttpExceptions: true
  };
  
  var response = UrlFetchApp.fetch(CSV_URL, options);
  var csvText = response.getContentText();
  
  // CSVをパースしてシートに書き込む
  var data = Utilities.parseCsv(csvText);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

2. コードを保存する
   Ctrl+S(またはCmd+S)で保存します。初回保存時にプロジェクト名を求められますので、適宜入力します。
3. 関数を実行する準備
   実行する関数「fetchCSV」を選択し、再生ボタン(▶)をクリックします。初回実行時には承認が必要です。ポップアップに従って権限を付与してください。

手順4: 認証フローを実行する

1. 認証URLにアクセスする
   スクリプトを実行すると、認証URLが表示されます。そのURLをブラウザで開き、APIへのアクセスを許可します。リダイレクト先で認証コードが返ってくるので、スクリプトに戻るか自動的に処理されます。
2. 認証が完了したら再度fetchCSVを実行する
   認証後、もう一度fetchCSV関数を実行すると、アクセストークンが有効になりCSVがシートに書き込まれます。

OAuth接続のCSV取込で注意すべきポイント

アクセストークンの有効期限切れ

OAuth2.0のアクセストークンには有効期限があります。通常は1時間程度です。トークンが期限切れになるとリクエストが失敗します。そのため、スクリプト内でリフレッシュトークンを使用して自動的にトークンを更新する処理を実装する必要があります。上のコードではOAuth2ライブラリが自動でリフレッシュを処理してくれますが、ライブラリを使用しない場合は自分でリフレッシュフローを実装してください。

レート制限への対応

APIによっては1分間あたりのリクエスト数に制限があります。大量のデータを取得する場合は、リクエスト間に待機時間を入れるなどの対策が必要です。Apps ScriptのUtilities.sleep()メソッドを使用してミリ秒単位で待機できます。また、エラーが返ってきた場合は指数バックオフでリトライする実装をお勧めします。

CSVの文字コードと改行コード

取得したCSVデータの文字コードがUTF-8でない場合、文字化けすることがあります。その場合はresponse.getContentText(‘Shift_JIS’)のように文字コードを指定して読み込んでください。また、改行コードがCRLFやLFなど異なる場合もありますが、Utilities.parseCsv()は自動的に認識します。

IMPORTRANGEとApps Scriptの比較

まとめ

OAuth認証付きURLからCSVデータをスプレッドシートに取り込むには、IMPORTRANGEではなくGoogle Apps Scriptを使用します。この記事ではOAuth2.0ライブラリを利用した認証フローとUrlFetchAppによるCSV取得方法を紹介しました。アクセストークンの管理やエラーハンドリングを適切に行うことで、安定したデータ取り込みが可能です。また、定期的にデータを更新したい場合は、スクリプトに時間主導型のトリガーを設定するとよいでしょう。ぜひ実際のAPIに合わせてコードをカスタマイズし、日々の業務に活用してみてください。