SOLVED

How to connect excel with API?

Go to solution
Ankit_Dua
Level 6

How to connect excel with API?

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

pastedImage_0.png

What should I do to fix this?

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

Re: How to connect excel with API?

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.

View solution in original post

5 REPLIES 5
SanfordWhiteman
Level 10 - Community Moderator

Re: How to connect excel with API?

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

Ankit_Dua
Level 6

Re: How to connect excel with API?

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
Level 10 - Community Moderator

Re: How to connect excel with API?

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
Level 6

Re: How to connect excel with API?

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
Level 10 - Community Moderator

Re: How to connect excel with API?

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.