[Excel/Access VBA]Google カレンダーから自動で祝日/祭日を取得する方法(マクロ/Web API)

こんにちは。あおいです。

Noteでは初のExcel/Access記事になります。私のブログではいくつかExcel/Access記事を書いているので気が向いた時にでも見に来てください。

[Access VBA]主キーである項目名(フィールド名)を取得する方法

[VBA]エクセルで仮想通貨取引所のAPI情報を取得する方法

[Excel]入力規則のリストにテーブルの列範囲(フィールド/Field)を指定する方法

こんな感じでブログには備忘を載せています。

話は戻り表題へ。今回はGoogle Calendar(グーグルカレンダー)からVBA(マクロ)を使って自動的に祝祭日を取得する方法をお伝えします。

イメージとしては、

１．取得したい期間を設定し、GoogleCalendarにWebAPIでアクセスする。

２．Jsonデータを取得してVBAで扱いやすいように加工する。

３．Excelの表に反映する。

完成イメージは下記図の通り。(図１)

図１、①で取得したい期間を設定し、②祝祭日取得ボタンをクリックすると、下の表に祝日の名称と日付の一覧が出力されます。

取得した祝祭日一覧を見ると、2018年にはなかった即位の礼が2019/5/1に入ってきました。また、2019年は2018年までの祝日だった天皇誕生日がないことも確認できます。（今上天皇の誕生日が１２月、皇太子の誕生日が２月であることから）基本的にはカレンダーの正確性はGoogleに依存します。

Googleカレンダーから祝日を取得するコードは下記の通りです。一旦、取得する期間は固定にしました。WebAPIを利用する際に必要になるため、『Microsoft XML. v6.0』の参照設定を行ってください。下記のコードを実行するとJson形式で２年間の祝祭日の一覧が取得できます。

Const APIURL    As String = "https://www.googleapis.com/calendar/v3/calendars/"    'GoogleCalendarにアクセスするためのURL
Const CalID     As String = "japanese__ja@holiday.calendar.google.com"             '日本の休日のID(このカレンダーに日本の休日が登録されている)
Const APIKey    As String = ""                                                     'APIKey 取得はこちらから→　https://code.google.com/apis/console/

Const StartDay  As Date = #2018/1/1#
Const EndDay    As Date = #2019/12/31# 

'URLの作成
URL = APIURL & CalID & "/events?key=" & APIKey
    
'URLに祝祭日を取得したい期間を設定しアクセスする。sendで取得が完了するまで待機。
XML.Open "GET", URL & "&timeMin=" & Format(StartDay, "yyyy-mm-dd") & "T00:00:00.000Z&timeMax=" & Format(EndDay, "yyyy-mm-dd") & "T23:59:59.000Z"
XML.send

実際に上記コードを実行する際には、GoogleカレンダーにアクセスするためのAPIキーが必要となります。APIキーの取得方法はネットにたくさんあるので調べてみてください。個人的に分かりやすかったサイトを１つ載せておきます。

GoogleカレンダーAPIキー取得とキーの制限方法 | design Edge

APIキーが取得できたら上記コードのAPIKey定数に入力しましょう。実行するとXML.responseTextプロパティに下記のようなJson形式のデータが格納されます。

{
 "kind": "calendar#events",
 "etag": "\"p32sahh52sfuts0g\"",
 "summary": "日本の祝日",
 "updated": "2018-11-29T18:40:58.000Z",
 "timeZone": "UTC",
 "accessRole": "reader",
 "defaultReminders": [],
 "nextSyncToken": "CIDFmrCg-t4CEAAYAQ==",
 "items": [
  {
   "kind": "calendar#event",
   "etag": "\"2778544482000000\"",
   "id": "20180505_60o30d9lcco30c1g60o30dr568",
   "status": "confirmed",
   "htmlLink": "https://www.google.com/calendar/event?eid=MjAxODA1MDVfNjBvMzBkOWxjY28zMGMxZzYwbzMwZHI1NjggamFwYW5lc2VfX2phQGg",
   "created": "2014-01-09T12:57:21.000Z",
   "updated": "2014-01-09T12:57:21.000Z",
   "summary": "こどもの日",
   "creator": {
    "email": "japanese__ja@holiday.calendar.google.com",
    "displayName": "日本の祝日",
    "self": true
   },
   "organizer": {
    "email": "japanese__ja@holiday.calendar.google.com",
    "displayName": "日本の祝日",
    "self": true
   },
   "start": {
    "date": "2018-05-05"
   },
   "end": {
    "date": "2018-05-06"
   },
   "transparency": "transparent",
   "visibility": "public",
   "iCalUID": "20180505_60o30d9lcco30c1g60o30dr568@google.com",
   "sequence": 0
  },

  ・
  ・
  ・
  
 ]
}

Json形式のデータには、items配列に複数の祝祭日が入っており(上記ではこどもの日のみ掲載)、単一の祝日の中、[summary]に名称、[start]の[date]に日付が入っていることが分かります。

しかし、Json形式のままではExcelVBAで容易に情報を取り出すことができません。ネットで検索しても知恵を働かさない限りはなかなかみつからないと思います。

今回はそのJson形式のデータから容易に日付と名称の情報を取り出しExcelの表に出力する部分を有料記事とさせていただきました。皆様には分かりやすく理解して頂くため、全コード合わせて4０行未満にしています。この方法を理解することができればJson形式である限り、カレンダーだけではなく色々な情報を容易に扱うことができるようになります。ぜひ購入をご検討ください。

ここからは有料記事となります。