Google Calendar APIを使ってExcelで祝日を扱う方法

Excelで祝日を扱いたい

祝日は自動的な処理ができないですよね。毎年同じ日付ではない祝日も多いですから。それでもExcelで処理したいという状況をなんとか解決してみましょう。

Google Calendar API を利用する

Googleカレンダーでは祝日を扱えます。これを外部から使う仕組みがGoogle Calendar APIです。もちろんGoogleアカウントが必要で、以下のサイトから設定します。

Google Developers Console

https://console.developers.google.com/home/

設定までの流れは

プロジェクトを作る
Calendar APIを有効にする
認証情報を追加する
APIキー（サーバーキー）を作成する

ここで作成したAPIキーをExcelの中で使います。

Calendar APIの動作確認

以下のURLをブラウザで入力すると、Calendar APIからの出力を確認することができます。JSON形式で出力されます。

https://www.googleapis.com/calendar/v3/calendars/ja.japanese%23holiday@group.v.calendar.google.com/events?maxResults=100&orderBy=startTime&singleEvents=true&key=作成したAPIキー&timeMin=2015-01-01T00:00:00Z&timeMax=2016-01-01T00:00:00Z

timeMinとtimeMaxが期間の設定で、例では2015年の全ての祝日情報が出力されます。

出力例

{
 "kind": "calendar#events",
 "etag": "\"1448683533000000\"",
 "summary": "日本の祝日",
 "description": "日本の祝日と行事",
 "updated": "2015-11-28T04:05:33.000Z",
 "timeZone": "UTC",
 "accessRole": "reader",
 "defaultReminders": [],
 "items": [
  {
   "kind": "calendar#event",
   "etag": "\"2778544492000000\"",
   "id": "20140101_60o30d9l64o30c1g60o30dr4ck",
   "status": "confirmed",
   "htmlLink": "https://www.google.com/calendar/event?eid=MjAxNDAxMDFfNjBvMzBkOWw2NG8zMGMxZzYwbzMwZHI0Y2sgamEuamFwYW5lc2UjaG9saWRheUB2",
   "created": "2014-01-09T12:57:26.000Z",
   "updated": "2014-01-09T12:57:26.000Z",
   "summary": "元日",
   "creator": {
    "email": "ja.japanese#holiday@group.v.calendar.google.com",
    "displayName": "日本の祝日",
    "self": true
   },
   "organizer": {
    "email": "ja.japanese#holiday@group.v.calendar.google.com",
    "displayName": "日本の祝日",
    "self": true
   },
   "start": {
    "date": "2014-01-01"
   },
   "end": {
    "date": "2014-01-02"
   },
   "transparency": "transparent",
   "visibility": "public",
   "iCalUID": "20140101_60o30d9l64o30c1g60o30dr4ck@google.com",
   "sequence": 0
  },
（以下略）

"items"の大きなくくりの中に祝日のデータが{ }で括られて格納されています。
多くのデータが格納されていますが、各要素の"summary"が祝日名と"start"の中にある"date"が日付が最低限必要なデータでしょう。

【スポンサーリンク】

ExcelVBAから取得する

ExcelVBAからはhttpでアクセスするとデータを取得できます。httpでデータを取得する方法は以下のサイトを参考にさせてもらいました。コードもここから改造しています。
ExcelのVBAでJSON形式のデータを解析する - Symfoware

http接続部はこのサイトから無変更でそのまま借用しています。

Public Function CreateHttpObject() As Object
  Dim objweb As Object

  '各種名称でHTTPオブジェクトの生成を試みる
  Err.Clear
  Set objweb = CreateObject("MSXML2.ServerXMLHTTP.6.0")
  If Err.Number = 0 Then
      Set CreateHttpObject = objweb
      Exit Function
  End If
  Err.Clear
  Set objweb = CreateObject("MSXML2.ServerXMLHTTP")
  If Err.Number = 0 Then
     Set CreateHttpObject = objweb
     Exit Function
  End If
  Err.Clear
  Set objweb = CreateObject("MSXML2.XMLHTTP")
  If Err.Number = 0 Then
      Set CreateHttpObject = objweb
      Exit Function
  End If
  Set CreateHttpObject = Nothing
End Function

CreateHttpObject()の戻り値がhttpのオブジェクトになっています。
objwebに CreateHttpObject()で取得したオブジェクトを格納し、urlに取得したい場所のURLを与えて次の手順でhttpのレスポンスとして結果を取得することができます。urlには先の動作確認で使った形式を指定します。

    objweb.Open "GET", url, False ' GETリクエストの生成
    objweb.Send　　　　　　' リクエストの送信
    objweb.responseText　’httpのレスポンス

JSONの解析

JSONはもともとjavascriptで利用しやすい形式です。javascriptを使うとそのまま配列として参照しやすい形になっています。形式もわかっているので、自前で解析することも可能ですが、ScriptControlを活用して簡単に解析することにしましょう。VBAからjavascriptを実行できる仕組みです。

dataにhttpレスポンスの値をまるごと渡しておきます。

    Set sc = CreateObject("ScriptControl")
    With sc
        .Language = "JScript"
        .addcode "var ary = " & data & ";"
        .addcode "var ary2;"
        .addcode "function getItems(name) { ary2 =  ary[name]; } "
        .addcode "function getValue(index, name) {return ary2[index][name]; }"
        .addcode "function getValue2(index, name1,name2) {return ary2[index][name1][name2]; }"
        .addcode "function getLength() { return ary2.length;}"
    End With

javascript部だけを取り出すと以下のようになります。aryにJSON形式のデータを与えておくと、配列参照で様々なデータを取り出すことができます。

        var ary = /*ここにデータを入れる*/ ;
        var ary2;
        function getItems(name) { 
            ary2 =  ary[name]; 
        }
        function getValue(index, name) {
             return ary2[index][name]; 
        }
        function getValue2(index, name1,name2) {
             return ary2[index][name1][name2]; 
        }
　　　　function getLength() { 
             return ary2.length;
        }

getItems(name)では、カレンダーのJSONデータから項目名を指定してary2に格納しておきます。
getValue(index,name)は1段下の階層のデータを取得します。
getValue2(index,name1,name2)は2段下の階層のデータを取得します。
getLength()はary2に格納されたデータ数を取得します。

祝日データをセルに書き込む

取得したデータを直接使ってもよいですが、とりあえずセルに書き込むプログラムにしてみました。

    sc.CodeObject.getItems ("items")
    Max = sc.CodeObject.getLength("")
    For i = 0 To Max - 1
        items = sc.CodeObject.getValue2(i, "start", "date")
        items2 = sc.CodeObject.getValue(i, "summary")
        Cells(i + 1, 1) = items
        Cells(i + 1, 2) = items2
    Next i

これでA列にに日付、B列に祝日名を取得することができます。

ひっきぃのメモ帳

趣味で作るプラモデル製作の過程と作品、日々の資格取得へ向けての活動、Apple中心のIT関連ネタを書いています。