使用 VBA 为 Google Calendar API V3 构建 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10082555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Build JSON For Google Calendar API V3 Using VBA
提问by HK1
I'm trying to add an event to a Google Calendar using the Google API V3. I'm not using any of Google's libraries so I'm really baffled as to how to do this. Google's documentationtells me that I need to send an "Events Resource"in the request body. I think I've built a valid JSON "string" but I have no idea how to properly prepare it and send it in the so-called "request body". What confuses me is the way that string values are wrapped in quotes and non-string values are not. Does this mean that I'll need to wrap the entire thing as a string and double/quadruple quote each double quote?
我正在尝试使用 Google API V3 将事件添加到 Google 日历。我没有使用任何谷歌的库，所以我真的很困惑如何做到这一点。Google 的文档告诉我，我需要在请求正文中发送一个“事件资源”。我想我已经构建了一个有效的 JSON“字符串”，但我不知道如何正确准备它并将其发送到所谓的“请求正文”中。让我感到困惑的是字符串值用引号括起来而非字符串值不是这样的方式。这是否意味着我需要将整个内容包装为一个字符串并用双/四引号将每个双引号括起来？
Here's the JSON I've written but I haven't figure out yet how to pass this to Google so I haven't been able to test it:
这是我编写的 JSON，但我还没有弄清楚如何将其传递给 Google，因此我无法对其进行测试：
{
"kind": "calendar#event",
"start": {
"dateTime": 04/10/2012 08:00 AM
},
"end": {
"dateTime": 04/10/2012 08:00 AM
},
"attendees": [
{
"email": "[email protected]",
"displayName": "My Name",
"organizer": True,
"self": True
}
],
"reminders": {
"useDefault": True
}
}
I do have a VBJSON code module installed in my Access/VBA Database. I see a function in there called StringToJSON which returns a string. I'm still baffled. When I pass this JSON to Google, will it merely be one big string value within my code?
我的 Access/VBA 数据库中确实安装了一个 VBJSON 代码模块。我在那里看到一个名为 StringToJSON 的函数，它返回一个字符串。我还是一头雾水。当我将此 JSON 传递给 Google 时，它是否只是我代码中的一个大字符串值？
采纳答案by HK1
OK, so I finally figured out how to build and pass my JSON string. I'm using VBJSON to build the JSON string. Please remember that JSON is case sensitive (or at least Google interprets it case sensitive). A pair with the key dateTime is not the same as a pair with the key datetime and Google will reject the latter.
好的，所以我终于想出了如何构建和传递我的 JSON 字符串。我正在使用 VBJSON 来构建 JSON 字符串。请记住，JSON 区分大小写（或至少 Google 将其解释为区分大小写）。键为 dateTime 的对与键为 datetime 的对不同，Google 会拒绝后者。
'Code to create JSON using Dictionary Objects and Collection Objects
Dim d As New Scripting.Dictionary
Dim c As New Collection
d.Add "kind", "calendar#event"
d.Add "summary", "Event Title/Summary"
Dim d2(4) As New Scripting.Dictionary
d2(0).Add "dateTime", "2012-04-14T16:00:00.000-04:00"
d.Add "start", d2(0)
d2(1).Add "dateTime", "2012-04-14T18:00:00.000-04:00"
d.Add "end", d2(1)
'First Attendee
d2(2).Add "email", "[email protected]"
d2(2).Add "displayName", "John Doe"
d2(2).Add "organizer", True
d2(2).Add "self", True
'Add attendee to collection
c.Add d2(2)
'Second attendee
d2(3).Add "email", "[email protected]"
d2(3).Add "displayName", "Suzy Doe"
'Add attendee to collection
c.Add d2(3)
'Add collection to original/primary dictionary object
d.Add "attendees", c
'Add more nested pairs to original/primary dictionary object
d2(4).Add "useDefault", True
d.Add "reminders", d2(4)
'Now output the JSON/results
'This requires the VBJSON module (named just JSON, a module, not a class module)
Debug.Print JSON.JSONToString(d)
The unprettified output is this:
未经修饰的输出是这样的：
{"kind":"calendar#event","summary":"Event Title\/Summary","start":{"dateTime":"2012-04-14T16:00:00.000-04:00"},"end":{"dateTime":"2012-04-14T18:00:00.000-04:00"},"attendees":[{"email":"[email protected]","displayName":"John Doe","organizer":true,"self":true},{"email":"[email protected]","displayName":"Suzy Doe"}],"reminders":{"useDefault":true}}
And then here's how you submit it to Google using V3 of the Google Calendar API. In V3 you have to use OAuth2.0 so you need to have a valid Access Token to append to your URL as shown below. You'll also need to know your CalendarID which is usually your email address URL encoded. For example, your calendarid will look like this: john.doe%40gmail.com
然后这里是你如何使用谷歌日历 API 的 V3 将它提交给谷歌。在 V3 中，您必须使用 OAuth2.0，因此您需要有一个有效的访问令牌来附加到您的 URL，如下所示。您还需要知道您的 CalendarID，它通常是您的电子邮件地址 URL 编码。例如，您的日历 ID 将如下所示：john.doe%40gmail.com
Dim objXMLHTTP As MSXML2.ServerXMLHTTP
Set objXMLHTTP = New MSXML2.ServerXMLHTTP
Dim sPostData As String
sPostData = JSON.JSONToString(d)
Dim sURL As String
sURL = "https://www.googleapis.com/calendar/v3/calendars/{mycalendarid}/events?sendNotifications=false&fields=etag%2ChtmlLink%2Cid&pp=1&access_token={my oauth2.0 access token}"
With objXMLHTTP
.Open "POST", sURL, False
.setRequestHeader "Content-Type", "application/json"
.Send (sPostData)
End With
Debug.Print objXMLHTTP.ResponseText
Set objXMLHTTP = Nothing
