Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Added functionality to the download tool for API queries

When building API calls within Alteryx there are a few common steps required

1) Build out the URI for the API call (base URL plus any query parameters)

2) Deal with authentication, such as basic authentication requires taking a key and secret, base 64 encoding and passing this into the tool

3) parsing the results out and processing these downstream

 

For this idea I am specifically focusing on step 3 (but it would be great to have common authentication methods in-built within the download tool (step 2)!).

 

There are common steps required to parse out the results, such as using Filter (to check for a 200 response), JSON parse, text to columns and then cross tab to get the results into a readable format. These will all be common steps anyone who has worked with APIs will be familiar with:

cgoodman3_2-1616585073736.png

 

This is all fine for a regular user to quickly add in and configure these tools. However there is no validation here for the JSON result being as expected, which when embedding an API into a batch macro or analytic app means it can easily fail.

One example of a failure which I've recently come across is where the output JSON doesn't have all fields (name:value pairs) depending the json response. For example using the UK Companies House API, when looking at the ceased to act field at this endpoint - https://developer-specs.company-information.service.gov.uk/companies-house-public-data-api/resources... the ceased to act field only appears in the results if a person has actually ceased to act. This is important if you have downstream tools such as a formula to create a field [Active] where you have:

IF ISNull([ceased_to_act]) THEN "Active" ELSE "Ceased to Act" ENDIF

However without modification the macro / app will error if any results are returned where there is not this field.

 

A workaround is to add in the Crew Ensure Fields or union on a list of fields, to ensure that the Cease to Act field is present in the output for all API calls. But looking at some other tools it would be good if an expected Schema could be built in to the download tool to do this automatically.

 

For example in Power Automate this is achieved as follows:

 

cgoodman3_1-1616584699689.png

 

I am a big advocate of not making things unnecessarily complicated. Therefore I would categorise this as an ease of use feature to improve the experience of working with APIs within Alteryx and make APIs (as load of integrations are API based) accessible to as many users as possible.

 

 

 

 

5 Comments
jarrod
12 - Quasar
12 - Quasar

Yes!

These are great comments about the download tool. I'd add that it doesn't fail gracefully either, if there is an error in libcurl, it just gives up instead of trying again or passing back a decent response. This throws the downstream process off and causes havoc. Would love to see the tool handle auth as well. Creating signatures is as pain most of the time that should be automated within the tool.

cgoodman3
13 - Pulsar
13 - Pulsar

How do you deal with with errors? I typically set multiple calls up in a batch macro and write out the results for each call. Then if there are failures I re-run against a directory tool to compare completed results against the original input, but this isn't very automated. 

jarrod
12 - Quasar
12 - Quasar

that's pretty much what I do as well. Depends on the endpoints (and what they return as "errors"). The one I can't really account for is the libcurl error from the Alteryx engine - at that point, Alteryx returns an error (i don't think it even returns a record) and then I have to guess which call failed.

 

Sometimes I can find it if I know what calls to expect, but other times i just re-run the full process. Shopify is one that is curious. it consistently fails with 404 errors (I think), so I just filter out 200's and run the workflow again hoping that the next time around it will pick it up. but that relies on the knowledge of what calls did and didn't make it through. The odd thing here is that an iterative macro doesn't work for recovering the bad calls; I have to copy the macro over several times in the same workflow and reprocess after filtering the good records.

 

I can't help but think that the issue is with how Alteryx deals with the API calls with curl. Hoping I can figure something out in Python and create a custom tool... or maybe a new official API tool... ahem 🙂

cgoodman3
13 - Pulsar
13 - Pulsar

I’m definitely for the download tool being split out to individual tools, such as a separate one for api calls, one for webscraping, one for downloading files from links. But I wouldn’t know how to, but happy to learn!

DanielG
10 - Fireball

Anything to make APIs easy to work with, I am all for.