Using MS Azure to create an ODATA/REST API for SharePoint Online

Nothing's as easy as it looks. Especially with Azure. Most of the articles I read have functionality that no longer exists in Azure or has different screens than I see. Makes for a good exercise to figure things out though.

 

Use case: wouldn't it be nice to have a public web app that could interact with your locked down SharePoint online? YES! For instance, have a potential service providers fill out a form with all their qualifications on a public site, then process these forms in SharePoint Online (SPO). Or a simple custom way to request access to your SPO. I organize a tennis league with SPO and will start using an Azure web app with REST to deal with requests for joining the league.

How to do it? We use a cool and expensive product at work called ServiceNow that opened my eyes to the possibilities. I can report off of it with HighCharts using REST calls. Got me thinking that a nice "database in the cloud with REST services attached" would be awesome. I searched for some free ones but never pulled the trigger. New projects at work got me involved with Visual Studio (VS) again. It's been a few years, lots new there. I discovered "LightSwitch" applications. The no-code little wonders will generate a fully functional atom ODATA service layer for a SQL server database. No code required. Sweet!

I got it working on my local machine pretty fast. End to end in 5 minutes really. Getting it "on the cloud" wasn't as easy as clicking "Publish" like I hoped though. After creating an azure web app and sql database and putting that in the publish tool, it wouldn't work. I don' think the db got copied.

Here's what you do:

1.       Create the web app in Azure. Easy

2.       Create the SQL database in Azure. Easy.

a.       Gotcha #1:Create a firewall rule for your DB, allowing your local IP access to it.

3.       Connect to your azure db from VS and design a table(s) locally.

4.       Connect to the db in your LightSwith app

5.       Test it.

6.       If it works, now deploy it. It should work because the db is already there.

 

Integrating with SPO

1.       Gotcha #2: You will have to add control-access-header to your web apps web.config file!

a.       Use FTP to get access to the azure web app web.config

b.       Add the HTTPProtocol section!

2.       Write some javascript in SPO to grab the data using REST/json!

3.       Done!

I will add more details to this post later.