Connect to API using Client ID and Client Secret and Parameters

[shashibhushan86]

Dear Experts,

I am new to Alteryx and this is my first question here.

My organization has a data source on Azure Table storage Platform and I have been tasked to pull this data for the analysis purpose. The IT team has developed an api for me and the api documentation is like this:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GET DATA in CSV

https://api.organizationxyz.com/products/:product_code/regions?start_dt=2020-07-09T00:00:00%2B08:00&...

HEADERS
client_id     abcdefghijkl12345
client_secret     xyz98764123
Content-Type     text/csv
PARAMS
start_dt     2020-07-09T00:00:00%2B08:00
end_dt      2020-07-09T23:59:59%2B08:00
PATH VARIABLES
product_code     chairs

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I have searched so many places but cannot find any example which is connecting to an api source like this. For example most of the examples only cover the apis with no authentication and have never seen any example covering the Client ID and Client Secret for authentication.

Kindly help me get this data via Alteryx designer. I have an Idea that text input and Download tools will be used.

My approach was:

Put the URL in the text input tool and connect with download. I added the Client ID and Secret in the header section of download tool however this results in an error "Error: Download (8): Error transferring data: Couldn't connect to server"

I dont know how to test it. Any help would be appreciated.

Thanks in advance.

[ChiN]

hi @shashibhushan86 ,

I can't open the api link you provided, hence I wasn't able to read the documentation. 

What types of API is it? REST or OAUTH?

Learn here : https://community.alteryx.com/t5/Alteryx-Designer/APIs-in-Alteryx-cURL-and-Download-Tool/ta-p/31522 

Let me know if it helps.

Chi

[DavidP]

I agree with your approach of using a text input tool and download tool.

start off with the entire URL hard coded and once you have it working, you can try to bring in dynamic parameters, as shown below.

It's not clear how product code should be entered, so you may have to play around with that.

The authentication could be either via the header or in the Connection tab.

Once you get a successful connection to the api, you may have to play around with how the output is downloaded. If it's a file, you should probably create a filename field with the full path and filename you choose (like c:\temp\outputfile.csv) and select that option in the download tool.

[shashibhushan86]

Dear

[shashibhushan86]

Dear Chi,

Thanks for your reply. I changed the URL to mask it hence you cannot open it. But the actual documentation is exactly similar to what I have posted.

I also masked the Client ID and Secret. I was just looking for an approach as to how to build an alteryx mapping to download the data via api.

I dont know if its an REST or OAUTH api. I am assuming its OAUTH because as per my understanding thats what the Azure platform uses. I will anyways go back to the IT team and ask the same.

Also, I have gone through the link you shared, it doesnt cover the use of client id and secret to authenticate.

Please can you share any other resource?

Appreciate your help. Thanks in advance

[DavidP]

Sorry, I meant to attach a dummy example to my 1st post.

[ChiN]

Hi @shashibhushan86 ,

Thanks for providing more information. Here is the link to the resource explaining OAUTH .

For Oauth API, you will need to generate a token. You can use text to column building block and add columns such as URL, Client ID, Client Secret, and and any additional columns mentioned in the API documentation. 

Then you pass the token through download tool to get data from the endpoint. 

I hope it helps,

Chi

[shashibhushan86 · Accepted solution]

Thank you so much David. You put a lot of effort for this.

Just an update, I was able to connect to this api from R scripts (httr package GET function) and can retrieve the response. However that was on another machine.

Hence, I am thinking that there is a firewall issue which is blocking the connection. I will follow up with IT and let you know.

Thanks & Regards

[shashibhushan86]

Hi Chi,

Thank you so much. I was able to connect to this api via R script on a different machine. I am guessing there are some firewall issues with my alteryx machine. I will follow up with IT team and then try your solution.

Thanks & Regards

[shashibhushan86 · Accepted solution]

Thank you experts for your tremendous help.

I used Alteryx on a different machine where there was no firewall and implemented both approaches suggested here however I could not achieve the desired results.

I was able to solve the problem however and at the end R came to the rescue 🙂

Given below is the approach in detail:

1. I used R connector in Alteryx with below R code:

-----------------------------------------------------------------------------------------------------------------------------------------------

library(httr)
library(RJSONIO)

r <- GET("https://api.organization.com/products/productabc/location?start_dt=2020-07-09T23:59:00%2B08:00&end_d...", add_headers(client_id = "abcdefghijkl12345", client_secret = "xyz98764123", Content_Type = "text/csv"))

data <- content(r, "text")
data

json_file <- fromJSON(data)

json_file <- lapply(json_file, function(x) {
x[sapply(x, is.null)] <- NA
unlist(x)
})
data <- do.call("rbind", json_file)
data <- as.data.frame(data)

write.Alteryx(data, 1)

-----------------------------------------------------------------------------------------------------------------------------------------------

2. As you can see all of the JSON parsing was done in the R script itself hence I did not need any other parsing. Just use a browse tool and observer the output. That's it. Done. I got the output data exactly how i wanted.

This is how the workflow looked like:

I am sure there is definitely an easier and direct way to achieve this in Alteryx however as of now I can live with this solution. Please feel free to post your views to enhance everybody's knowledge.

I am going to close this thread for now and posted the solution for everyone's reference.

 Thank & Regards,