{"id":9287,"date":"2017-10-31T09:00:04","date_gmt":"2017-10-31T00:00:04","guid":{"rendered":"https:\/\/www.ka-net.org\/blog\/?p=9287"},"modified":"2017-10-31T09:38:39","modified_gmt":"2017-10-31T00:38:39","slug":"google-calendar-api%e3%82%92%e4%bd%bf%e3%81%a3%e3%81%a6%e6%97%a5%e6%9c%ac%e3%81%ae%e7%a5%9d%e6%97%a5%e3%82%92%e5%8f%96%e5%be%97%e3%81%99%e3%82%8bvba%e3%83%9e%e3%82%af%e3%83%ad","status":"publish","type":"post","link":"https:\/\/www.ka-net.org\/blog\/?p=9287","title":{"rendered":"Google Calendar API\u3092\u4f7f\u3063\u3066\u65e5\u672c\u306e\u795d\u65e5\u3092\u53d6\u5f97\u3059\u308bVBA\u30de\u30af\u30ed"},"content":{"rendered":"

\u795d\u65e5\u306e\u4e00\u89a7\u3092\u7528\u610f\u3059\u308b\u5fc5\u8981\u304c\u3042\u3063\u305f\u306e\u3067\u3001Google Calendar API\u3092\u4f7f\u3063\u3066\u65e5\u672c\u306e\u795d\u65e5\u3092\u53d6\u5f97\u3059\u308b\u30de\u30af\u30ed\u3092\u8003\u3048\u3066\u307f\u307e\u3057\u305f\u3002<\/p>\n

API\u30ad\u30fc\u306e\u4f5c\u6210<\/h4>\n

Calendar API\u3092\u5229\u7528\u3059\u308b\u306b\u5f53\u305f\u308a\u3001\u307e\u305a\u306f\u5fc5\u8981\u3068\u306a\u308bAPI\u30ad\u30fc\u3092\u7528\u610f\u3057\u307e\u3059\u3002<\/p>\n

    \n
  1. Google Cloud Console<\/a>\u3092\u958b\u304d\u3001Google \u30a2\u30ab\u30a6\u30f3\u30c8\u3067\u30ed\u30b0\u30a4\u30f3\u3057\u307e\u3059\u3002<\/li>\n
  2. \u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u306e\u9078\u629e\u753b\u9762\u304b\u3089\u300c\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3092\u4f5c\u6210<\/span>\u300d\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

    \"\"<\/a><\/p>\n

  3. \u65b0\u3057\u3044\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u753b\u9762\u304c\u8868\u793a\u3055\u308c\u308b\u306e\u3067\u3001\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u540d\u3092\u5165\u529b\u3057\u3001\u300c\u4f5c\u6210<\/span>\u300d\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  4. \u3057\u3070\u3089\u304f\u5f85\u3064\u3068\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u304c\u4f5c\u6210\u3055\u308c\u308b\u306e\u3067\u3001\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u306e\u9078\u629e\u753b\u9762\u304b\u3089\u4f5c\u6210\u3057\u305f\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3092\u958b\u304d\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  5. \u300cAPI\u3068\u30b5\u30fc\u30d3\u30b9<\/span>\u300d\u304b\u3089\u300c\u30e9\u30a4\u30d6\u30e9\u30ea<\/span>\u300d\u3092\u958b\u304d\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  6. \u300cGoogle Calendar API<\/span>\u300d\u3092\u9078\u629e\u3057\u3001\u300c\u6709\u52b9\u306b\u3059\u308b<\/span>\u300d\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

    \"\"<\/a><\/p>\n

  7. \u300c\u8a8d\u8a3c\u60c5\u5831\u3092\u4f5c\u6210<\/span>\u300d\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  8. \u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3078\u306e\u8a8d\u8a3c\u60c5\u5831\u306e\u8ffd\u52a0\u753b\u9762\u304c\u8868\u793a\u3055\u308c\u307e\u3059\u304c\u3001\u300cAPI \u30ad\u30fc<\/span>\u300d\u3092\u30af\u30ea\u30c3\u30af\u3057\u3066\u3053\u306e\u624b\u9806\u3092\u30b9\u30ad\u30c3\u30d7\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  9. API \u30ad\u30fc\u753b\u9762\u304c\u8868\u793a\u3055\u308c\u308b\u306e\u3067\u3001\u300c\u4f5c\u6210<\/span>\u300d\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u3066API\u30ad\u30fc\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/li>\n

    \"\"<\/a><\/p>\n

  10. \u4f5c\u6210\u3055\u308c\u305fAPI\u30ad\u30fc\u3092\u30b3\u30d4\u30fc\u3057\u3066\u3001\u30e1\u30e2\u5e33\u7b49\u306e\u30c6\u30ad\u30b9\u30c8\u30a8\u30c7\u30a3\u30bf\u306b\u8cbc\u308a\u4ed8\u3051\u3066\u304a\u304d\u307e\u3059\u3002<\/li>\n

    \"\"<\/a>\n<\/ol>\n

    Google Calendar API\u3092\u4f7f\u3063\u3066\u65e5\u672c\u306e\u795d\u65e5\u3092\u53d6\u5f97\u3059\u308b\u30de\u30af\u30ed<\/h4>\n

    \u203b\u4e0b\u8a18\u30b3\u30fc\u30c9\u306fScriptControl\u3092\u4f7f\u7528\u3057\u3066\u3044\u308b\u305f\u3081\u300164\u30d3\u30c3\u30c8\u7248Office\u3067\u306f\u52d5\u4f5c\u3057\u307e\u305b\u3093\u3002<\/span>
    \n\u203b\u30b3\u30fc\u30c9\u4e2d\u306e\u300cApiKey\u300d\u306e\u5024\u306f\u3001\u4e0a\u8a18\u624b\u9806\u3067\u53d6\u5f97\u3057\u305fAPI\u30ad\u30fc\u306b\u7f6e\u304d\u63db\u3048\u3066\u304f\u3060\u3055\u3044\u3002<\/span><\/p>\n

    Option Explicit\r\n\r\nPrivate Sub GetHolidaysFromGoogleCalendar()\r\n'Google Calendar API\u3092\u4f7f\u3063\u3066\u65e5\u672c\u306e\u795d\u65e5\u3092\u53d6\u5f97\r\n  Dim url As String\r\n  Dim js As String\r\n  Dim summary As String, start_date As String\r\n  Dim items As Object, item As Object\r\n  Dim jso_start As Object\r\n  Dim i As Long\r\n  \r\n  '--------------------------------------------------\r\n  '\u203b\u5fc5\u8981\u306b\u5fdc\u3058\u3066\u5909\u66f4\r\n  '--------------------------------------------------\r\n  Const TargetYear As String = "2018" '\u795d\u65e5\u3092\u53d6\u5f97\u3059\u308b\u5e74\r\n  Const CalendarId As String = "ja.japanese#holiday@group.v.calendar.google.com"\r\n  Const ApiKey As String = "(\u53d6\u5f97\u3057\u305fAPI\u30ad\u30fc)"\r\n  '--------------------------------------------------\r\n  \r\n  url = "https:\/\/www.googleapis.com\/calendar\/v3\/calendars\/" & _\r\n        EncodeURL(CalendarId) & _\r\n        "\/events?orderBy=startTime&singleEvents=true" & _\r\n        "&timeMin=" & EncodeURL(TargetYear & "-1-1T00:00:00+09:00") & _\r\n        "&timeMax=" & EncodeURL(TargetYear & "-12-31T00:00:00+09:00") & _\r\n        "&key=" & ApiKey\r\n  \r\n  With CreateObject("WinHttp.WinHttpRequest.5.1")\r\n    .Open "GET", url, False\r\n    .Send\r\n    Select Case .Status\r\n      Case 200: js = .ResponseText\r\n    End Select\r\n  End With\r\n  If Len(Trim(js)) < 1 Then Exit Sub\r\n  \r\n  i = 1 '\u521d\u671f\u5316\r\n  js = "(" & js & ")"\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    Set items = CallByName(.CodeObject.eval(js), "items", VbGet)\r\n  End With\r\n  With ActiveSheet\r\n    For Each item In items\r\n      Set jso_start = CallByName(item, "start", VbGet)\r\n      start_date = CallByName(jso_start, "date", VbGet)\r\n      summary = CallByName(item, "summary", VbGet)\r\n      .Cells(i, 1).Value = start_date\r\n      .Cells(i, 2).Value = summary\r\n      i = i + 1\r\n    Next\r\n  End With\r\nEnd Sub\r\n\r\nPrivate Function EncodeURL(ByVal Target As String) As String\r\n'URL\u30a8\u30f3\u30b3\u30fc\u30c9\r\n  With CreateObject("ScriptControl")\r\n    .Language = "JScript"\r\n    EncodeURL = .CodeObject.encodeURIComponent(Target)\r\n  End With\r\nEnd Function<\/pre>\n
    \u4e0a\u8a18\u30b3\u30fc\u30c9\u3092\u5b9f\u884c\u3059\u308b\u3068\u3001\u9078\u629e\u4e2d\u306e\u30b7\u30fc\u30c8\u306b\u3001\u6307\u5b9a\u3057\u305f\u5e74\u306e\u795d\u65e5\u304c\u66f8\u304d\u3053\u307e\u308c\u307e\u3059\u3002<\/p>\n
    \"\"<\/a><\/p>\n
    \n
    \n