Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
Alteryx Community Team
Alteryx Community Team

Almost two years ago, @Ned made a tool that emulates the Alteryx Formula tool, but instead of building an Alteryx expression, you write JavaScript code. About a year later, as part of our Innovation Days - two days each quarter for the development organization to work on their own side projects - I made a similar tool, this time inspired by the R tool rather than the Formula tool. Ned blogged about his tool to celebrate the unofficial release of the HTML SDK. As we get closer to the official release of the SDK later this year, I decided to dust off the code I wrote a year ago and share it here. In this post I'll describe how you can use this experimental tool. I'll follow up with a second post about how I created the tool's user interface, and then finish the series with a third post explaining how the back-end works.

 

Installation

Download the yxi file. Open the file in Designer (or just double click on the file) to install. The tool will then appear in the Developer category of the tool palette.

 

How to use it

The tool's interface is fairly simple. There is a text box to list script files you'd like to include (like jQuery), a code editor, and a button that allows you to insert into the code editor functions that allow your code to interact with Alteryx. When you insert these functions they come pre-commented with a sentence or two about what the function does. Those comments (along with this post) constitute the tool's documentation at this point. 

 

The fastest way to get the tool to do something is as follows, and actually requires no typing:

  1. Drag the tool onto the canvas.
  2. Hover over the "Insert Code" button and click "Write Metadata".
  3. Then click "Write Data".
  4. Run the workflow.

js1.gif

 

The first function you just inserted, writeAlteryxMetadata, tells Alteryx what fields are going to come out of the JavaScript tool. The second function, writeAlteryxData, pushes data out of the tool. Both functions come pre-populated with arguments that show the format of the data that the functions are expecting. There's a third function, notifyAlteryxComplete, pre-populated at the bottom of the code editor, that tells the tool you're done. You've just created a script that will output two rows of data!

 

Here's how you configure the tool to act as a connector to an API, here using jQuery:

Capture.PNG

 

Here's code demonstrating how to deal with data coming into the tool - this just pushes back out the same data that's coming in:

writeAlteryxMetadata(readAlteryxMetadata())
writeAlteryxData(readAlteryxData())
notifyAlteryxComplete()

And here's code that makes the tool add 1 to the incoming data:

var incomingData = readAlteryxData()
writeAlteryxMetadata([{name:'plus1', type:'Double'}])
for (i = 0; i < incomingData.length; i++) {
   writeAlteryxData([[incomingData[i][0] + 1]])  
}
notifyAlteryxComplete()

All of the above example usages are included in the workflow attached to this post.

 

The top two use cases I can think of for why this tool would be useful are:

  1. To use as a connector to download data from web REST APIs. The Download tool is awesome, and most of the time it will be easier to use than the JavaScript tool, even for experienced JS developers. But sometimes APIs are tricky to use and you might just want to copy and paste some JS code from a sample that the API docs provide, rather than try and figure out how to get it working with the Download tool.
  2. To use as a JSON parser to sift through complex objects. Again, the JSON Parse tool will often be easier to use for this purpose than this tool, but if you have a very complex object and you need to get at some data that's deeply nested inside the object, this tool (along with Lodash) could help. After all, the JS in JSON stands for JavaScript.

There are so many JS libraries out there that I'm sure there's a bunch or things you can do with this tool in Alteryx that I couldn't even dream of. I'm hoping some of you come up with other interesting use cases - let me know in the comments.

 

P.S.

Why do I call this tool "experimental"? Cuz it has issues. One is that error handling is tricky. Designer could hang if you don't properly handle errors and have a notifyAlteryxComplete function call at the end of every code path. I try to take care of this for you in the back-end (wait for part III for more detail on this), but for ajax, you'll need to do this on your own.

Here's an example: 

$.get({
  url: "http://dev.arkitondemand.com/MODApis/Api/v2/Quote/json?symbol=AYX",
  dataType: "json",
  success: function (data) {
    writeAlteryxMetadata([{name:'JSON', type:'String', size:1000}]);
    writeAlteryxData([[JSON.stringify(data)]]);
    notifyAlteryxComplete();
  }
})

In the code snippet above, the URL is misspelled. I've written "arkitondemand" instead of "markitondemand".  Because I don't have an error callback function, the notifyAlteryxComplete function never gets called, and Alteryx hangs - you need to manually cancel the workflow or else it will keep running forever. So be careful!

The right way to do this is as follows:

$.get({
  url: "http://dev.markitondemand.com/MODApis/Api/v2/Quote/json?symbol=AYX",
  dataType: "json",
  success: function (data) {
    writeAlteryxMetadata([{name:'JSON', type:'String', size:1000}]);
    writeAlteryxData([[JSON.stringify(data)]]);
    notifyAlteryxComplete();
  },
  error: function (err) {
    logAlteryxError(JSON.stringify(err));
    notifyAlteryxComplete();
  }  
})

 

P.P.S.

Shout out to CodeMirror and W3Schools - more about how they helped in part II.

 

Another shout out to @jdunkerley79 - if you want more information about how the HTML SDK works but can't wait for the official release, or even my next post, check out his recent post here.

Neil Ryan
Sr Program Manager, Community Content

Neil Ryan is the Sr Program Manager, Community Content, responsible for the content in the Alteryx Community. He held previous roles at Alteryx including Advanced Analytics Product Manager and Content Engineer, and had prior gigs doing fraud detection analytics consulting and creating actuarial pricing models. Neil's industry experience and technical skills are wide ranging and well suited to drive compelling content tailored for Community members to rank up in their careers.

Neil Ryan is the Sr Program Manager, Community Content, responsible for the content in the Alteryx Community. He held previous roles at Alteryx including Advanced Analytics Product Manager and Content Engineer, and had prior gigs doing fraud detection analytics consulting and creating actuarial pricing models. Neil's industry experience and technical skills are wide ranging and well suited to drive compelling content tailored for Community members to rank up in their careers.

Comments
11 - Bolide

 This could be useful for scraping web pages and using the various DOM methods available in javascript/jquery.

 

I usually just use the DL tool or the CMD tool with a headless browser to pull down the rendered page and the use regex to hack out what I need.

 

JS would certainly be more efficient but then again I would have to dust off my limited JS "skills" which makes me sad.

Alteryx Alumni (Retired)

I wanted to update this thread to let you know that we have new SDK documentation available for our HTML GUI SDK, which was released in as a beta in 11.5. You can find documentation for both the HTML GUI SDK online. We'd love for you to provide your feedback here

11 - Bolide

Hey Neil - 

 

How could we tweak this tool to be used to in conjunction with an interface drop down box to generate a list of choices for a user once they've started typing?  I had a situation wherein I need a gallery user to select an address, but there are ~ 100K of them, so populating a drop down doesn't help.  If they type the wrong address, the workflow will run, but then come back with nothing and/or an error message, 'are you sure about this address'.  But the reality is our address data is horrible and it's easy to miss fire.  I needed to give our users the ability to start typing, see a list of addresses that made sense, choose one, and then start blasting away with the workflow.   

 

The eventual solution I came up with was writing an ajax call on a standalone js page to goto the database once the user started typing, give them a list of matches that I knew were in the database, and then on selection, passing that value on to a service to invoke calls to gallery using the API / Open Auth, passing the resultant messages and files back to the html page and serving up to the browser.  A total hassle to get around what on the face of it is a very simple web operation, auto complete.  

 

I'm also going to post to the ideas forum.  

 

Thanks. 

brian

 

 

Alteryx Community Team
Alteryx Community Team

@brian_scott the HTML SDK is used to build dynamic interfaces for tools in workflows (like the tool described in this post). I think your approach of building a custom interface for an analytic app by leveraging the Gallery API is correct.

8 - Asteroid

Great article, thanks @NeilR, I am dabbling in Javascript and thought this tool could be exactly what I needed for a particularly troublesome API. How can a user specific run-times and libraries like Node.js? Is it similar to how you're importing jquery?

 

Also, does this tool support ES6?

 

Edit: on a related note, is there any more documentation on how to output the other fields on the same record that some javascript data manipulation was performed on? It's not quite clear how this is done currently. I am simply returning "[field] has length of 0" errors

 

Alteryx Community Team
Alteryx Community Team

@aMac

 


How can a user specific run-times and libraries like Node.js? Is it similar to how you're importing jquery?

node.js is a JavaScript runtime environment, not a library like jquery. Alteryx executes its JS code in a different runtime environment - Chromium. Node is not supported.

 

Also, does this tool support ES6?

Chromium supports ES6 as of version 52. You can check what version of Chromium Alteryx is running by adding logAlteryxMessage(navigator.userAgent) to the JavaScript tool. The version will appear in the log. My version of Alteryx (2018.3) is running Chromium v62...

 

log1.PNG

 

on a related note, is there any more documentation on how to output the other fields on the same record that some javascript data manipulation was performed on? It's not quite clear how this is done currently. I am simply returning "[field] has length of 0" errors

The readAlteryxData() function returns the incoming data as an array of arrays - the higher level array elements representing the records and the embedded array representing each field within. So incoming data that looks like this...

 

data1.PNG

 

...is represented like this:

data2.PNG

So if you wanted to multiply the first column by 2, and leave the other columns alone:

metaData = readAlteryxMetadata()

data = readAlteryxData()
logAlteryxMessage(data)

for (i = 0; i < data.length; i++) {
	data[i][0] = data[i][0]*2
} 

writeAlteryxMetadata(metaData)
writeAlteryxData(data)

notifyAlteryxComplete()

 

8 - Asteroid

How do you print to the console window

Alteryx Community Team
Alteryx Community Team

@joejoe317logAlteryxMessage('Example message')

 

message.PNG

8 - Asteroid

@NeirlR 

Thanks, I actually should have deleted my post, I saw the example right above that (even though I read it over before hand). Really nifty tool!

8 - Asteroid

Modded this to create multi input to do smarter joins etc.. only issues so far with the tool is that there isn't downstream data, so you have to run the workflow to get data past this tool + the next one. 

 

@NeilR are you okay with me uploading it here?

Alteryx Community Team
Alteryx Community Team

@joejoe317 

Spoiler
Yes
8 - Asteroid

@NeilR 

Can't find attachment button, only insert link, will have to try and remember to do it later... can you only attach if its the main post?

 

Anyway, if so I will get back to this to upload to google drive or something. 

Alteryx Community Team
Alteryx Community Team

@joejoe317 yeah you can only attach to the main post - I think a google drive link is the way to go. Looking forward to seeing what you've come up with!

clipboard_image_0.jpeg

8 - Asteroid

@NeilR 

 

Here is the link to the JavaScriptMulti tool..

 

When I get time I would like to get downstream working, its a real big pain at the moment, but I have work to get done so I can't work on it, if I do I will update it.

 

https://drive.google.com/open?id=1LRVjilo-J4C7I9A9g4vJVMqpTiw-8K3n

 

update: added public link

8 - Asteroid

@NeilR I love this!  I stumbled upon it recently and start messing with it.

 

I may be overlooking something, and I'm not JavaScript expert by any means (I just dabble), but how would run return the response text from a POST in an Alteryx field?

 

I can't seem to get any field return when running the following:

$.ajax({
  url: "http://sharePoint/sites/MySharePointSite/_api/contextInfo",
  method: "POST",
  headers: { "Accept": "application/json; odata=verbose"},
  success: function (data) {
    writeAlteryxMetadata([{name:'JSON', type:'V_String', size:2147483647}]);
	writeAlteryxData([[JSON.stringify(data)]]);    
    $('#__REQUESTDIGEST').val(data.d.GetContextWebInformation.FormDigestValue);
    
    notifyAlteryxComplete();
  },
  error: function (data, err) {
    logAlteryxError(JSON.Stringify(err));
    notifyAlteryxComplete();
  }  
});

I'm referencing the jquery cdn in script files.  I'm able to run it fine with $.Get or "GET" in an ajax call but can't get a field return on Post. 

8 - Asteroid

@jmstampe 

Edit: Cross domain issue... Request Digest in the form of $('#__Request...) is looking for an id on the dom to get the token...
in our case we are cross-domain, so you will need a seperate library when dealing with tokens outside of sharepoint..

This is what I found in the past 

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/complete-basic-operations-using-sharepoin...

Scroll down until you see the javascript example without the $('#__Request_Digest') it should be for crossdomain applications..

in theory this is what validates the ability to post to sharepoint and is in the dom itself.. in alteryx we dont have access to the dom, so what we have to do 

is get the token in a different way....

feel free to read what I said below, but it doesn't have much relevance to the solution, only shows different ways you can use newer syntax so you dont have to rely on jquery. and imo is much cleaner...
If this is what you are used to writing in, I would look up promises, fetch, and async await functions.. much cleaner so you don't get stuck in a lot of callbacks.

 

I will have to test, but first and foremost.. on a javascript aspect, you are using jquery on that ajax call, so you would want to include the jquery library.

However, you may want to look into fetch() 
Why is this better? if you work with a lot of sharepoint and need to join lists to other lists where the relationship is further than 1 list away, sometimes I had 4 or 5 ajax calls nested in eachother which isnt fun or cool. async await will allow you to act like they are sequential like other sharepoint code, but it actually waits for the item to be done before moving on to the next item.. Promises work the same way as well, and the nice thing is they work with functions as well. 
in your case it would look something like this..

 

 

 

 

//fetch method
fetch("http://sharePoint/sites/MySharePointSite/_api/contextInfo",{
  method: "POST",
  headers: { "Accept": "application/json; odata=verbose"},

}).then(data=> {
    writeAlteryxMetadata([{name:'JSON', type:'V_String', size:2147483647}]);
	writeAlteryxData([[JSON.stringify(data)]]);   
 //you can actually loop through the data and not have to stringify it..
// typically sp is something like data[0].d.results so..
let newData = data[0].d.results;
let alteryxData = []
newData.forEach(k=>{
//manipulate data
//fill in missing fields etc, example...
//choose Title, person, store, cost
// desctructure object with prop names (same as creating object) with same prop name and //takes values..
let {Title,person,store,cost} = k;
//assign props to a variable;
let rowData = {Title,person,store,cost};
//pushing the values of the object properties in the correct order
//alteryx rows are just values only.. this means the metadata will need to be setup
//like Title,person,store,cost wiith the correct types etc...
ateryxData.push(Object.values(rowData));
})
   // $('#__REQUESTDIGEST').val(data.d.GetContextWebInformation.FormDigestValue); 
// this is an issue because you have to be logged in to get the request digest
// alteryx typically doesnt show on same network i think because I had a hard time here
    
    notifyAlteryxComplete();
  }).catch(err=>{

    logAlteryxError(JSON.Stringify(err));
    notifyAlteryxComplete();
   });

//async await method
async function run(){
var aData = []
try{
let spData = await fetch("http://sharePoint/sites/MySharePointSite/_api/contextInfo",{
  method: "POST",
  headers: { "Accept": "application/json; odata=verbose"},

})
spData.forEach(k=>{
let {Title,person,store,cost} = k;
let data = {Title,person,store,cost}; 
aData.push(Object.values(data))
    notifyAlteryxComplete();

})
return aData;
}
catch(e){
    logAlteryxError(JSON.Stringify(e));
    notifyAlteryxComplete();
}

}
run()

 

 

 

 

 

sweet, after reading all the stuff I didnt see your note on the bottom.. Im pretty sure you cant post because of permissions.. request digest form is called from an ID it looks like hence the # sign.. you will need to aquire the requestdigest from a different method since we arent working with a dom on sharepoint..

At somepoint I had a method to query this outside of sharepoint, but I believe I had to use the sharepoint library, I have since moved away from sp and started using my own server and oracle for storing data, but I will see if I can fish some things up.