IT Hands-on Lab

小規模組織向けIT環境の構築・運用に役立つ情報を、ハンズオン形式で紹介しています。

トップ > VBA⇔Cloud > 【Excel VBA⇔Cloud】API×ExcelOnlineデータ検索&更新1
最終更新日

【Excel VBA⇔Cloud】API×ExcelOnlineデータ検索&更新1

VBA⇔Cloud アプリ開発

【Excel VBA⇔Cloud】API×ExcelOnlineデータ検索&更新1

Excel VBAでMicrosoft Graph REST APIを使用して、SharePoint Online上にあるExcelファイル(テーブル未使用)のデータ検索・更新を行う方法を紹介します。
  • 操作環境(*)：
    • OS:Windows 11
    • Webブラウザー:Edge
  • 使用プラン：Microsoft 365 Business Premium
    *SharePoint Online上のExcelファイルおよびMicrosoft Graph REST APIにアクセスできる組織アカウントでサインインしていること

なお、この記事では、VBAの使用許可や開発メニュー表示といった初期設定や、VBAエディタの使い方の説明は割愛いたします。

 

 

ExcelOnline操作用APIについて

ExcelOnline(テーブル未使用)のAPIによる操作は、Microsoft Graph REST APIで実施できます。この記事で参考にした公式記事を以下に列挙します。

 

Entra管理C API初期設定

以前の記事で紹介したSharePointリスト×APIの設定と流れは同じです。

 

WebブラウザーよりMicrosoft Entra管理センターにアクセスし、左メニューの[Entra ID＞アプリの登録]を選択し、[新規登録]を選択します。

図表2-1 Entra管理センターのアプリ登録へ

図表2-1 Entra管理センターのアプリ登録へ

 

以下の通り設定し、[登録]を選択します。

  • 名前：(ExcelOnlineのAPIの接続用アプリであることが分かる名前)
  • アカウントの種類：この組織ディレクトリのみに含まれるアカウント
  • リダイレクトURL(選択肢)：Web
  • リダイレクトURL(値)：https://localhost

図表2-2 アプリ登録の実行

図表2-2 アプリ登録の実行

 

登録が完了し、サブメニューの[概要]画面に遷移したら、以下の値を控えておきます。

  • アプリケーション(クライアント)ID
  • ディレクトリ(テナント)ID

図表2-3 登録したアプリの情報確認

図表2-3 登録したアプリの情報確認

 

サブメニューの[APIのアクセス許可]を選択し、[アクセス許可の追加]を選択します。

図表2-4 登録したアプリのアクセス許可設定へ

図表2-4 登録したアプリのアクセス許可設定へ

 

[Microsoft API]タブを選択し、[Microsoft Graph]を選択します。
この記事の最初の方で紹介した使用APIに関する公式記事によると、委任されたアクセス許可のFiles.ReadWriteが必要です。また、今回はSharePoint Online上のExcelファイルを操作するため、SharePointサイトの権限Sites.ReadWrite.Allも必要です。この後設定します。

図表2-5 アクセス許可設定(Graph API選択)

図表2-5 アクセス許可設定(Graph API選択)

 

以下の通り設定し、[アクセス許可の追加]を選択します。

  • アプリケーションに必要なアクセス許可の種類：委任されたアクセス許可
  • アクセス許可：Files＞Files.ReadWrite、Sites＞Sites.ReadWrite.All

図表2-6 アクセス許可設定(許可の種類,対象選択)

図表2-6 アクセス許可設定(許可の種類,対象選択)

 

[xxxxxに管理者の同意を与えます]を選択します。
管理者の同意確認に関するメッセージが表示されたら、[はい]を選択します。

図表2-7 アクセス許可設定(管理者の同意)

図表2-7 アクセス許可設定(管理者の同意)

 

サブメニューの[証明書とシークレット]を選択し、[クライアントシークレット]タブを選択し、[新しいクライアントシークレット]を選択します。

図表2-8 クライアントシークレット作成へ

図表2-8 クライアントシークレット作成へ

 

以下の通り設定し、[追加]を選択します。

  • 説明：(SharePoint APIの接続用シークレットであることが分かる名前)
  • 有効期限：(適宜設定)

元の画面で今追加したクライアントシークレットの値を控えておきます。

図表2-9 クライアントシークレット作成と値の確認

図表2-9 クライアントシークレット作成と値の確認

 

最後に、エンドポイントを確認しておきます。
サブメニューの[概要]を選択し、[エンドポイント]を選択します。
この記事では、[Oauth 2.0 承認エンドポイント (v2)]と[Oauth 2.0トークン認エンドポイント (v2)]を使用します。

図表2-10 エンドポイントの確認

図表2-10 エンドポイントの確認

 

SharePoint Online上のExcelファイル用意

前の記事の工程1で使用したExcelファイルに削除フラグ(del_flg)列を追加し、流用します。削除フラグの追加理由は、データ更新VBAの紹介時に説明します。

図表3-1 データソースExcelファイルの用意

図表3-1 データソースExcelファイルの用意

 

SharePointサイトのドキュメントに用意したExcelファイルをアップロードします。

図表3-2 データソースExcelファイルのアップロード

図表3-2 データソースExcelファイルのアップロード

 

実行用Excelファイル用意

VBAを実行するExcelファイルを用意します(拡張子はxlsm)。

  • Menuシート：各ボタン(ActiveXコントロール)を押すと、SharePoint Online上のExcelファイルに接続し、データの検索や更新を行います。

図表4-1 実行ExcelファイルのMainシート

図表4-1 実行ExcelファイルのMainシート

 

  • Searchシート：Menuシートの[書籍データ検索]ボタンを押すと、SharePoint Online上のExcelファイルのデータ検索結果をこのシートに出力します。

図表4-2 実行ExcelファイルのSearchシート

図表4-2 実行ExcelファイルのSearchシート

 

  • Renewシート：このシートに追加・変更・削除対象(Action列で識別)のデータを入力しておき、Menuシートの[書籍データ反映]ボタンを押すと、SharePoint Online上のExcelファイルに反映されます。

図表4-3 実行ExcelファイルのRenewシート

図表4-3 実行ExcelファイルのRenewシート

 

  • paramシート：工程2で控えたクライアントID・テナントID・クライアントシークレットの値を定義しておきます。サイトID・ファイルID、初回のアクセストークン・リフレッシュトークンは工程6で確認してこのシートに定義します。なお、アクセストークン・リフレッシュトークン・セッションIDは、Mainシートでボタンを押すと更新されます。

図表4-4 実行Excelファイルのparamシート

図表4-4 実行Excelファイルのparamシート

 

  • VBAプロジェクト：Mainシート上にソースを実装します。画面に表示されているのは検索・更新ボタンクリック時にデータ検索・更新の関数を呼び出すソースです。

図表4-5 実行ExcelファイルのVBAプロジェクト

図表4-5 実行ExcelファイルのVBAプロジェクト

 

Web APIのデータのやり取りはJson型というデータフォーマットを使用します。こちらのサイトにVBA用の変換モジュールJsonConverter.basがあるので、ダウンロードします。

図表4-6 GitHubのVBA-JSON変換モジュール入手

図表4-6 GitHubのVBA-JSON変換モジュール入手

 

ダウンロードしたZIPを展開しておきます。VBAのプロジェクトエクスプローラ上で右クリックし、[ファイルのインポート]を選択します。
展開したファイル内のJsonConverter.basを選択します。
VBAのプロジェクトエクスプローラー上に、[標準モジュール>JsonConverter]が表示されます。

図表4-7 VBA-JSON変換モジュールのインポート

図表4-7 VBA-JSON変換モジュールのインポート

 

[ツール＞参照設定]を選択し、[Microsoft Scripting Runtime]のチェックをオンにします。
これが未実施の場合、API呼び出し時のHTTPリクエストオブジェクトの作成でエラーが発生します。

図表4-8 VBAプロジェクトの参照設定

図表4-8 VBAプロジェクトの参照設定

 

 

 

認可コード取得

公式記事を参考に、テキストエディター等で認可コード取得用URLを作成し、WebブラウザーのURL欄に貼り付けて[Enter]キーを押します。

https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/authorize?
client_id={client_id}&response_type=code&redirect_uri=https%3A%2F%2F2.zoppoz.workers.dev%3A443%2Fhttps%2Flocalhost%2F&
response_mode=query&scope=offline_access%20user.read%20Files.ReadWrite&state=12345
*上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
*{tenant_id}と{client_id}は工程2で控えた値です。カッコ{ }は不要です
*redirect_uriとoffline_accessの値の中の記号はコード値に変換しています

図表5-1 認可コード取得(URL作成)

図表5-1 認可コード取得(URL作成)

 

認証を要求されたら、組織のアカウントでサインインします。

図表5-2 認可コード取得(認証)

図表5-2 認可コード取得(認証)

 

Webブラウザー上でエラーが表示されたら、URLをテキストエディター等にペーストします。
URLの・・・code=xxx&state=・・・のxxx部分が認可コードです。次の工程で使いますので、この値を抽出しておきます。

図表5-3 認可コード取得(値の抽出)

図表5-3 認可コード取得(値の抽出)

 

PostmanによるサイトID・ファイルID・トークン取得

API開発ツール「Postman」を使い、認証およびデータ検索・更新時に必要となる値を取得します。Postmanの入手方法は以前の記事を参考にしてください。

 

環境設定

まずは環境を新規作成します。コレクションに直接設定値をベタ打ちもできますが、同じ接続先に色々なリクエストを試したい場合、ここで設定を変数化しておくと便利です。セキュアな情報はタイプをシークレットにします。

  • auth_url：https://login.microsoftonline.com/{{tenant_id}}/oauth2/v2.0/token
    *工程2の最後に確認した[Oauth 2.0トークン認エンドポイント (v2)]の値です
  • tenant_id：工程2で控えたディレクトリ(テナント)ID
  • client_id：工程2で控えたアプリケーション(クライアント)ID
  • client_secret：工程2で控えたクライアントシークレット
  • scope： https://graph.Microsoft.com/.default
  • grant_type：authorization_code
  • callback：https://localhost
    *図表2-2で設定したリダイレクトURLの値です
  • url： https://graph.microsoft.com/v1.0/sites/{{site_id}}
    /drive/items/{{file_id}}/workbook/worksheets/data
    *上記URLは表示の都合で改行が入っていますが、使用時は改行を削除します
    *一番最後のdataはデータソースとなるExcelファイルのシート名です
  • site_id：この工程で取得するサイトID
  • file_id：この工程で取得するファイルID
  • code：工程5で取得した認可コード
*{{xxx}}はxxxという変数として扱われます

図表6-1 Postman Graph APIの環境設定

図表6-1 Postman Graph APIの環境設定

 

サイトID取得

工程3でExcelファイルをアップロードしたサイトのIDを取得します。

  • メソッド：GET
  • URL：https://graph.microsoft.com/v1.0/sites/(ドメイン○○.comの○○部分).sharepoint.com:/sites/(サイト名)
  • Headers＞Content-type：application/json
  • Authorization＞Auth Type：OAuth2.0

図表6-2 Postman サイトID取得(全般設定)

図表6-2 Postman サイトID取得(全般設定)

 

Postmanでは、[Authorization]タブ下部の認証設定を行うことにより、サイトID取得時に必要となるアクセストークン取得も一緒に実施できます。

  • Callback URL：{{callback}}
  • Auth URL：{{auth_url}}/authorize
  • Access Token URL：{{auth_url}}/token
  • Client ID：{{client_id}}
  • Client Secret：{{client_secret}}
  • Scope：{{scope}}
  • Token Request＞grant_type：{{grant_type}}：Request Body
*{{xxx}}はxxxという変数として扱われます

設定が完了したら、[Get New Access Token]を選択します。

図表6-3 Postman サイトID取得(トークン取得設定)

図表6-3 Postman サイトID取得(トークン取得設定)

 

[Authentication complete]と表示されたら、 [Proceed]を選択します。
アクセストークンが表示されたら、[Use Token]を選択します。

図表6-4 Postman サイトID取得(トークン取得)

図表6-4 Postman サイトID取得(トークン取得)

 

[Send]を選択し、画面下部に結果が表示されたら、"id":"xxx"のxxx部分がサイトIDとなります。
この値を図表6-1のPostman環境変数と、図表4-4の実行Excelのparamシートに定義します。

図表6-5 Postman サイトID取得(API実行)

図表6-5 Postman サイトID取得(API実行)

 

ファイルID取得

工程3でサイトにアップロードしたExcelファイルのIDを取得します。サイトのドライブIDを取得した後、この値を使用してファイルIDを取得する流れとなります。

 

工程6-2で追加したサイトID取得の設定で[・・・＞Duplicate]を選択し、ドライブID取得の設定を追加します。

  • メソッド：GET
  • URL： https://graph.microsoft.com/v1.0/sites/{{site_id}}/drive
    *{{xxx}}はxxxという変数として扱われます

サイトID取得時と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。
画面下部に結果が表示されたら、最上位層の"id":"xxx"のxxx部分がドライブIDですので、この値を控えておきます。

図表6-6 Postman ファイルID取得(ドライブID)

図表6-6 Postman ファイルID取得(ドライブID)

 

工程6-2で追加したサイトID取得の設定で[・・・＞Duplicate]を選択し、ファイルID取得の設定を追加します。

  • メソッド：GET
  • URL： https://graph.microsoft.com/v1.0/drives/(前の画面で控えたドライブIDの値)/root:/(工程3のチャネル名)/(工程3のファイル名)
    *URL内のカッコ( )は不要です

サイトID取得時と同様に[Authorization]タブでアクセストークン取得を経て、[Send]を実行します。
画面下部に結果が表示されたら、最上位層"id":"xxx"のxxx部分がファイルIDとなります。
この値を図表6-1のPostman環境変数と、図表4-4の実行Excelのparamシートに定義します。

図表6-7 Postman ファイルID取得(ファイルID)

図表6-7 Postman ファイルID取得(ファイルID)

 

認可コードによるトークン取得

工程6-2,工程6-3では認可コードを使用しませんでしたが、APIでExcelファイルを操作する場合、初回は認可コードによるトークン取得(公式記事)が必要です。

  • メソッド：POST
  • URL：{{auth_url}}/token
  • Headers＞Content-Type：application/x-www-form-urlencoded
  • Body＞
    • client_id：{{client_id}}
    • client_secret：{{client_secret}}
    • scope：https://graph.microsoft.com/.default
    • grant_type：authorization_code
  • code：{{code}}
  • redirect_uri：{{callback}}
*{{xxx}}はxxxという変数として扱われます

図表6-8 Postman 認可コードによるトークン取得(設定)

図表6-8 Postman 認可コードによるトークン取得(設定)

 

画面下部の実行結果内にaccess_tokenとrefresh_tokenの値が表示されます。
データ検索・更新で使用するアクセストークンは有効期間が短いです。VBAで実行する際は、リフレッシュトークンを使ってアクセストークンを更新し(公式記事)、更新されたアクセストークンを使ってデータ検索・更新を実施する形になります。
この後、VBAでの実装イメージ確認のため、このリフレッシュトークンを使ってアクセストークン更新用の設定を試してみます。

図表6-9 Postman 認可コードによるトークン取得(結果確認)

図表6-9 Postman 認可コードによるトークン取得(結果確認)

 

リフレッシュトークンによるトークン更新

以下の通り設定します。

  • メソッド：POST
  • URL：{{auth_url}}/token
  • Headers＞Content-Type：application/x-www-form-urlencoded
  • Body＞
    • 　client_id：{{client_id}}
    • 　client_secret：{{client_secret}}
    • 　scope Files.ReadWrite
    • 　grant_type：refresh_token
    • 　refresh_token：工程6-4で取得したリフレッシュトークン
*{{xxx}}はxxxという変数として扱われます

図表6-10 Postman リフレッシュトークンによるトークン更新(設定)

図表6-10 Postman リフレッシュトークンによるトークン更新(設定)


ここで得られたアクセストークンとリフレッシュトークンを、図表4-4の実行Excelのparamシートに定義します。

図表6-11 Postmanリフレッシュトークンによるトークン更新(結果確認)

図表6-11 Postmanリフレッシュトークンによるトークン更新(結果確認)

 

次の記事(ソースの紹介)に続きます。

 

 

当ブログ内の連載記事

elmgrn.hatenablog.com

 

当ブログ内の関連記事

elmgrn.hatenablog.com

elmgrn.hatenablog.com