Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Knowledge Base

Definitive answers from Server experts.

Excel to Alteryx API

patrick_digan
17 - Castor
17 - Castor
Created

Have you ever built an Alteryx App only to realize that some part of the process must remain in Excel? Instead of abandoning your Alteryx App all together, you may be able to use the Alteryx API and call your app from Excel. We’ve put together all the necessary VBA code so that it should be quick and easy for you to call an Alteryx App from Excel and get the results back. The Alteryx app for our sample, API Tester.yxzp, has been included in case you want to upload it to your private gallery and test out the Excel to Alteryx API using your Alteryx server.


SAMPLE EXCEL FILE - Input


We are going to be working with the attached “Reverse String.xlsm” to demonstrate the API. Suppose you wanted to reverse a string in excel (“Hello World” should be converted to “dlroW olleH”). You can’t quite get it in Excel, but you could quickly use the ReverseString function in Alteryx. Now the only trick is getting your data from Excel to Alteryx and back to Excel. We’ll have the user enter the string on cell E5 and then hit the Run Alteryx Button. Cell E6 will then be the results we get back from Alteryx

1.png

HOW TO SETUP THE VBA

When working with the Alteryx API, you need to setup some settings/configurations in your excel file before you can deploy it. If you open up the VBA editor (Alt + F11), there is Sub called Doer where we will have to enter a few key settings that apply to your Alteryx App. This is a one time setup for each different application that you setup.

  • strKey – this is your unique Alteryx Key. It is unique to all the apps in your private studio. It can be found here for the Alteryx public gallery
  • strSecret – this is your unique Alteryx Secret. It is unique to all the apps in your private studio. It can be found here for the Alteryx public gallery

2.png

  • strAppID – this is your app’s unique ID. When you look at your app in a browser, the appID is the unique ID at the end of the URL. For our sample, our AppID “589b70eceffc2a0bb0a2d530” can be found at the end of the App’s URL

3.png

  • AlteryxURL – this is the base URL where the app resides. For the public gallery, it is : https://gallery.alteryx.com. You would switch this your URL if you were going to use your private gallery. A private gallery would be of the format resembling: https://server.domain.com/gallery.
  • Data – Any data that needs to be passed from Excel to Alteryx can be saved as string variables in the VBA. For our simple app, we’re only passing one item, the string that we want reversed. In order to pass more complex data, we’ve found it easier to save the data to a .csv and then have the Alteryx App read the .csv as part of the app.
  • strQuery – This is the entire set of information that is being sent from Excel to the API for processing. It’s in JSON format. For each item, you need a name and value. The name corresponds to the interface tool’s name in your Alteryx App. The value corresponds to the value that want to set. From our Alteryx App, you can see that we’ve given our textbox tool the name “input_string”. For the value, we’ll be passing whatever string the user enters into excel.

4.png

To see all the questions for a given app, the API documentation is immensely helpful. After you enter your key and secret, you would go to the second GET, type in the appID, and then try it out to get a list of the name value pairs that we must send from Excel to Alteryx. Note that you only send the name and value fields from the response body.

5.png

  • Save – This variable is a Yes or No answer for whether or not you want to save the data coming back from Alteryx. In this case we would say Yes. For more complex apps, we may have our Alteryx workflow save the data to a network folder that our excel VBA would go grab once Alteryx is done. That post run coding would be added under the Part_Deux sub in the VBA editor. There we would have Save=”No”
  • Output_Name – this is the column name of the output data that we want to retrieve from Alteryx. In our sample, the column Name is “Output_String”. This only applies if Save=”Yes”
  • SaveLocation – this is the cell range where the output should be written. This process currently only supports writing one cell of data back to excel. Anything more complicated should be handled through custom VBA in the Part_Deux sub. This only applies if Save=”Yes”

NOTES ABOUT WHAT IS GOING ON BEHIND THE SCENES

While I won’t go through everything that is going on in the background, here are some key points:

  • Ensure that you have references to Microsoft XML, 6.0 if any errors pop up.
  • The VBA takes all the settings and adds the time and a random string before posting a request to the Alteryx Server to run the job in the RunAlteryx sub. It has to combine all these items, URL encode certain parts, create a base64 hash, and send this to the Alteryx server in just the right order.
  • It then makes a Get request to check the status. If the status is complete, then the VBA moves on to get the results; otherwise, the VBA creates another request to check the status in 1 second. Note that Excel will be operational during this time since it’s not during any work. This loop will continue until the status is either completed or error. Note that it will update the status on the bottom left of your screen to indicate that Excel is waiting on Alteryx to run the job.
  • Once the Alteryx app returns a completed status, the VBA will then make another get request for the output that you’ve requested (only if save=”Yes”). If Save=”Yes”, the VBA then saves the output to the specified cell.
  • If you want to write any of your own code that should execute when the code is done, place it in the part_Deux sub. For example, perhaps you have a private server setup where the Server will write an output file to a network folder that excel can read in from. The code in part_Deux might look something like the below:

Dim ActBook As Workbook     Dim FromAlteryx As String           FromAlteryx = "\\server\" + Environ$("username") + "_out.csv"      Set ActBook = ThisWorkbook          ActBook.Activate     Worksheets("Data").Select     Cells.Select     Selection.ClearContents          On Error GoTo 0     Workbooks.Open FromAlteryx      Cells.Select     Selection.Copy     ActBook.Activate     Worksheets("Data").Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False          Workbooks(Environ$("username") + "_out.csv").Close

SAMPLE EXCEL FILE - Output

Assuming we have everything setup correctly, our app should return the reverse of the string that is input:

6.png

Attachments
Comments
joshuaburkhow
ACE Emeritus
ACE Emeritus

Thanks Patrick! I had a few libs to update and had to enable XML 3.0 as well but got it working and its AWESOME! :) 

jdsmith6
5 - Atom

Patrick,

This was exactly what I needed, when I needed it. Fantastic work! Works Amazingly well!

pablolarocca
5 - Atom

this is really good, thank

pablolarocca
5 - Atom
how can output a table with multiple columns?
clin62
6 - Meteoroid

@patrick_digan  Hey, thanks for such a detailed post, I tried to create my own, but when I log in my gallery. I only see Members and Artisans. There is no API section? Does that mean I can't do API on my Alteryx designer?

patrick_digan
17 - Castor
17 - Castor

@clin62 If your talking about the key and secret, when you use your browser to login to your gallery, you can click on the settings gear on the top right. You would get the api key and secret from the keys tab:

patrick_digan_1-1580852457245.png

If you're not API enabled, a gallery admin would need to use the gallery admin website to go to your subscription and check yes next to api enabled for you.

 

If you're looking for the api documentation, there should be a link like this at the bottom of your gallery:

 

patrick_digan_0-1580852382919.png

clin62
6 - Meteoroid

@patrick_digan  hey thanks for the response. I am pretty sure the account that I am using is the admin account, but I don't see "admin" selection under my icon on the gallery page. There is only "My profile" and "logout". I also tried to find a subscription page, but couldn't find it anywhere. Could you please help? My interface also seems different from yours.

 

clin62_0-1580854275648.png

 

 

patrick_digan
17 - Castor
17 - Castor

@clin62 Since there isn't a keys tab, you aren't API enabled. An admin for that gallery would have to set you up. Is that the public gallery by any chance (gallery.alteryx.com)? If so, you can reach out to support@alteryx.com to see if they can set you up with API access. I'm not sure if that's still something they do.

KFarr12
7 - Meteor

@patrick_digan Thanks for this Patrick. Really detailed and useful.

 

I have been able to successfully replicate this using my Alteryx server, my keys and your app, however I am having trouble replicating this for my own app.

 

I have updated the AppID and strQuery name (I use a textbox input as well) however I am returned xmlhttp.Status = 500 when I try to run the macro. This occurs in the RunAlteryx sub so I think it isn't able to post the job.

 

My app is slightly difference in that the input string is a UNC file path. The app uses this filepath to pass data from an excel file to subsequent workflows. However I can't see how/if this would complicate the code provided.

 

Any ideas why I'm being returned xmlhttp.Status = 500?

 

Thanks

SPrykucki
5 - Atom

@patrick_digan do you have an updated version of this example leveraging Oauth2.0 for Server versions after 2021.4?

lepome
Alteryx Alumni (Retired)

@patrick_digan I don't know that the above comment successfully tagged you.

@SPrykucki  It might be useful to leverage the tools described in this article:  Introducing the Alteryx Server v3 API     

As those tools are macros, it should be reasonably straightforward to edit them if you need to customize them for your use case.

patrick_digan
17 - Castor
17 - Castor

@SPrykucki I don't have anything that I can post publicly at the moment, but let me see if I can put that together. I do have the VBA code for Oauth2.0 working, but it's built into a bigger template that I'll need to peel back so I can post something here. If you're trying to do it yourself and run into any issues, I'd be happy to try and assist!