How to connect excel with API? | Community
Skip to main content
A
AnkitDu3
New Participant
February 2, 2019
Solved

How to connect excel with API?

  • February 2, 2019
  • 1 reply
  • 6854 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by SanfordWhiteman

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


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.

1 reply

SanfordWhiteman
SanfordWhiteman
New Participant
February 2, 2019

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

A
AnkitDu3Author
New Participant
February 3, 2019

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
SanfordWhiteman
New Participant
February 3, 2019

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?

Terms & ConditionsAccessibility statement

Loading footer...