URL Encoding and Decoding for Iterative Macro API Calls

[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!

Someone suggested I use the Formula UrlEncode ability . . .

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 Parse Tab?)

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

3. Fix the formula UrlEncode

URLDecode.yxmc