How to connect excel with API?

[Ankit_Dua]

I am trying to get the authentication token in excel and have used the following query:

let

mktoUrlStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[URL],

clientIdStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client ID],

clientSecretStr = Excel.CurrentWorkbook(){[Name="REST_API_Authentication"]}[Content]{0}[Client Secret],

getAccessTokenUrl = mktoUrlStr & "/identity/oauth/token?grant_type=client_credentials&client_id=" & clientIdStr & "&client_secret=" & clientSecretStr,

TokenJson = try Json.Document(Web.Contents(getAccessTokenUrl)) otherwise "Marketo REST API Authentication failed, please check your credentials",

accessTokenStr = TokenJson [access_token]

in

accessTokenStr

But everytime it throws an error

What should I do to fix this?

[SanfordWhiteman]

Supply the full worksheet you're using. This isn't useful otherwise.

[Ankit_Dua]

Thank you for your reply Sanford, there is not much to see in the sheet. I am trying to generate access token through this.

I have created a table with three columns:

1. URL

2. Client ID

3. Client Secret.

When I insert this code in "Advanced editor", it throws the above error.

[SanfordWhiteman]

So what you're saying is I have to create an Excel sheet, since you won't provide it. Sigh.

I'll look at this tomorrow. No idea what you're doing wrong but using Excel VBA to do this sounds like a disaster waiting to happen. How are you going to handle the token expiring while you're using it?

[Ankit_Dua]

Thanks again Sanford, I was able to fetch the data into excel. Thanks for your help.

Ahh, I haven't thought about the token expiring here, what could be the better option for this?

Thanks

[SanfordWhiteman · Accepted solution]

If Excel is your desired work environment, invest in the CData connector for Excel. While imperfect it's better than anything homebrewed, and will automatically manage your tokens. It also gives better performance as a compiled plugin.