Cyclic APIs and URL Encoding in an Iterative Macro.

[jrobiso2]

Many of today's APIs, like MS Graph, won't or can't return more than a few hundred rows of JSON data. Usually, the metadata returned will include a complete URL for the NEXT set of data.

Example:  https://graph.microsoft.com/v1.0/devices?$count=true&$top=999&$filter=(startswith(operatingSystem,'W...') or startswith(operatingSystem,'Mac')) and (approximateLastSignInDateTime ge 2022-09-25T12:00:00Z)

This will require that the "Encode URL" checkbox in the download tool be checked, and the metadata "nextLevel" output will have the same URL plus a $skiptoken=xxxxx value.  That "nextLevel" url is what you need to get the next set of rows.

The only way to do this effectively is an Iterative Macro . 

Now, your download tool is "encode URL" checked, BUT the next url in the metadata is already URL Encoded . . . so it will break, badly, when using the nextLevel metadata value as the iterative item.

So, long story short, we need to DECODE the url in the nextLevel metadata before it reaches the Iterative Output point . . . but no such tool exists.

I've made a little macro to decode a url, but I am no expert. Running the url through a Find Replace tool against a table of ASCII replacements pulled from w3school.com probably isn't a good answer.

We need a proper tool from Alteryx!

[DavidSta]

Hi @jrobiso2,

I totally get your idea and it might be worth to post within the Ideas Section of the Community.

On the other hand this macro is good.

As an idea on how to ship around this problem you can use as a first step, before going into the Download Tool, a Formula with UrlEncode() function to achieve the same as the checkbox does.

Best regards,

David

[jrobiso2]

Unfortunately, the Formula UrlEncode does NOT work. It encodes things based upon a straight ASCII conversion table, and therefore it encodes things like ? and $ when it should not.  Whoever is responsible for that code in the formula tool needs to re-visit it.

Base URL: https://graph.microsoft.com/v1.0/devices?$count=true&$top=999&$filter=(startswith(operatingSystem,'W...') or startswith(operatingSystem,'Mac')) and (approximateLastSignInDateTime ge 2022-09-25T12:00:00Z)

Correct Encoding: 

https://graph.microsoft.com/v1.0/devices?$count=true&$top=999&$filter=(startswith(operatingSystem%2c...

Formula UrlEncode:

https://graph.microsoft.com/v1.0/devices%3F%24count%3Dtrue%26%24top%3D999%26%24filter%3D%28startswit... . . . . . (truncated output).

As you can see, the initial "?" was altered, as were all the "$", and the result is rejected by the http server. Also, the spaces around "and" and "or" should convert to + in this case, but I THINK the standard %20 would also work.

So, I don't know what the Download Tool is doing when it encodes, but it does a good job at it. Formula UrlEncode, while one would expect it to do the same encoding as the Download Tool, is not and therefore fails.

So, I have a couple of asks:

1. Copy the code from inside the Download Tool for Encoding and make it into a separate tool. (UrlEncode Tool, perhaps in Developer Tab?)

2. Using that copied code but in reverse, create a Url Decode Tool. (UrlDecode Tool, again - perhaps in Developer Tab?)

3. Fix the formula UrlEncode

[jrobiso2]

Also, it appears that after a random number of iterations, the Iterative Macro will "burp" and apparently try to run on a blank iterative value. This makes it essentailly impossible to pull an entire data set from Graph without a failure.

Has anyone else noticed this problem?