A New Kind of Macro

[NeilR]

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

The test spreadsheet

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:

 The simple request workflow and the nightmare response

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...

Success! The output data matches the Quip spreadsheet contents.

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.

Download tool.yxmd

Python tool.yxmd

Quip Spreadsheet Input.yxmc

[kevinmlee]

@NeilR thank you for this! I'm trying to get this to work with a quip spreadsheet that has multiple tabs, but it doesn't import the first tab. It picks one in the middle. Do you have any ideas on how to correct this?

[NeilR]

@kevinmlee we're using the quip function get_first_spreadsheet (see here). The meat of this function is as follows:

return self._get_container(thread_id, document_html, "table", 0)

There's also a function called get_last_spreadsheet that contains:

return self._get_container(thread_id, document_html, "table", -1)

So I think you basically need to increment that last argument until you get the table you're looking for.

[rladdha]

Thanks for this article. Could you please tell as to how we can get the macro output exported to an excel file, so that we can use the same in tableau for creating visualization. Once exported to excel, if we make any changes to the file and go on to refresh the macro will that also get updated

[NeilR]

@rladdha to export to Excel simply place an Output Data tool after the Quip tool and select Excel as the output format. (You could also write to .tde or .hyper Tableau files instead of Excel.) Then every time you run the workflow the files should be up to date.

[findingnemo]

@NeilR Thanks for this article. I tried out the python module but got this error - 

ModuleNotFoundError: No module named 'quip'

 I saved the quip.py script as a PY file and referenced it the same way per your workflow. Am I missing anything else?

[NeilR]

@findingnemo, Did you update the path below to point to where you saved the PY file?

sys.path.append('C:/Users/nryan/Documents/Community/Blog/quip')

If that doesn't work, you could always just copy/paste the script into your notebook and skip the import line.