Sometimes in life you have to do things you really don’t like to do. Such as extracting data from a SharePoint List. Usually I use SSIS and the OData source component for this task, as described in one of my most popular articles ever: Reading SharePoint Lists with Integration Services 2017 (I’m not kidding). However, when I tried to read a new list, the SSIS package refused to connect to the list, even though the connection manager had a successful connection. Just one of those days…
Time to use another strategy: Azure Logic Apps. They’re easy to use and require almost no code. Let’s create an HTTP-triggered app and add a SharePoint action. If you don’t see SharePoint listed in the connectors, click on the arrow to expand your search.
There you go:
The “Get Items” action allows you to retrieve all items from a list.
Using the same list as in the article I linked to previously, we can configure the action as follows:
Select your SharePoint site (you might need to connect first) and select the list you want from the dropdown. You can also add some parameters, such as an OData filter query or a specific view you want to use.
Normally I limit the number of columns to those I need (so I get rid of the gazillion metadata columns), but I couldn’t get the filter query to accept a “select query”. Even using a view with only the columns you need, will return all of those extra metadata column. Luckily we can just extract the columns we need using the “Select” action. In the connectors pane, choose Data Operations.
There you can find the select action.
The “Get Items”-action returns the data from the list as an array. Using the “Select”-action, we can choose which properties (aka columns) of the array we want to keep. In the From field, choose the value returned by the SharePoint action.
For each mapping, you specify a key (which is the new column name) and a value (which you select from the available columns of the SharePoint List). If you want, you can rename columns. Once the columns are selected, we use again a data operation to format the data as a csv table (if you want to you could also use Json).
Now you can write the data to your desired output, such as Azure Blob Storage for example. This makes the entire flow:
Eazy peazy lemon squeezy.
Some follow-up reading:
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
View Comments
Thanks a lot for sahring the information. It was really helpful
Thank you! Helped me get my head around it.
I found myself coming back here for a second read as I needed to do some more SharePoint list automation. Reading more carefully at the beginning made chuckle quite a bit! 🤣🤣🤣
"Sometimes in life you have to do things you really don’t like to do. Such as extracting data from a SharePoint List."