Excel VBA初めて触った初心者がVBAでREST APIにPOSTする方法について書いてみた

Posted at 2024-05-26

Excel VBA (Visual Basic Application)でREST APIにPOSTする方法を書きます(2024/5/26時点情報)。本記事ではGoogleの生成AI GeminiのREST APIを利用しました。

検証した環境

PC: XPS 13 9370
OS: windows 11 (23H2 OS内部バージョン 22631.3593)。Excel for Macでは本記事に記載の内容だけではエラーが出て動かないのでご注意ください。
Excel：Microsoft Office Home and Business 2016

環境設定

開発タブの有効化

Excel VBAを起動できるよう以下の手順でエクセルの「開発」タブを有効化します。

エクセルを起動
左上の「ファイル」タブを押下
左下の「オプション」を押下
ポップアップ画面の「リボンのユーザー設定」を押下
「開発」にチェックを入れ（デフォルト設定ではチェックされていない）、右下の「OK」を押下

これでエクセルに「開発タブ」が追加されます。

Gemini API KEYを取得

以下のGoogle AI for Developersのページから「Google AI StudioでAPIキーを取得する」を押下し、APIキーを発行します。
https://ai.google.dev/gemini-api

作成後、以下のページで作成したAPIキーのプランが無料であることを念のため確認しておきましょう。
https://aistudio.google.com/app/apikey

利用料金については下記に記載があります。
https://ai.google.dev/pricing

Gemini REST APIにPOSTする

エクセルの準備

エクセルのシートを二つ作成します。

Sheet1

プロンプトを記載しておくカラムと、生成結果を記入するカラムを作成し、右側にテキスト生成の実行ボタンとなる図形を設置しておきます。（図形は何でも構いません）

プロンプトはなんでもいいですが、例として”Gemini 1.5 Flashの機能について100文字程度で教えてください。"にしておきます。

Sheet2

二つ目のシートのB1セルに発行したGeminiのAPIKEYを記入します。

ファイル保存

Excelマクロ有効ブック(.xlsm) の形式でファイルを保存します。

VBAの設定

VBAの参照設定

Excel VBAでDictionary型を使用するためにMicrosoft Scripting Runtimeライブラリへの参照設定が必要のため、このライブラリを参照するよう設定します。

エクセルの「開発」タブを押下
「Visual Basic」を押下し、VBAエディタを起動
VBAエディタの「ツール」タブの参照設定を押下
参照可能なライブラリファイルの「Microsoft Scripting Runtime」にチェックを入れ、「OK」を押下

JsonConverter.basのインポート

Jsonデータの処理を簡単に行えるよう、Json解析用のライブラリをVBAプロジェクトにインポートします。

JsonConverter.basのダウンロード：下記のリンクからJsonConverter.basのみダウンロードします
https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas
VBAエディタを開く: Excelの「開発」タブをクリックし、「Visual Basic」をクリックしてVBAエディタを開く。
JsonConverter.basをインポート:「ファイル」 > 「ファイルのインポート」を選択し、ダウンロードしたJsonConverter.basファイルを選択してインポートします。成功すれば下記のようにプロジェクト欄の標準モジュールにJsonConverterが表示されます。

Gemini REST APIへPOSTするVBAコード

以下のコードをVBAエディタに貼り付けます。
やっていることとしてはSheet1のA2セルのプロンプト文を取得し、Gemini 1.5 flashのREST APIにPOSTして結果を取得したあとにJsonを解析して、生成結果のみを取り出し、Sheet1のB2セルに書き込んでいます。

今回のサンプルコードではgemini-1.5-flashを利用しました。Geminiの各モデル詳細は下記に記載されています。
https://ai.google.dev/gemini-api/docs/models/gemini

' JsonConverterの初期設定
Const JsonLib As String = "JsonConverter"

Sub geminiMain()
    'gemini REST APIにPOSTし、結果を書き込むSubプロシージャ'
    Dim prompt As String
    Dim llmRes As String
    'sheet1のA2セルに記載のプロンプト文を取得'
    prompt = Worksheets("sheet1").Range("A2").Value
    'gemini REST APIにPOST'
    llmRes = geminiPost(prompt)
    'プロンプト文の右隣のセル（B2セル）に生成されたテキストを書き込み'
    ThisWorkbook.Sheets("Sheet1").Range("B2").Value = llmRes
    
End Sub

Function geminiPost(prompt As String)

    'gemini REST APIにPOSTするFunction'
    Dim url As String
    Dim requrl As String
    Dim http As Object
    Dim response As String
    Dim postData As String
    Dim jsonResponse As Object
    Dim parsedData As String
    Dim apiKey As String
    
    'APIKEY取得'
    apiKey = Worksheets("Sheet2").Range("B1").Value
    
    ' APIエンドポイントURLを設定
    url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent?key="
    requrl = url & apiKey
    ' 送信するデータ（例：JSON形式）
    postData = "{""contents"": [{""parts"":[{""text"": """ + prompt + """}]}]}"
    Debug.Print postData
    ' MSXML2.XMLHTTPオブジェクトを作成
    Set http = CreateObject("MSXML2.XMLHTTP")

    ' HTTPリクエストを開く
    http.Open "POST", requrl, False

    ' ヘッダーを追加
    http.setRequestHeader "Content-Type", "application/json"
    ' 追加のヘッダーが必要であればここに追加

    ' リクエストを送信
    http.Send postData

    ' レスポンスを取得
    response = http.responseText
    
    ' レスポンスの表示（デバッグ用）
    Debug.Print "Response: " & response
    
    ' JsonConverterを使ってレスポンスを解析
    Set jsonResponse = JsonConverter.ParseJson(response)
    
    ' 必要なデータを取り出す
    If Not jsonResponse Is Nothing Then
        If jsonResponse.Exists("candidates") Then
            parsedData = jsonResponse("candidates")(1)("content")("parts")(1)("text")
        Else
            parsedData = jsonResponse("error")("message")
        End If
    Else
        parsedData = "No valid JSON response"
    End If
    'Geminiの生成結果テキストもしくはエラーメッセージを返却"
    geminiPost = parsedData

End Function

VBAエディタに貼り付けると下記のような感じになります。

"必要なデータの取り出し"部分のコードについて

上記コードの「必要なデータを取り出す」の部分では、以下のAPIリファレンスを参考に、生成されたテキストのみを取り出し、parsedDataに格納しています。

Geminiのコンテンツ生成のAPIリファレンス:
https://ai.google.dev/api/rest/v1beta/models/generateContent
レスポンス形式: https://ai.google.dev/api/rest/v1/GenerateContentResponse

Geminiのレスポンス例は以下です。

{
  "candidates": [
    {
      "content": {
        "parts": [
          {
            "text": "ここにGeminiが生成したテキストが格納される"
          }
        ],
        "role": "model"
      },
      "finishReason": "STOP",
      "index": 0,
      "safetyRatings": [
        {
          "category": "HARM_CATEGORY_SEXUALLY_EXPLICIT",
          "probability": "NEGLIGIBLE"
        },
        {
          "category": "HARM_CATEGORY_HATE_SPEECH",
          "probability": "NEGLIGIBLE"
        },
        {
          "category": "HARM_CATEGORY_HARASSMENT",
          "probability": "NEGLIGIBLE"
        },
        {
          "category": "HARM_CATEGORY_DANGEROUS_CONTENT",
          "probability": "NEGLIGIBLE"
        }
      ]
    }
  ],
  "usageMetadata": {
    "promptTokenCount": 323,
    "candidatesTokenCount": 1,
    "totalTokenCount": 324
  }
}

VBAのコードではこの中で、"text"の値を取り出しています。注意点としてはVBAの場合はリストの1番目のインデックスが1なので