Sync O365 SharePoint List data to Azure SQL DB table using Microsoft Flow & Azure Function App

Overview:

This article provides you knowledge to synchronize O365 SharePoint list data with Azure SQL database table by making use of latest technologies/features like Azure Function app and Microsoft flows.

On every SharePoint list item, insert/update our custom Microsoft Flow will get triggered and calls Azure function app to do sync job operation with Azure SQL database table. Our custom Azure function app has all the functionality written to get SP list item information from list and then add/update in Azure SQL DB table. All operations performed by Azure function app will be logged to “Log Analytics” which is again a feature of Azure to maintain logs of any application for further reference. The content flow will be as mentioned below, between SP list and Azure DB table:

O365 SP List à Microsoft Flow à Azure Function app à Azure SQL DB

Same way, the synchronization flow from Azure SQL database table to SP list is as given below:

Microsoft Flow (Scheduled Job) à Azure Function app à Azure SQL DB à O365 SP List

Our solution is completely generic and can be used for multiple lists vs DB table synchronization just by changing configuration.

Components used in this article:

• Azure: Function App, SQL DB, Log Analytics
• O365: SharePoint List, Microsoft Flow

Now, let’s go through the detailed discussion required to implement the sync job between list and DB table. Below mentioned are the steps that we will follow in this article:

1. Create SharePoint list
2. Create Azure SQL DB and table
3. Generate Client ID and Secret & grant access at site level
4. Create Azure function app
5. Create Log Analytics
6. Create Microsoft Flow for SP to Azure SQL Sync (Single item)
7. Create Microsoft Flow for Azure SQL to SP Sync (Multiple items)
8. Add Azure Function App Configuration (Key & Value Pair)
9. Test it by adding/updating items in SP list and DB table

Step 1: Create SharePoint List

1. Login to O365 SharePoint Site (Ex: https://contoso.SharePoint.com)
2. Create Custom List with Employee title
3. Add below-mentioned columns to the Employee list
   1. FirstName
   2. LastName
   3. Designation
   4. EmployeeID

Step 2: Create Azure SQL DB and table

1. Login to Azure portal (https://portal.azure.com)
2. Create Azure SQL database with ContosoOrgDB (if already not created) (Instruction to create Azure SQL DB)
3. Click SQL Databases option available in Left navigation à Select the ContosoOrgDB database à Select Overview option à Copy Server name present in the right window à Click Set Server Firewall à Click Add Client IP link present at top of the window à Click Save button present at top-left corner of the window. With this, your system will be added to the trusted locations to work with Azure database.
4. Go to SSMS in your local machine à Click Connect symbol à Paste the server name which you copied in the last step à Provide your DB username and password à click Connect.
5. Create table with Employee title and add the below mentioned columns to it. (The column names should be as same as SP list column names to perform Sync without issues).
   1. FirstName
   2. LastName
   3. Designation
   4. EmployeeID

Note: The account which you are using to login to Azure must have Azure subscription and enough access to create Azure “Function app” and “SQL Database”

Step 3: Generate Client ID and Secret & grant access at site level

Register App:

1. Navigate to O365 SharePoint App registration page (Ex: <site collection url>/_layouts/15/AppRegNew.aspx)
2. Generate Client ID and Client Secret by clicking Generate button
3. Fill Title field with EmployeeSync
4. Fill App Domain field with localhost.com
5. Fill Redirect URI field with https://www.localhost.com
6. Now, click the Create button to register the app
7. Once created, it will show the details on the display page. Please copy it to your notepad.

Grant Access to the App:

1. Navigate to <site collection url>/_layouts/15/AppRegNew.aspx page to grant access to the app
2. Now, enter the Client ID which we generated in app registration page and then click Lookup
3. It should fetch the app details once you click lookup
4. Fill Permission Request XML field with below XML <AppPermissionRequests><AppPermissionRequest Scope=”http://sharepoint/content/sitecollection” Right=”FullControl” /></AppPermissionRequests>
5. Now, click Create button
6. Click Trust button to grant access to the app
7. Now your app is registered to the site and granted with Full Access

Reference: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/register-sharepoint-add-ins

Note: The account which you are using to login to O365 SharePoint site must have Site Collection access to register the app and to create Microsoft flows.

Step 4: Create Azure Function App

1. Create Azure Function App Visual Studio solution by following this article. Let the solution name be EmployeeSyncApp
2. Add HTTP trigger function to the solution by following this article. Let the function name be SPtoSQLSingleItemSync. Test the application as mentioned in the article to confirm the solution/function is working as expected.
3. Write C# code in the above created HTTP trigger function. The code should include functionality to Get SharePoint List item details and Add/update item in the Azure SQL database table.
4. Add one more HTTP Trigger function and the name be SQLtoSPMultipleItemSync. Test the application as mentioned in the article to confirm the solution/function is working as expected.
5. Write C# code in the above created HTTP trigger function. The code should include functionality to Get all Azure SQL DB table records and Add/update item in the SP List.
6. Navigate to https://portal.azure.com and create Azure function app by following below mentioned steps.
   1. Click Create a resource present at top left corner of the page
   2. Select Compute and then select Function App
   3. Provide App name as EmployeeSyncApp (Name should be unique)
   4. Select new resource group and provide AzureTestGroup value to it.
   5. Click Create
   6. Now go to All Services à Function apps à Select EmployeeSyncApp function app
   7. Click Get Publish Profile link to download.
   8. Now go to Visual Studio solution
   9. Right click on project and select Publish
   10. Select Existing radio button and upload downloaded Publish profile
   11. Click Publish button to publish azure function.

Step 5: Create Log Analytics

1. Navigate to https://portal.azure.com
2. Click Create Resource present at top left corner of the page
3. Select Management Tools and then select Log Analytics
4. Provide OMS Workspace name as EmployeeSyncAppLogs
5. Select Use existing option for Resource Group and select AzureTestGroup
6. Now click OK button
7. Now, the log analytics is ready for use in our function app.

Step 6: Create Microsoft Flow for SP to Azure SQL Sync (Single item)

1. Navigate to https://flow.microsoft.com
2. Click My flows link present at top left corner of the page
3. Click Create from blank link
4. Again, click Create from blank button
5. Select SharePoint connector
6. Select When an item is created or modified trigger
7. Provide SharePoint Site address (Site URL) where we created our Employee list (Ex: https://contoso.SharePoint.com)
8. Select SharePoint List Name on which our flow must be triggered on item add/update operation. In our case, it’s Employee
9. Click + New Step and select HTTP connector
10. Select HTTP – HTTP trigger
11. Select Method as Get
12. The URI is nothing but the URL of the Azure function to which it must call
    1. Navigate to https://portal.azure.com
    2. Go to Azure Functions à Select EmployeeSyncApp function app à select SPtoSQLSingleItemSync function under Functions section
    3. Click on Get function URL link present at right side window and copy the URL.
    4. Go back to Flow and paste copied URL in URI field
13. Add below mentioned headers
    1. Key=ReferenceColumn
       • Value=EmployeeID
    2. Key=ReferenceIds
       • Value=EmployeeID
14. Click Save present at top right corner
15. Provide Flow Name as SPtoSQLSingleItemSyncFlow

Step 7: Create Microsoft Flow for Azure SQL to SP Sync (Multiple items)

1. Navigate to https://flow.microsoft.com
2. Click My flows link present at top left corner of the page
3. Click Create from blank link
4. Again, click on Create from blank button
5. Select Built-in tab at top and select Schedule connector
6. Select Recurrence trigger
7. Enter 1 in the Interval field and select Day
8. Click + New Step and select HTTP connector
9. Select HTTP – HTTP trigger
10. Select Method as Get
11. The URI is nothing but the URL of the Azure function to which it must call
    1. Navigate to https://portal.azure.com
    2. Go to Azure Functions à Select EmployeeSyncApp function app à select SQLtoSPMultipleItemSync function under Functions section
    3. Click on Get function URL link present at right side window and copy the URL.
    4. Go back to Flow and paste copied URL in URI field
12. Add below mentioned headers
    1. Key=ReferenceColumn
       • Value=EmployeeID
13. Click on Save present at top right corner
14. Provide Flow Name as SQLtoSPMultipleItemSyncFlow

Step 8: Add Azure function app configuration

1. Navigate to https://portal.azure.com
2. Now go to All Services à Function apps à Select EmployeeSyncApp function app
3. On the right side window, click Application Settings link
4. Under application settings section, add all the configurations which are required for your sync job/function.
5. In my scenario, I added below mentioned configuration settings:
   1. ClientId: This is the Client ID which we generated in step 3.
   2. ClientSecret: This is the Client secret which we generated in step 3.
   3. SPToDBSyncUrl: This is “SPtoSQLSingleItemSync” function URL.
   4. DestDBName: This is database where you table is present
   5. DestSQLConString: This is connection string for DB access
   6. DestTableName: This is DB table name
   7. LogName: Here, you must provide name of log analytics
   8. SharedKey: Log Analytics “Primary Key”
   9. SPListName: SharePoint list name
   10. SPSiteUrl: SharePoint site URL where your list present
   11. TimeDuration: Request timeout duration

Step 9: Test it by adding/updating items in SP list and DB table

1. Now go to SharePoint list
2. Add/update an item in the list
3. Wait for a while and check Azure SQL DB table.
4. You should find the record synced to SQL database table.

If you don’t find the record in the table, then please cross-verify the above steps and configuration in the Azure function app.