Featured Photo by Ruca Souza from Pexels
Many organisations use SharePoint to allow their users to collaborate and share documents in an easily accessible and traceable way, while retainng fine control over who can view and edit what. In this blog we’ll have a look at integrating SharePoint and Alteryx, using the SharePoint List Input and Output tools.
Connecting to your SharePoint
When you add the SharePoint List Input tool to the canvas, the first thing you enter are the credentials you use to access your SharePoint site. If this is a casual workflow which only you will be using, you can save your credentials in the configuration pane too.
If you use SharePoint a lot, depending on your Active Directory set up, you might want to create an Alteryx Service Account to distinguish between machine and individual access to the system.
The second half of the configuration pane is about selecting the list you want to input, which should automatically populate when the credentials have been entered correctly.
Here you can see that I have already selected a list, but what is a list? Microsoft has its own definition, but I tend to think of a list as something in between excel and a database - people can add individual or bulk edit items, but (depending on settings), changes are recorded, views can be generated, and access controlled, like a database. You’ll notice that like a normal input tool, there is an option to limit the number of records input, which is particularly useful if your list has thousands of items.
Doing your data transformation
Once your list has been entered into Alteryx, it runs just like any other workflow, and you can manipulate the data like any other data source. You will notice that the input appends some extra columns to your data featuring SharePoint metadata, such as who last edited the row and what time they accessed it - these can be useful for business questions, such as case reviews, which rely on the last edited row, and may not have this data present as a standalone column. In my case, I’ve populated some data fields as I don’t fully grasp how 'Modern' SharePoint can do some calculations so it’s easier for me to do this in Alteryx.
Writing Back to SharePoint
It is more noticeable that SharePoint leans towards a database more than Excel when it comes to outputting data, because SharePoint is quite touchy about certain rules;
- You cannot add columns from the external interface; a column (even empty) must exist first and be expecting the right data type.
- You cannot create a whole new SharePoint list from the SharePoint Output Tool.
- The ID column, which is one SharePoint generates, must be included in the upload/update (even if you’ve added new rows and it’s empty for those rows) as it acts like a Primary Key.
However, the configuration of the tool is very easy, because it’s very similar to the input tool.
Once your credentials are entered, you can pick a list, and then pick from the output options. These options are to append the changed data, delete the existing data in the table and add your rows as new, or to update (wherein that ID field is the Primary Key). I’ve decided to do a data update on a little sample of data, so you can see that it has only updated the rows which I sampled.
What’s the best way of generating a new SharePoint list for data though? I don’t want to manually specify loads of columns :(
You’re right, that’s tedious, no one wants to do that. My workaround for doing this would be to finish your workflow, generate an excel file with one or two rows of your data, and then use that with SharePoint's “Create a List from an Excel File” utility. Once you’ve generated that framework for your data, you can delete the one or two rows you had and replace your Output Data Tool with a SharePoint List Output instead.
Hopefully this post has de-mystified the SharePoint Integration tools with Alteryx! If you have any comments or suggestions, you can let me know on twitter.