Categories: Azure

Reading a SharePoint List with Azure Logic App

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:


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

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

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago