Accessing Podio from vba (Excel/Word)
I have to read and write podio data out of Word or Excel. So I wrote following code at first.
Dim winHttpReq As Object
Dim access_token As String
Function OAuthAppAuthorization()
Dim result As String
Dim postData As String
Dim myURL As String
Dim token_pos As Long
'Initialisierung
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "https://api.podio.com/oauth/token"
postData = "grant_type=app&client_id=podiotest-XXX&client_secret=XXX&app_id=999&app_token=XXX"
If winHttpReq.Open("POST", myURL, False) = S_OK Then
'MsgBox ("Open erfolgreich")
End If
winHttpReq.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
winHttpReq.Send (postData)
result = winHttpReq.responseText
token_pos = InStr(result, "access_token")
access_token = Mid(result, token_pos + 15)
token_pos = InStr(access_token, Chr(34))
access_token = Mid(access_token, 1, token_pos)
'MsgBox (access_token)
OAuthAppAuthorization = result
End Function
This returns a good result.
{"access_token":"0bb5654b8cd74aaeaf2242565d05317e","token_type":"bearer","ref":{"type":"app","id":999},"expires_in":28800,"refresh_token":"385466e5726c42599cad600d9d1307f0"}
Then I wrote following to read some App data:
Function get_items(app_id As String)
Dim result As String
Dim myURL As String
Dim GetQuery As String
GetQuery = "?Authorization:OAuth2 " & access_token
myURL = "https://api.podio.com/item/app/" & app_id & GetQuery
winHttpReq.Open "GET", myURL, False
winHttpReq.Send
result = winHttpReq.responseText
get_items = result
End Function
But here I always get en error as result:
{"error_parameters":{},"error_detail":null,"error_propagate":false,"request":{"url":"http:\/\/api.podio.com\/item\/app\/999","query_string":"Authorization:OAuth2%200bb5654b8cd74aaeaf2242565d05317e\"","method":"GET"},"error_description":"invalid_request","error":"unauthorized"}
I think I did all as in your dokumentation. Have you any idea what is wrong with the get function?
best regards
-
For anyone else having trouble, there's a problem parsing the response from OAuthAppAuthorization:
result = winHttpReq.responseText token_pos = InStr(result, "access_token") access_token = Mid(result, token_pos + 15) token_pos = InStr(access_token, Chr(34)) access_token = Mid(access_token, 1, token_pos)This is including the double-quote when setting the access_token variable, which isn't a valid access token.
For example, running OAuthAppAuthorization returns this:
{"access_token":"12345","expires_in".....
The code above sets the access_token variable to: 12345" (including the double-quote)
Here's the fix:
token_pos = InStr(access_token, Chr(34)) -1 -
You can use "SetRequestHeader " to send the header... The following code is able to get the item :
Function GetItems(app_id As String)
Dim result As String
Dim myURL As String
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "https://api.podio.com/item/app/" & app_id
winHttpReq.Open "GET", myURL, False
winHttpReq.SetRequestHeader "Authorization", "OAuth2 " & access_token
winHttpReq.Send
result = winHttpReq.responseText
GetItems = result
End Function -
This is not quite working for me yet!
The first section of code seems to work just fine, I can trigger a message box giving the API credentials.
The second section returns an error saying "Invalid Authorization Header" using Stephane's code.
I've tried amending but am struggling! Does anyone have any thoughts?
Podio is great, and I know Globiflow could probably deal with what I need, but it's not going to happen due to the cost! It would be great for Power Query/Power BI links to be available too.
This post seems to offer the best solution but not quite there yet!
Please sign in to leave a comment.
Comments
9 comments