FlaskでWebAPIを作る・利用する➁

2023年11月20日 01:14

このたび、ノンプログラマーのためのスキルアップ研究会(以下、ノンプロ研)のご支援のもとで「ラズパイとFlaskでつくる！Webアプリ開発入門」という技術同人誌を出させていただきました。

購入はこちらから！

この記事では、FlaskでWebAPIを作る・利用する①の続きとして、①で作ったWebAPIをExcelVBAで利用する方法を書いていきます。

WebAPIの再掲

①で書いたWebAPIのコードを再掲します。
このWebAPIでは西暦から、その年に生まれた人の現在の年齢と干支を算出します。
詳細な解説については、前回の記事をご覧ください。

from flask import Flask, Response
import json
import datetime

app = Flask(__name__)

@app.route('/', methods=['GET'])
def year_change_get():
    # リクエストされたクエリパラメータからyearを取得し、整数型に変換
    year = int(request.args.get('year', datetime.date.today().year))

    # 現在の年齢を計算
    age = f"{datetime.date.today().year - year}歳"
    
    # 干支の特定
    eto = ["申年","酉年","戌年","亥年","子年","丑年","寅年","卯年","辰年","巳年","午年","未年"]
    eto = eto[year % 12]
    
    # JSONデータへの変換
    data = {
        '年齢と干支': f"{age}({eto})",
    }
    response = Response(response=json.dumps(data, ensure_ascii=False), status=200, mimetype='application/json')
    return response

if __name__ == '__main__':
    app.run(debug=True)

他のPCから、ExcelVBAでWebAPIを利用する

VBA-JSONを導入する

このWebAPIではJSON形式でデータのリクエスト・レスポンスを行いますが、ExcelVBAでJSONを扱うにはVBA-JSONを導入する必要があります。
導入にあたっては、こちらのサイト様の解説が分かりやすいです。

WebAPIを利用するExcel VBAコードを書く

上記のサイト様でも書かれていますが、まずはJsonConveterをインポートします。

続いて、標準モジュールを追加して以下のコードを書きます。

Sub YearChangeSub()
    
    ' WebAPIにリクエストを行う上での変数
    Dim httpReq As XMLHTTP60
    Dim WebURL As String
    Dim responseText As String
    Dim requestJson As Object
    
    ' インスタンスを作成
    Set httpReq = New XMLHTTP60
    
    ' メソッドとURLを指定してWeb APIを呼び出し
    Dim year As Integer
    year = 2020
    WebURL = "https://xxxx.ngrok-free.app/?year=" & year
    With httpReq
        .Open "GET", WebURL, False
        .setRequestHeader "Content-Type", "application/json"
        ' ngrokのブラウザ警告をスキップするヘッダーを追加
        .setRequestHeader "ngrok-skip-browser-warning", "pass-ngrok"
        .send
    End With

    ' レスポンスを取得
    responseText = httpReq.responseText
    
    ' レスポンステキストをJSONオブジェクトに変換
    Set requestJson = JsonConverter.ParseJson(responseText)

    ' 結果を表示
    MsgBox responseText
    MsgBox requestJson("年齢と干支")

    ' オブジェクトの解放
    Set httpReq = Nothing
End Sub

また、ユーザー定義関数として利用することも可能です。

Function YearChangeFunc(year As Integer)
    
    ' WebAPIにリクエストを行う上での変数
    Dim httpReq As XMLHTTP60
    Dim WebURL As String
    Dim responseText As String
    Dim requestJson As Object
    
    ' インスタンスを作成
    Set httpReq = New XMLHTTP60
    
    ' メソッドとURLを指定してWeb APIを呼び出し
    WebURL = "https://xxxx.ngrok-free.app/?year=" & year
    With httpReq
        .Open "GET", WebURL, False
        .setRequestHeader "Content-Type", "application/json"
        ' ngrokのブラウザ警告をスキップするヘッダーを追加
        .setRequestHeader "ngrok-skip-browser-warning", "pass-ngrok"
        .send
    End With

    ' レスポンスを取得
    responseText = httpReq.responseText
    
    ' レスポンステキストをJSONオブジェクトに変換
    Set requestJson = JsonConverter.ParseJson(responseText)

    ' 結果を返す
    YearChangeFunc2 = requestJson("年齢と干支")

    ' オブジェクトの解放
    Set httpReq = Nothing
End Function

Excel VBAコードの解説

変数の定義

    ' WebAPIにリクエストを行う上での変数
    Dim httpReq As XMLHTTP60
    Dim WebURL As String
    Dim responseText As String
    Dim requestJson As Object

最初に各変数を定義していきます。
httpReqはXMLHTTP60 オブジェクトで、HTTPリクエストを送信するために使用されます。
WebURLはリクエストを送信するURL、responseTextはWebAPIからのレスポンスされるテキストを格納するための変数で、いずれも文字列型として定義しています。
requestJsonはWebAPIからのレスポンスされるテキストをJSONデータとして格納するために使用します。

WebAPIへのリクエスト送信を行う

    ' インスタンスを作成
    Set httpReq = New XMLHTTP60
    
    ' メソッドとURLを指定してWeb APIを呼び出し
    Dim year As Integer
    year = 2020
    WebURL = "https://xxxx.ngrok-free.app/?year=" & year
    With httpReq
        .Open "GET", WebURL, False
        .setRequestHeader "Content-Type", "application/json"
        ' ngrokのブラウザ警告をスキップするヘッダーを追加
        .setRequestHeader "ngrok-skip-browser-warning", "pass-ngrok"
        .send
    End With

XMLHTTPオブジェクトを使用してWebAPIのURLにリクエストを行うコードを書いていきましょう。Set httpReq = New XMLHTTP60でXMLHTTPオブジェクトをインスタンス化した後にメソッドとURLを指定してWebAPIにリクエストを行います。
引数yearに任意の数値を代入したあとにURLを指定しますが、今回のWebAPIではGETメソッドを使っているのでURLにはクエリパラメータ「?year=」を含め、パラメータの値としてyearを指定しましょう。

続いて、Withステートメントを用いてHTTPリクエストの設定と送信を行います。

.Open "GET", WebURL, False:：GETメソッドを使用して、指定されたURLに対して非同期（False）でリクエストを開きます。
.setRequestHeader "Content-Type", "application/json"：リクエストのコンテンツタイプをJSONとして設定します。
.setRequestHeader "ngrok-skip-browser-warning", "pass-ngrok"：ngrokのブラウザ警告をスキップするためのカスタムヘッダーを設定します。
.send：リクエストを送信します。

ポイントとなるのは.setRequestHeaderで"ngrok-skip-browser-warning"の値を設定していることで、このカスタムヘッダーを設定することでngrok起動後の初回アクセス時の画面をスキップすることができます。

なお、このとき"ngrok-skip-browser-warning"に渡す値は任意です。

WebAPIからのレスポンスを取得し、結果を返す

    ' レスポンスを取得
    responseText = httpReq.responseText
    
    ' レスポンステキストをJSONオブジェクトに変換
    Set requestJson = JsonConverter.ParseJson(responseText)

    ' 結果を表示
    MsgBox responseText
    MsgBox requestJson("年齢と干支")

    ' オブジェクトの解放
    Set httpReq = Nothing

レスポンスを取得してJSONに変換したあと、必要な値を取り出して結果を表示しています。取得したレスポンスは文字型データで返されているので、JsonConverterを使ってJSONオブジェクトに変換し、キーや値を取り出せるようにします。

変換したJSONデータから値を取り出す場合は、requestJson("年齢と干支")のように、取り出したい値が含まれているJSONオブジェクトの引数に、その値に対応するキーを渡すことで行います。

最後に、オブジェクトの解放を行って一連の処理を終了します。

以上が自作したWebAPIをExcel VBAで利用するための一連のコードになります。WebAPIを使うことで、Excel VBA上で複雑な処理を行わせたり、SQLなどの外部データベースと連携させるといったことが容易になるため、いろいろ活用してみて下さい。

この記事が気に入ったらサポートをしてみませんか？