Post

9 followers Follow
1
Avatar

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

Markus Reuter

Please sign in to leave a comment.

8 comments

1
Avatar

Hi Markus,

It looks like you are sending the OAuth2 access token as a URL query string parameter. It needs to be sent as an HTTP header.

/Andreas

Andreas Haugstrup Pedersen 1 vote
1
Avatar

Hey, Would you mind posting your completed code?

I got the first section to work as you did, Could you please post the latter bit of code to get the items?

Regards

Leee

Leee Jeffries 1 vote
0
Avatar

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

Stéphane Aubert 0 votes
0
Avatar

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!

 

 

Simon Beckham 0 votes
0
Avatar

Made some progress (I think!), error has changed from "Invalid Authorisation Header" to "expired token".

Not sure what else to change?

 

 

Simon Beckham 0 votes
1
Avatar

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

 

Laura Hotalling 1 vote