📌

Google Sheet から Microsoft Entra ID で認証して Microsoft Graph API を叩く

2024/05/12に公開

GAS

OAuth 2

Microsoft Entra

tech

TL;DR

Google Sheet から Azure のリソースを叩きたい
Google Apps Script で OAuth2 の流れを書く
とりあえず、Microsoft Entra ID で認証して、Microsoft Graph API でユーザー情報を取得して、その内容を Google Sheet に書き込むというのができた

はじめに

とあるもちべーしょんがありまして、Google Sheet から Microsoft Entra ID 認証がやりたかったんですね。
まぁ、結局 OAuth2 でやるだけなのでもう何番煎じだよ、っていういつもの流れではあるんですが、Google Apps Script (GAS) での実装例ということで見ていただければと。

Microsoft Entra ID でアプリケーション登録する

まずは、Microsoft Entra ID でアプリケーションを登録します。
Redirect URI は後で直せるので、とりあえず空のまま、適当な名前で保存しておきます。

作成後、Certificates & secrets から secret を作成しておきます。
実際の secret の値は、しばらくは見えていますが、そのうち見えなくなってしまうので、メモしておきます。

Google Sheet を作る

とりあえず新しい Sheet を作ります。

Google Apps Script を書く

Sheet のメニューから Extensions > Apps Script を選択して、エディターを開きます。
以下のような手順で仕上げていきます。

Script Properties の設定
OAuth2 ライブラリを追加
3 つのファイルを作成

忘れないうちに、GAS の Script ID を取得しておきます。
エディターの左のメニューから、Project Settings に行くと、IDs という内容で書かれています。
こちらの内容をもとに、Microsoft Entra ID のアプリケーション登録で Redirect URI を設定しておきます。
内容は https://script.google.com/macros/d/<SCRIPT_ID>/usercallback みたいな感じです。

Script Properties の設定

要は環境変数みたいなやつを設定します。
左のメニューから Project Settings の中の一番下の方に Script Properties というのがあります。
ここでは、3 つの Script Properties を追加します。

CLIENT_ID: Microsoft Entra ID のアプリケーション登録で取得した Client ID
CLIENT_SECRET: Microsoft Entra ID のアプリケーション登録で取得した Client Secret
TENANT_ID: Microsoft Entra ID のテナント ID

GAS の library を追加

いろいろ試行錯誤しているうちに、googleworkspace/apps-script-oauth2 を見つけたので、こちらで楽をさせていただいています。
使い方も README.md に書かれているので非常に簡単でした、ありがとうございます。

追加の仕方としては、エディター上で、Libraries の右の + を押して、Script ID として 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF を入力します。
Version とかは適当に最新版でいいかと思うので追加すればそれだけで使えるようになります。

1 つめのファイル: めいんっぽいやつ

最初に作成されていたであろう Code.gs に、メインっぽい内容を書きます。
大体、以下のような内容が書いてあります。

Sheet に「Custom Menu」というメニューを追加し、そこに「Login with Microsoft Entra ID」と「Logoff and reset access token」、「Add Authenticated User Data」という項目を追加
「Login with Microsoft Entra ID」を選択したあとの、OAuth2 の処理

Code.gs

code.gs

// Configuration for Microsoft Entra ID
var CLIENT_ID = PropertiesService.getScriptProperties().getProperty('CLIENT_ID');
var CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('CLIENT_SECRET');
var SCRIPT_ID = ScriptApp.getScriptId();;
var REDIRECT_URI = `https://script.google.com/macros/d/${SCRIPT_ID}/usercallback`;
var TENANT_ID = PropertiesService.getScriptProperties().getProperty('TENANT_ID');
var AUTH_URL = `https://login.microsoftonline.com/${TENANT_ID}/oauth2/v2.0/authorize`;
var TOKEN_URL = `https://login.microsoftonline.com/${TENANT_ID}/oauth2/v2.0/token`;

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Login with Microsoft Entra ID', 'login')
      .addItem('Logoff and reset access token', 'removeAccessToken')
      .addItem('Add Authenticated User Data', 'addAuthenticatedUserData')
      .addToUi();
}

function login() {
  var service = new getMeidService_();
  var authUrl = service.getAuthorizationUrl();
  var html = `
    <html>
      <head>
        <script>
          function openLinkAndCloseDialog(url) {
            window.open(url, '_blank');
            google.script.host.close();
          }
        </script>
      </head>
      <body>
        <p><a href="#" onclick="openLinkAndCloseDialog('${authUrl}'); return false;">Login with Microsoft Entra ID</a></p>
      </body>
    </html>
  `;

  var htmlOutput = HtmlService.createHtmlOutput(html)
                              .setWidth(400)
                              .setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Microsoft Entra ID Login');
}

function getMeidService_() {
  // Create Microsoft Entra ID OAuth2 service with a unique service name.
  return OAuth2.createService('ME-ID')

      // Set the Microsoft Entra ID OAuth endpoint URLs.
      .setAuthorizationBaseUrl(AUTH_URL)
      .setTokenUrl(TOKEN_URL)

      // Set the client ID and secret from Microsoft Entra ID application registration.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      // Set the name of the callback function that handles the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens will be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      // Set the scopes as required by Microsoft Entra ID separated by spaces.
      // Example scope below for user.read and offline_access to ensure refresh tokens are received.
      .setScope('https://graph.microsoft.com/User.Read openid email')

      // Additional Azure-specific OAuth2 parameters.

      // Force the user to consent again.
      .setParam('prompt', 'consent')

      // Ensures the user can select their account again in multi-account scenarios.
      .setParam('prompt', 'select_account')

      // Requests offline access to receive refresh tokens.
      .setParam('access_type', 'offline');
}

function authCallback(request) {
  var code = request.parameter.code;
  var options = {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: {
      client_id: CLIENT_ID,
      scope: 'https://graph.microsoft.com/User.Read openid email',
      code: code,
      redirect_uri: REDIRECT_URI,
      grant_type: 'authorization_code',
      client_secret: CLIENT_SECRET
    }
  };
  
  var response = UrlFetchApp.fetch(TOKEN_URL, options);
  var tokens = JSON.parse(response.getContentText());
  setAccessToken(tokens);
  
  Logger.log(tokens); // Log token information
  return HtmlService.createHtmlOutput('Authentication successful! You can close this tab.');
}

2 つめのファイル: ユーザープロファイル扱うやつ

次に、エディターの左上にある + ボタンを押して、新しいスクリプトファイルを作成します。
ファイル名は UserProfile.gs とします。

こちらは以下のような処理が書かれています。

Microsoft Entra ID で取得したアクセストークンを使い、Microsoft Graph API でユーザー情報を取得
取得したユーザー情報を Sheet に書き込む

UserProfile.gs

function getUserProfile(token) {
  var url = "https://graph.microsoft.com/v1.0/me";
  var options = {
    'method': 'get',
    'headers': {
      'Authorization': 'Bearer ' + token
    },
    'muteHttpExceptions': true
  };

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() === 200) {
    var userData = JSON.parse(response.getContentText());
    return userData;
  } else {
    Logger.log('Failed to fetch user data. Response Code: ' + response.getResponseCode());
    return null;
  }
}

function writeToSheet(data) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

  // Add a title row if necessary
  if (lastRow === 0) {
    sheet.appendRow(['Display Name', 'Mail', 'Job Title']);
  }

  // Add user data
  sheet.appendRow([data.displayName, data.mail, data.jobTitle]);
}

function addAuthenticatedUserData() {
  var token = getAccessToken();
  var userData = getUserProfile(token);
  
  if (userData) {
    writeToSheet(userData);
  } else {
    Logger.log('No data to write.');
  }
}

3 つめのファイル: アクセストークン扱うやつ

再度、+ ボタンを押して、新しいスクリプトファイルを作成します。
ファイル名は AccessToken.gs とします。
こちらでは以下のような処理を書いています。

アクセストークンをキャッシュに保存
キャッシュからアクセストークンを取得
(debug 用) アクセストークンを削除

AccessToken.gs

function setAccessToken(tokens) {
  var cache = CacheService.getUserCache();
  var expiresIn = parseInt(tokens.expires_in);
  var accessToken = tokens.access_token;

  // Set the cache expiration based on the expiresIn provided in seconds.
  cache.put('ACCESS_TOKEN', accessToken, expiresIn);
}

function getAccessToken() {
  var cache = CacheService.getUserCache();
  var accessToken = cache.get('ACCESS_TOKEN');

  // If no access token is found in the cache
  if (!accessToken) {
    Logger.log('No access token in cache. Need re-authentication.');
    var ui = SpreadsheetApp.getUi();
    // Use OK_CANCEL buttons to give the user a choice
    var response = ui.alert('Authentication Required', 'Your session has expired. Please re-authenticate to continue.', ui.ButtonSet.OK_CANCEL);

    if (response == ui.Button.OK) {
      // If the user clicks OK, start the re-authentication process by calling the login function
      login();
    } else if (response == ui.Button.CANCEL) {
      // If the user clicks Cancel, log this choice and do nothing or handle differently
      Logger.log('User chose to cancel the re-authentication process.');
      // Optionally, return null or handle as needed
      return null;
    } else {
      Logger.log(response);
      Logger.log('The user clicked the close button in the dialog\'s title bar.');
      return null;
    }
  }
  
  return accessToken;
}

function removeAccessToken() {
  var cache = CacheService.getUserCache();
  cache.remove('ACCESS_TOKEN');
  Logger.log('Access token has been removed from cache.');

  var service = getMeidService_();
  service.reset();
}

ということで試してみる

準備が整いましたので、最初に用意していた Sheet を一度リロードします。
ちょっと待つと、File / Edit / View などの一番上のメニューの右に、「Custom Menu」というメニューが追加されます。

Microsoft Entra ID でログイン

最初は素直に、「Login with Microsoft Entra ID」を選択します。
CSS も何も飾り気のない、シンプルなダイアログが表示されるので、「Login with Microsoft Entra ID」をクリックします。
別ウィンドウが開いて、Microsoft Entra ID のログイン画面が表示されるので、ログインします。
初回ログイン時は、アプリケーションの権限を求められるので、内容を確認して、許可します。
リダイレクトされたのち、「Authentication successful! You can close this tab.」と表示されていれば、ログイン成功です。

Microsoft Graph API を試す

では、ログインしたその権限を使って、何か情報を取得してみます。
Custom Menu から「Add Authenticated User Data」をクリックすると、Microsoft Graph API で取得したユーザー情報が Sheet に書き込まれます。
シンプルな内容ではありますが、Microsoft Entra ID を使って、ユーザーによる認証を行い、その権限を使って Microsoft Graph API でユーザー情報を取得し、それを Sheet に書き込むという流れを確認できました。

アクセストークンを削除してみる

また、debug 用に「Logoff and reset access token」を用意しているので、クリックしてみます。
画面上の大きな変化はありませんが、キャッシュに保存されているアクセストークンが削除されています。

アクセストークンがない状況で API を叩こうとしてみる

本来であれば、「Login with Microsoft Entra ID」を実行してから「Add Authenticated User Data」を実行するはずなのですが、アクセストークンが消えているこの状況から直接「Add Authenticated User Data」をクリックするとどうなるでしょうか。
「Authentication Required」というダイアログが表示され、再度ログインを求められたでしょうか。

ちょっと込み入った話

Google Apps Script 内で Logger.log を使えますが、そのログは GAS のエディターの左のメニューから、Executions という画面に移動すると確認ができます。
エディターとは別のウィンドウで開いておくと、実行された内容が自動的に更新され、一番上に新しいログが表示されるのでとても分かりやすいです。

Access token の管理には CacheService を使っています。
getUserCache() を使えばユーザーごとのキャッシュを取得できるので、アクセストークンを保存するのに便利です。

まとめ

ということで、Google Sheets 上で Microsoft Entra ID 認証を使ったほにゃららのため、Google Apps Script で OAuth2 による認証を実装してみました。
ちょっと画面遷移的には分かりづらい部分もあるかもしれませんが、さんぷるということでは十分なんじゃないかと満足しています。
Google Sheet をフロントに使うってことで、わかりやすい UI をもった、Azure のリソースを叩くアプリ、的なものとして使えるのかなと思っています。

参考

googleworkspace/apps-script-oauth2: An OAuth2 library for Google Apps Script.
途中でこれに気づいたのでだいぶ楽になりました、まぁそりゃあるよな、という話ではあるんですが。README.md が親切なのでほんとなにも困らなかったです。

CacheService | Apps Script | Google Developers