10-12-2017 04:12 PM - edited 05-21-2021 12:15 PM
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
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.
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.
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:
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:
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! :)
Patrick,
This was exactly what I needed, when I needed it. Fantastic work! Works Amazingly well!
this is really good, thank
@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?
@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:
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 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 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.
@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
@patrick_digan do you have an updated version of this example leveraging Oauth2.0 for Server versions after 2021.4?
@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.
@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!