Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
At Alteryx we like love to automate. When a recent project required the automated movement of data from a Quip spreadsheet to Designer, I looked to the Download tool.
Start with the download tool
In my experience, the toughest part of dealing with a REST API is authentication, but in this case Quip makes it incredibly simple. In no time I'd pulled down the above spreadsheet. Here is the download tool workflow:
I was expecting Quip to return something along the lines of a JSON object representing the spreadsheet, but instead they returned HTML - and lots of it! The API documentation says, "Once you have a token, the easiest way to use [the API] is via the Python Client Library, which makes most tasks a single line of code." So at this point I decided to switch gears to Python - I wanted an excuse to check out the new Python tool, anyway. To continue following along, you'll need the latest version (2018.3) of Designer, as the Python tool is brand new!
Next try the Python tool
Sure enough, it was pretty straightforward. First I downloaded the Python module provided by Quip. Then I fired up Designer, pulled down a Python tool into my workflow, and imported the quip module, along with a couple others I'll need...
from ayx import Alteryx import pandas import sys
# Path where I downloaded the quip module sys.path.append('C:/Users/nryan/Documents/Community/Blog/quip') import quip
Three lines of code leveraging quip helper methods got me most of the way there...
# Get your access token from https://quip.com/dev/token
client = quip.QuipClient(access_token="add your token here")
# Get your thread_id from the URL of your document spreadsheet = client.get_first_spreadsheet(thread_id="add your thread id here") parsedSpreadsheet = client.parse_spreadsheet_contents(spreadsheet)
At this point, a snippet of my parsedSpreadsheet object looks like this...
{'id': 'IIFACAn1LEm', 'headers': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'], 'rows': [{'id': 'IIFACA35dsI', 'cells': OrderedDict([('A', {'id': 's:IIFACA35dsI_IIFACAEjlSD', 'content': 'A'}), ('B', {'id': 's:IIFACA35dsI_IIFACA7vJxX', 'content': 'B'}), ('C', {'id': 's:IIFACA35dsI_IIFACAlPzQC', 'content': 'C'}),...
...which is a lot better than the raw HTML that we started with. Finally, I need to convert this object to a DataFrame, which is the type of data structure Alteryx is expecting when pushing data out of the Python tool to downstream tools. Here is how I did that (if you have a better way, let me know in the comments)...
counter = 0
spreadsheetData = []
colNames = []
for rows in parsedSpreadsheet["rows"]:
cells = rows["cells"]
rowData = []
for key, value in cells.items():
if counter == 0:
colNames.append(key)
rowData.append(value['content'])
spreadsheetData.append(rowData)
counter += 1
df_out = pandas.DataFrame(spreadsheetData, columns=colNames)
Alteryx.write(df_out,1)
Run the workflow and voila...
Now let's make a macro
I want the rest of my team to leverage this, and I don't want them to have to look at my Python code. I also don't want to hardcode my access token and thread ID into the workflow. So we'll now turn this workflow into a macro.
First, we'll feed a Text Input tool into the Python tool - the Text Input tool will have columns for the access token and thread ID. Then we add a couple of Text Box interface tools so the user can update those values - this is the step that transforms the workflow into a macro.
Add the following Python code to retrieve the user-specified values.
df_in = Alteryx.read("#1")
api_key = df_in["quip_token"][0]
thread = df_in["thread_id"][0]
We can now use the above variables in place of the hardcoded values.
We also need to make it so the macro user doesn't have to download the Quip-provided python module. I simply copy the Quip code into a cell ahead of my custom code. Here's what my final macro looks like in action...
A quick search of the Ideas page reveals there isn't any customer demand for a Quip connector, so Designer is unlikely to include an officially supported Quip tool any time soon. So this turns out to be a prime example of when you may want to roll your own custom tool. And when it comes to extending Alteryx, the Python tool is opening doors just like the R tool did once upon a time (currently, all of the predictive tools are macros built on top of the R tool).
Reskinning the tool with the HTML GUI SDK could enhance the functionality by allowing the user to interactively choose a spreadsheet available to them rather than having to supply a Thread ID. Converting the backend from a macro to a plugin with the Python SDK could improve performance. But there's nothing quite like being able to mash some Alteryx tools together with some Python code and having them work seamlessly together all from within Designer, an experience that is finally here.
