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 documentationsays, "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 the final macro looks like in action...
For more information, visit https://community.alteryx.com/t5/Engine-Works-Blog/A-New-Kind-of-Macro/ba-p/260418