【pleasanter/VBA】エクセルからプリザンターのデータを取得する

所属の会社では、使えるプログラム言語はVBAのみ！という限られた環境です。プリザンターが導入されてjavaScriptを初めて触りました。が、エディターの導入がゆるされないので、メモ帳でチマチマ書いているお粗末な状況。。。

プリザンターでエクスポートボタンでエクスポートするのもよいのですが、エクセルからVBAでエクスポート（インポート？）すると、そのままエクセル作業ができてとても便利ではないか？ということで、見よう見まねでVBAでプリザンターから情報取得をやってみたいと思います！！！←気合入ってる！！！

なお、グダグダ書いてますが、４のコードをコピペすればできます。。。
※環境により、項目の書き方が異なる場合があるようですので、その場合は環境に合わせて修正してください。

※おことわり
2023年7月時点の情報です。プリザンターのバージョンは 1.3.20.0 です。Google Chrome でやっています。
javascript,html,cssともに初心者です。調べながら、やってみながら、きっとこうすればいいんだ！という感じで書いていますので、間違っている場合、効率的な書き方ではない可能性が大いにあります。間違ってるよ！とか、こうしたほうがいいよ！ということがありましたら、コメント等で教えていただけると大変ありがたいです。

１．VBAでプリザンターをエクスポートするには
２．VBAでプリザンターAPIを利用する際に準備すること
３．使用したサイトの内容
４．エクスポートするコード（すべてエクスポート、フィルタなし）
５．コードの解説
６．その他もろもろ
７．最後に
８.参考とさせていただいた記事

１．VBAでプリザンターをエクスポートするには

VBAでプリザンターのAPIにアクセスして、情報を取得する、ということをやるとできます。

APIとは？アプリケーションプログラムインターフェースの略でAPIです。

なにかっていうと？ネットで調べてみてください。。。

私はこのようにふんわり理解してみました。

このプログラム（アプリケーション）使いたいなーと思ったときに、受付してくれるための窓口（インターフェイス）。

たとえば天気予報のサイトから地元の今日の天気をとりよせて、サイトの一部に表示したいなーとか、思ったときに、天気予報サイトが「いいっすよ」と窓口を開設してくれていると、情報を取り寄せることができる！
天気予報サイトは「所定の用紙に所定の事項を記入して窓口まで出してくれたら、情報を返しますよ」としている。（イメージです）
「所定の用紙に所定の事項を記入する」が決まったフォームに必要事項を埋めて、「窓口」はurlが決まっていて、何らかのプログラムでほいっと送信すると、やはりまたjson形式のファイルで返答が返ってくる。所定の形式はjson形式の書き方であることが多いみたい

jsonとは・・・金曜日にカマ持ってやってくる死神・・・ではなくて、テキストファイルみたいなもので

{ 地区 : 〇〇市〇〇町 , 日付 : 2023/6/1 , 時刻 : 13時 }　

みたいな形式で、何々が〇〇を : でつないで表し、各項目は , で区切る、という形式のテキスト。

上記のようなjsonの情報を指定のurlへえいやっと送ると、

{ 天気 : 晴れ }　みたいな感じで情報が返却されてくる、というイメージです。（あくまでイメージの説明です）

なので、私はAPIというと、市役所の窓口に所定の申込用紙に必要事項を記入して、所定の形式でほしいものが返ってくる、という感じのイメージでとらえています。

正しくは、ちゃんとしたほかのサイトで調べてくださいね。。。

長くなってしまいましたが、プリザンターもこのような窓口が用意されているので、所定の「これがほしい！」と送ると、ほいよっと返してくれる、ということですね。

２．VBAでプリザンターAPIを利用する際に準備すること

・VBA-JSONを用意する
・Microsoft Scripting Runtime を参照設定しておく（こちらは任意ですが推奨）

VBAでは素のままではjsonを扱えないので、どこぞの頭のいい人が作ってくれた、json形式に変換するためのプログラム（ライブラリ）をインポートしておきます。

VBA-JOSNの取得の仕方は以下のサイト等を参考にしてください
https://vba-labo.rs-techdev.com/archives/1401

Microsoft Scripting Runtime を参照設定しておいた方がよいのは、vba では Dictionary を使って、json に変換する一歩手前の申し込み用必要事項を作成するためです。
Dictionary を利用するには Microsoft Scripting Runtime を参照設定しておくと、入力候補などが出て使いやすくなるためです。
参照設定しない場合、都度 CreateObject すると Dictionary は使えるのですが、私の好みは参照設定するほうです。なお、この記事の以降のコードはScripting Runtime を参照設定をした場合の書き方になりますので、参照設定をしない場合は書き方が一部異なりますのでご注意ください。
Dictionary と Microsoft Scripting Runtime について、詳しくは詳しく説明しているサイトがありますのでそちらも参照してみてください。
https://vbabeginner.net/dictionary-all-method-property/

jsonは { “氏名”：”田中” , “年齢” : “49” }　のように何は、何、と指定するのですが、DictionayもKyeと値のセットでデータを格納するため（キー＝氏名：値＝田中。キー＝年齢：値＝49）なんか似てますよね。だからDictionaryを使うのかな。

３．使用したサイトの内容

記録テーブルです

４．エクスポートするコード（すべてエクスポート、フィルタなし）

前置きが長くなしました。まずは絞り込みせずそのままエクスポートする、というのをやってみます。いろいろなサイトを見ながら見よう見まねで書き書きしたコード（ほぼコピペ）
※Microsoft Scripting Runtimeを参照設定している場合の書き方です。

Sub pleasanter_export1()
    Const myApikey As String = "7fc5e36aee・・・"    'APIキーは変更してください
       
    Dim jsonRequest     As Object
    Dim parseResponse   As Object
    Dim httpRequest     As Object
    
    Set jsonRequest = New Dictionary
    
    jsonRequest.Add "ApiVersion", "1.1"
    jsonRequest.Add "ApiKey", myApikey

    Set httpRequest = CreateObject("msxml2.xmlhttp")
    
    httpRequest.Open "POST", "http://localhost/api/items/426/get"   'サイトID(426)、サーバー名は変更してください
    httpRequest.setRequestHeader "Content-Type", "application/json;charset=utf-8"
    
    httpRequest.send JsonConverter.ConvertToJson(jsonRequest)
    
    Do While httpRequest.readyState < 4
        DoEvents
    Loop

    Set parseResponse = JsonConverter.ParseJson(httpRequest.responseText)
    
    If parseResponse("Response")("Data").Count <= 0 Then
        MsgBox "なし"
        Exit Sub
    End If
    
    Dim responseData As Variant
    Dim i As Long: i = 2
    For Each responseData In parseResponse("Response")("Data")
        With ActiveSheet
            .Cells(i, 1).Value = responseData("Title")
            .Cells(i, 2).Value = responseData("ClassHash")("ClassA")
            .Cells(i, 3).Value = responseData("ClassHash")("ClassB")
            .Cells(i, 4).Value = responseData("ClassHash")("ClassC")
            .Cells(i, 5).Value = responseData("NumHash")("NumA")
            .Cells(i, 6).Value = responseData("NumHash")("NumB")
            .Cells(i, 7).Value = responseData("DateHash")("DateA")
            .Cells(i, 8).Value = responseData("DescriptionHash")("DescriptionA")
            .Cells(i, 9).Value = responseData("CheckHash")("CheckA")
            .Cells(i, 10).Value = responseData("CheckHash")("CheckB")
            .Cells(i, 11).Value = responseData("Owner")
            .Cells(i, 12).Value = responseData("UpdatedTime")
            .Cells(i, 13).Value = responseData("Updator")
            .Cells(i, 14).Value = responseData("Creator")
            .Cells(i, 15).Value = responseData("ResultId")
        End With
        i = i + 1
    Next

    MsgBox "出力完了"
End Sub

結果　※1行目の項目名は手入力したものです

やったーーー！
ちなみにUpdator,Creatorがすべて１となっているのはこのプリザンターの主の私（IDが１）がすべてレコードの作成と更新をしているからです。。。

５．コードの解説

大まかな流れは以下のようになります。

（１）APIkeyをセットする
（２）変数宣言
（３）jsonRequesut
（４）httpRequestを投げる
（５）受け取る。受け取ったjsonを変換してDictionary形式にする
（６）データがあるかないかを調べる
（７）シートに出力する

さて、一つずつ

（１）APIkeyをセットする

Const myApikey As String = "123454abcde"

変数myApikey(constで宣言しているので正しくは定数）にApikeyを代入しています。

APIキーはプリザンターの右上のユーザー名のところから取得できます。
ユーザー名のところクリック→API設定
ずらーっと長い英字と数字の羅列がAPIキーになります。
表示されていない場合は「作成」ボタンをクリックするとAPIキーが表示されます。
これをコピーしてきて利用します。

（２）変数宣言

Dim jsonRequest     As Object
Dim parseResponse   As Object
Dim httpRequest     As Object

使用する変数を宣言します。ちなみに上ふたつはDictionaryとして使うので、以下のように書いてもいいです。

Dim jsonRequest     As Dictionary
Dim parseResponse   As Dictionary
Dim httpRequest     As Object

jsonRequest は、プリザンターにほいっと投げるリクエストを格納する変数です。Dictionary形式で内容を格納し、プリザンターに投げるときにVBA-JSONを利用してjson形式に変換してから投げます。

parseResponse は、プリザンターから帰ってきた返答を受け取るための変数です。返ってきた変更そのものはjson形式なので、VBA-JSONを利用してDictionary形式に変換してから parseResponse に格納します。

httpRequest は、・・・
よくわからないけれども、http通信をするためのオブジェクトだそうです。
後のコードで
Set httpRequest = CreateObject(“msxml2.xmlhttp”)
とやってます。
vbaの参照設定で「Microsoft XML,v6.0」を設定することでも利用可能となるそうです。今回は参照設定ではなく、CreateObjectでやってます。
vbaでHTTP通信をするためのライブラリだそうです。

HTTP通信とは、普通はWebでこのページちょうだい→ほいよ、とかやったりするWeb界とつながる通信だと思うのですが、vbaでできちゃうなんてすごいですね。
以下のリンクが大変参考になりました。（参考にはなったが、本当はよく理解していない）
https://tonari-it.com/excel-vba-http-request/

（３）jsonRequesut

Set jsonRequest = New Dictionary
jsonRequest.Add "ApiVersion", "1.1"
jsonRequest.Add "ApiKey", myApikey

jsonRequest、つまりプリザンターAPI窓口にほいっと投げるリクエスト（申込用紙）を作っているところです。

今回は最低限の必要事項のみ記入している感じです。

jsonRequestというディクショナリに以下のイメージのモノを登録した感じです。

ApiVersion：1.1
ApiKey : myApykey　

（変数myApikeyには7fc5e36aee・・・のような英数字の羅列が入ってます）
（イメージのため”は取っ払ってます)

ちなみにjsonに変換すると以下のような感じになるというイメージ

{ “ApiVerson” : “1.1”, “ApiKey” : “7fc5e36aee” }

（４）httpRequestを投げる

Set httpRequest = CreateObject("msxml2.xmlhttp")
httpRequest.Open "POST", "http://localhost/api/items/426/get" 
httpRequest.setRequestHeader "Content-Type", "application/json;charset=utf-8"
httpRequest.send JsonConverter.ConvertToJson(jsonRequest)
    
Do While httpRequest.readyState < 4
    DoEvents
Loop

HTTP通信をしている部分です。ま、コピペですよね。
コピペして　http://～/items のサーバー名のところと、サイトIDのところ（上記のコードではitemsとgetの間の426の部分）だけ実際の環境に合わせて変更したらよいです。

Set httpRequest = CreateObject(“msxml2.xmlhttp”)
vbaがhttp通信ができるようにする呪文。。。参照設定をしておくなどほかの書き方もできます。

httpRequest.Open “POST”, “http://localhost/api/items/426/get”
また呪文。。。HTTP通信をするための初期化をしているとか。。。
OPENと書いているけど、まだページを開いているわけではなく「このページにPOSTしに行くよぅ」という準備をしているっぽい。POSTだから、リクエストをポストに投函するイメージかな。
しつこいようですが、サーバー名(localhostの部分)とサイトID（426の部分）は変更してくださいね。

httpRequest.setRequestHeader “Content-Type”, “application/json;charset=utf-8”
はい、また呪文。リクエストのヘッダーを作ってる感じ？

httpRequest.send JsonConverter.ConvertToJson(jsonRequest)
やっとここでリクエストをSend（送ってる）みたいですね！
インポートしておいた JsonConverter の ConvertToJson を使ってます。
Dictionary で作っておいた jsonRequest をjson形式にコンバート（変換）してくれてます。
その変換したやつを send しています。

Do While httpRequest.readyState < 4
　　　DoEvents
Loop

これも呪文。返答が返ってくるのを待って、帰ってきたら次に進むという処理です。
readyStateが4未満だとまだ。4になったら返答が返ってきたということみたいです。

ちなみに、別の書き方もサイトに載っていました。Openの時に False を付けると上記のDo…Loopの部分は不要でした。Flaseを付けない場合、Do…LoopでreadyState が4になるのを待たないとエラーになりました。
httpRequest.Open “POST”, “http://localhost/api/items/426/get”, False

（５）受け取る

Set parseResponse = JsonConverter.ParseJson(httpRequest.responseText)

ここもコピペですね。。。

httpRequestオブジェクトのresponseTextに返ってきた返答が入ります。
返答は json形式のため、そのままではvbaで利用できないので、JsonConverter の ParseJson を利用して Dictionary 形式に変換し、用意しておいた変数 parseResponse に代入します。

（６）データがあるかないかを調べる

If parseResponse("Response")("Data").Count <= 0 Then
    MsgBox "なし"
    Exit Sub
End If

parseResponse のカウントが0だったら、返答データなし、ということで終了します。

（７）シートに出力する

Dim responseData As Variant
Dim i As Long: i = 2
For Each responseData In parseResponse("Response")("Data")
    With ActiveSheet
        .Cells(i, 1).Value = responseData("Title")
        .Cells(i, 2).Value = responseData("ClassHash")("ClassA")
        .Cells(i, 3).Value = responseData("ClassHash")("ClassB")
        .Cells(i, 4).Value = responseData("ClassHash")("ClassC")
        .Cells(i, 5).Value = responseData("NumHash")("NumA")
        .Cells(i, 6).Value = responseData("NumHash")("NumB")
        .Cells(i, 7).Value = responseData("DateHash")("DateA")
        .Cells(i, 8).Value = responseData("DescriptionHash")("DescriptionA")
        .Cells(i, 9).Value = responseData("CheckHash")("CheckA")
        .Cells(i, 10).Value = responseData("CheckHash")("CheckB")
        .Cells(i, 11).Value = responseData("Owner")         '担当者
        .Cells(i, 12).Value = responseData("UpdatedTime")   '更新日時
        .Cells(i, 13).Value = responseData("Updator")       '更新者
        .Cells(i, 14).Value = responseData("Creator")       '作成者
        .Cells(i, 15).Value = responseData("ResultId")      'レコードID
    End With
    i = i + 1
Next

Excelのワークシートに出力しています。返ってきたデータはDictionaryの入れ子になっているんですなあ。複雑で頭が追い付かず。

parseResponse(“Response”)(“Data”) からひとつづつデータセットを responseData に取り出して出力しています。

responseData の中身はイメージ的にはこんな形になっているのではないかと。あくまでもイメージです。

{ 
    "Title" : "タイトル299",
    "ClassHash" : { "ClassA": "100",
                    "ClassB": "小麦粉", 
                    "ClassC": "セット" },
    "NumHash" : { "NumA": "1584",
                  "NumB": "30" },
}

responseData(“Title”)　とすることでキー”Title”に対する値「タイトル299」が取得できます。
responseData(“ClassHash”)(“ClassA”)　”ClassHash”の中の”ClassA”としていることで、ClassAの値が取得できます。Titleのような独立した項目とは違い、「”ClassHash”の中の」を指定しないとその中身は取り出せないんですね。