Moving Data from a SharePoint List to SQL Using Logic Apps Part II: Retrieve List Items from SharePoint

In Part I of this series, we looked at the very simple steps to create your first Logic App, but now we need to configure to actually do something! In this post, we'll set up a connection to a SharePoint list and then read the items from the list.

Prerequisite(s):

·      You must have access to a list stored in SharePoint

·      If your SharePoint instance is on-prem, you will likely need to set up an on-prem gateway first

Steps:

1) In the Azure portal, navigate to the Logic App you created in the previous post. (You should have received a notification once it was provisioned.)

You should see then a screen like the following. If not, there should be an option to navigate to the Logic App Designer on the left.

I’m going to set up my Logic App to run on a schedule, but you can choose from a variety of triggers. For instance, you could choose to run this Logic App when an item is created in SharePoint.

2) Under the Start with a common trigger heading, click the Recurrence button

The design canvas appears where you can build out your Logic App and even test it. I want my Logic App to fire off once per month. I could always trigger it manually by coming in here and clicking the Run button too.

3) In the Recurrence activity, in the Interval text box, enter 1

4) In the Frequency dropdown list, select Month

Ok, so now our Logic App will run once a month - but it still doesn't do anything interesting. Let's add a new activity using the pre-built SharePoint connector. This connector makes it easy for us to authenticate with and then interact with our SharePoint instance in a variety of ways.

5) Below the Recurrence activity, click the New step button

6) In the Choose an action activity, in the search text box, enter sharepoint

7) In the list of connectors, click SharePoint

8) In the list of Actions, select the Get items option

I’m using SharePoint Online so I’m just going to authenticate, but if you are still using on-prem SharePoint (because you are a masochist), then you may have to set up and configure an on-prem gateway first.

9) Click the Sign in button

You should now see the Get items activity has replaced the Choose an action activity. Now you’ll need the URL for your SharePoint site and the name of the list you want to query.

10) In the Site Address text box, enter the URL to your SharePoint site

11) In the List Name dropdown list, select the name of your list

The query to this list that gets set up by the Logic App connector is an OData query so you could use the advanced option to add filters, sorts, etc. My list has more than 30 columns so I want to use a view that has fewer columns. You can click the Show advanced option button to expand the activity and use the Limit Columns by View dropdown list to do the same thing.

If your list has lots of rows (like mine), you may need to consider using pagination. NOTE: Be smart about this. If you have hundreds of thousands of rows of data, bringing back all of it will take a lot of time. You may run into timeout issues or unexpected costs. In these cases, you should consider using the advanced options to add a filter and reduce the number of rows.

12) At the top right of the Get items activity, click the ellipsis button, and select the Settings option

13) In the Settings for ‘Get items’ view, set Pagination to On

14) In the Limit text box, enter a reasonable number

15) Click the Done button

Let’s test it!

16) In the menu bar at the top of the page, click the Save button

17) Click the Run button

Depending on the amount of data returned and your connection speed, it could take a few seconds or several minutes to retrieve your data. I have over 400 rows with more than 30 columns and it took 11 seconds, but mileage will vary.

You can view the retrieved data in JSON format and open it in your favorite JSON reader. I use Visual Studio, but you could use Notepad or anything else.

18) Click the Click to download hyperlink to download the JSON file

Now we are getting somewhere! Our Logic App can now reach our SharePoint instance to retrieve list items and return them in a JSON format. The possibilities from this point are endless. In Part III, we'll copy these list items to our SQL Database, but we could do all kinds of things. We could loop through our list items and based on the value in each row, we could update an opportunity in CRM or we could email a summary of the items to our executives every week.

As I mentioned at the bottom of the previous post, you can learn more from experts in the community by registering for dev up. I hope to see you there!