Nintex Workflow – Connecting to Smartsheet UDA
Between June 1st-3rd 2015, DocuSign is running their Momentum conference in London. One of my colleagues, Sean Fiene (a fellow Technical Evangelist) got to attend the San Francisco version of that conference a few months ago. This time around, Nintex is a Gold Spnsor, along with a company named Smartsheet. Now I had never heard of Smartsheet, since I don’t do a lot in the project management space. But I thought, since we are co-sponsors, I should look into it a little more. Turns out that they have an interesting SAAS offering and what really peaked my interest, is the fact that they have an API.
Being the nerd that I am, I wanted to check it out and see what I could do from a Nintex Workflow standpoint, to talk to it.
Let’s say that you run your business around SharePoint and have a bunch of automated processes. At the same time, you are using Smartsheet for your project management. Now you can use Nintex Workflow for your business process automation and start building out processes to talk to Smartsheet, to get data out of the sheets. This is pretty exciting. I really like having any product interacting with another product, because it shows what the flexibility and connectivity can bring to improve your business. No longer do you need to keep copies of data in multiple system. Now, you can simply have Nintex Workflow communicate to your other line-of-business systems and get data at runtime.
NOTE: This does require that your SharePoint environment can see the internet. Since Smartsheet is a SAAS offering, the Nintex Workflow logic uses the Web Request action to talk to it, which requires connectivity outside your SharePoint farm.
Requirements
In order to talk to Smartsheet, you need to have an account. Why would you be reading this if you didn’t already have one right??
Then, you need to get yourself an API Access Token. This is required for you be able to use their API. Here is a link to the Smartsheet API Documentation.
All the REST calls are explained there. I will only be making a call to a couple of them. But it should give you a good idea as to how use this.
From Nintex Workflow, the UDA uses two workflow constants.
- Smartsheet Credentials – credentials required to access the Smartsheet site
- Smartsheet API Token – the api access token from the Smartsheet site
I made these into Nintex Workflow constants, because if they change, I want to simply change the constant instead of the UDA and have republish the UDA and the Workflows that use it.
User Defined Action
Although this post is about a UDA which is only supported in NW2010 and NW2013, the functionality behind this would actually work better in Nintex Workflow for Office 365. This is be cause the REST calls to Smartsheet, return JSON data, which is a lot easier to parse using a Dictionary variable type that is available in Nintex Workflow for Office 365.
The UDA design, looks a little like this:
Not the most elegant design and I usually hate this type of design, as I like to utilize State Machines and Run Parallel Actions etc. But for this purpose, this will do. The above picture is only a chunk of the UDA design. There are a few more actions after this, but it looks the same.
The purpose of the UDA is to do a few things.
- We want to be able to get data from a particular row and column in a Smartsheet that we know and understand.
- Since the call to Smartsheet requires you to know the sheet ID, we first need to make a call to get all the Sheets. That way, we can iterate through each one until we find the one we need.
- Once we have done that, and we have the sheet ID, we can make another call to the Smartsheet API to get the data specifically about that sheet (including Column and Row data).
So the parameters for the UDA look like this :
It takes one input parameter, which is the name of the sheet and two output parameters which are the Column Data and the Row Data. Ideally, you could also get back the Sheet ID so that you can store it somewhere, to reuse later.
The data that comes back, Column and Row data is still in JSON format. Why???? This UDA was meant to be somewhat reusable, so we get back JSON data and then potentially could make another UDA that gets back the data from within that.
I warn you now, with the on-prem version of Nintex Workflow, there’s no easy way of parsing JSON data. (I’ve passed that on to the appropriate people). So you need to do some cryptic regular expressions to get data out of it. But it’s not rocket science, and the examples in the UDA should give you an idea on how to do it.
I created a simple sheet and it looks like this:
As you can see, I have 3 columns, 3 rows and the Gantt chart enabled and it’s called a “TestSheet”.
The first call to the Smartsheet API is to get all the sheets available. I have not tested this out if you have a whole bunch of sheets and what the performance is. That is another reason that if you’re making multiple calls to the same sheet, make sure you store the Sheet ID. This is the Web Request looks like:
Make sure to take note of the URL, since that changes depending no what data you want to get back. Also, you need to add a header to this call, which is the Authorization where you put in the Smartsheet Access Token.
The data that comes back from is, looks like this :
[{“id”:7946975606794116,”name”:”TestSheet”,”accessLevel”:”OWNER”,”permalink”:”https://app.smartsheet.com/b/home?lx=aqJjtpsltZ8xna0xNL-LrA”}]
Again, notice this is in JSON format so to pull out the “id”, we can use a Regular Expression action like this ;
If you want to know what that Regular Expression is, please take a look at this post : Extract text from between two text strings
On the next Web Request call, the response is even bigger, because there’s so much more data there and it looks like this :
{“id”:7946975606794116,”name”:”TestSheet”,”version”:1,”totalRowCount”:3,”accessLevel”:”OWNER”,”effectiveAttachmentOptions”:[“DROPBOX”,”FILE”,”BOX_COM”,”GOOGLE_DRIVE”],”ganttEnabled”:true,”dependenciesEnabled”:false,”resourceManagementEnabled”:false,”permalink”:”https://app.smartsheet.com/b/home?lx=aqJjtpsltZ8xna0xNL-LrA”,”createdAt”:”2015-06-02T08:05:04-06:00″,”modifiedAt”:”2015-06-02T08:10:40-06:00″,”columns”:[{“id”:8864342134286212,”index”:0,”title”:”Primary Column”,”type”:”TEXT_NUMBER”,”primary”:true,”tags”:[“GANTT_DISPLAY_LABEL”],”width”:150},{“id”:279355344611204,”index”:1,”title”:”Start Date”,”type”:”DATE”,”tags”:[“GANTT_START_DATE”],”width”:150},{“id”:4782954971981700,”index”:2,”title”:”End Date”,”type”:”DATE”,”tags”:[“GANTT_END_DATE”],”width”:150},{“id”:2531155158296452,”index”:3,”title”:”Percentage Complete”,”type”:”TEXT_NUMBER”,”tags”:[“GANTT_PERCENT_COMPLETE”],”width”:150},{“id”:7034754785666948,”index”:4,”title”:”Column5″,”type”:”TEXT_NUMBER”,”width”:150},{“id”:1405255251453828,”index”:5,”title”:”Column6″,”type”:”TEXT_NUMBER”,”width”:150}],”rows”:[{“id”:2326133376083844,”rowNumber”:1,”expanded”:true,”createdAt”:”2015-06-02T08:10:40-06:00″,”modifiedAt”:”2015-06-02T08:10:40-06:00″,”cells”:[{“columnId”:8864342134286212,”type”:”TEXT_NUMBER”,”value”:”aaa”,”displayValue”:”aaa”},{“columnId”:279355344611204,”type”:”DATE”,”value”:”2015-06-02″},{“columnId”:4782954971981700,”type”:”DATE”,”value”:”2015-06-05″},{“columnId”:2531155158296452,”type”:”TEXT_NUMBER”,”value”:0.5,”displayValue”:”0.5″}]},{“id”:6829733003454340,”rowNumber”:2,”siblingId”:2326133376083844,”expanded”:true,”createdAt”:”2015-06-02T08:10:40-06:00″,”modifiedAt”:”2015-06-02T08:10:40-06:00″,”cells”:[{“columnId”:8864342134286212,”type”:”TEXT_NUMBER”,”value”:”bbb”,”displayValue”:”bbb”},{“columnId”:279355344611204,”type”:”DATE”,”value”:”2015-06-02″},{“columnId”:4782954971981700,”type”:”DATE”,”value”:”2015-06-12″},{“columnId”:2531155158296452,”type”:”TEXT_NUMBER”,”value”:0.2,”displayValue”:”0.2″}]},{“id”:1200233469241220,”rowNumber”:3,”siblingId”:6829733003454340,”expanded”:true,”createdAt”:”2015-06-02T08:10:40-06:00″,”modifiedAt”:”2015-06-02T08:10:40-06:00″,”cells”:[{“columnId”:8864342134286212,”type”:”TEXT_NUMBER”,”value”:”ccc”,”displayValue”:”ccc”},{“columnId”:279355344611204,”type”:”DATE”,”value”:”2015-06-03″},{“columnId”:4782954971981700,”type”:”DATE”,”value”:”2015-06-05″},{“columnId”:2531155158296452,”type”:”TEXT_NUMBER”,”value”:0.75,”displayValue”:”0.75″}]}]}
That’s kind of ugly right? So using one of the online beautifiers, it would look like this :
{
"id": 7.9469756067941e+15,
"name": "TestSheet",
"version": 1,
"totalRowCount": 3,
"accessLevel": "OWNER",
"effectiveAttachmentOptions": [
"DROPBOX",
"GOOGLE_DRIVE",
"BOX_COM",
"FILE"
],
"ganttEnabled": true,
"dependenciesEnabled": false,
"resourceManagementEnabled": false,
"permalink": "https:\/\/app.smartsheet.com\/b\/home?lx=aqJjtpsltZ8xna0xNL-LrA",
"createdAt": "2015-06-02T08:05:04-06:00",
"modifiedAt": "2015-06-02T08:10:40-06:00",
"columns": [
{
"id": 8.8643421342862e+15,
"index": 0,
"title": "Primary Column",
"type": "TEXT_NUMBER",
"primary": true,
"tags": [
"GANTT_DISPLAY_LABEL"
],
"width": 150
},
{
"id": 2.793553446112e+14,
"index": 1,
"title": "Start Date",
"type": "DATE",
"tags": [
"GANTT_START_DATE"
],
"width": 150
},
{
"id": 4.7829549719817e+15,
"index": 2,
"title": "End Date",
"type": "DATE",
"tags": [
"GANTT_END_DATE"
],
"width": 150
},
{
"id": 2.5311551582965e+15,
"index": 3,
"title": "Percentage Complete",
"type": "TEXT_NUMBER",
"tags": [
"GANTT_PERCENT_COMPLETE"
],
"width": 150
},
{
"id": 7.0347547856669e+15,
"index": 4,
"title": "Column5",
"type": "TEXT_NUMBER",
"width": 150
},
{
"id": 1.4052552514538e+15,
"index": 5,
"title": "Column6",
"type": "TEXT_NUMBER",
"width": 150
}
],
"rows": [
{
"id": 2.3261333760838e+15,
"rowNumber": 1,
"expanded": true,
"createdAt": "2015-06-02T08:10:40-06:00",
"modifiedAt": "2015-06-02T08:10:40-06:00",
"cells": [
{
"columnId": 8.8643421342862e+15,
"type": "TEXT_NUMBER",
"value": "aaa",
"displayValue": "aaa"
},
{
"columnId": 2.793553446112e+14,
"type": "DATE",
"value": "2015-06-02"
},
{
"columnId": 4.7829549719817e+15,
"type": "DATE",
"value": "2015-06-05"
},
{
"columnId": 2.5311551582965e+15,
"type": "TEXT_NUMBER",
"value": 0.5,
"displayValue": "0.5"
}
]
},
{
"id": 6.8297330034543e+15,
"rowNumber": 2,
"siblingId": 2.3261333760838e+15,
"expanded": true,
"createdAt": "2015-06-02T08:10:40-06:00",
"modifiedAt": "2015-06-02T08:10:40-06:00",
"cells": [
{
"columnId": 8.8643421342862e+15,
"type": "TEXT_NUMBER",
"value": "bbb",
"displayValue": "bbb"
},
{
"columnId": 2.793553446112e+14,
"type": "DATE",
"value": "2015-06-02"
},
{
"columnId": 4.7829549719817e+15,
"type": "DATE",
"value": "2015-06-12"
},
{
"columnId": 2.5311551582965e+15,
"type": "TEXT_NUMBER",
"value": 0.2,
"displayValue": "0.2"
}
]
},
{
"id": 1.2002334692412e+15,
"rowNumber": 3,
"siblingId": 6.8297330034543e+15,
"expanded": true,
"createdAt": "2015-06-02T08:10:40-06:00",
"modifiedAt": "2015-06-02T08:10:40-06:00",
"cells": [
{
"columnId": 8.8643421342862e+15,
"type": "TEXT_NUMBER",
"value": "ccc",
"displayValue": "ccc"
},
{
"columnId": 2.793553446112e+14,
"type": "DATE",
"value": "2015-06-03"
},
{
"columnId": 4.7829549719817e+15,
"type": "DATE",
"value": "2015-06-05"
},
{
"columnId": 2.5311551582965e+15,
"type": "TEXT_NUMBER",
"value": 0.75,
"displayValue": "0.75"
}
]
}
]
}Word of warning, if you do test out your regular expression, don’t do it with the beautified version, because you have space characters all over, which could affect the regular expression.
Conclusion
This is more of a heads up, to make sure that when using this UDA, you’ll have a bunch of data that you will parse. Potentially, I will create other UDAs to support this, but depends on what request I get from your guys.
The downloads section below has the UDA and a test workflow. Don’t forget to create the appropriate Nintex Workflow Constants.
Downloads
Download the Get Smartsheet Sheet UDA– Download, Unzip and import into the Nintex Workflow Create User Defined Action designer page
Download the Test Workflow– Download, Unzip and import into the Nintex Workflow designer page
