Azure, jQuery & SharePoint: Huh?

Article
11/26/2010

I’ve been spending a lot more time of late looking at how Azure integrates with SharePoint. You may have seen some earlier posts on this. A couple of weeks ago, I attended SharePoint Connections in Las Vegas. I delivered the opening keynote, where we (I was joined by Paul Stubbs) showed a few demos where we integrated SharePoint with the cloud. In the keynote, I specifically outlined three permutations of how SharePoint can map to or integrate with the cloud:

Office 365 (with specific focus on SharePoint Online). Office 365 represents the next wave of hosted productivity applications (and includes Office 2010 Client, SharePoint Online, Lync Online, and Exchange Online).
SharePoint WCM. Essentially, building Internet-facing web sites using SharePoint 2010.
SharePoint integrating with wider cloud services. This might be leveraging Twitter or Facebook to harvest social data and creating ‘socially intelligent’ solutions in SharePoint or using Bing or Azure to create expansive applications that integrate/leverage the cloud.

During this conference (and also at PDC and TechEd EU), I also presented to more enthusiasts on SharePoint and the Cloud. We talked about a number of different patterns that cut across Twitter and SharePoint integration, oData and Office/SharePoint integration, Office Server-Side services, and SharePoint and Azure.

One pattern we discussed during the session was integrating jQuery and Azure in SharePoint. This pattern is interesting because you’re leveraging jQuery as a client technology in SharePoint and also using Excel Services, and you could potentially divorce yourself from server-side dependencies to integrate with Azure, thus no need to install assemblies on the server. This means that you have improved code reusability across SharePoint On-premises and SharePoint Online. I’m halfway through creating a pattern for this integration, so at the risk of showing you half the story I thought I’d go ahead and post current progress. I figured you may find some (or potentially all) of this useful.

First of all, I’m tackling this problem in two steps, the first of which is tested and shown in this blog post. I will post the second step when I complete the pattern. The figure below shows my rough thinking around how I’ll set things up to get a working prototype. Step 1 is complete and discussed in this blog; although, it still has the server-side dependency with the deployed ASMX service to IIS. The goal of trying Step 2 will be to remove the dependency on the ASMX to IIS to become a complete client-side service call/app. The value of the first step is if you want to use jQuery in an on-premises instance of SharePoint (say in an enterprise app), you can use this pattern to do do. The value of the second step is that you take a step away from the server dependency and operate completely on the client.

Scenario: Use an ASMX service to call a method called GetRate(), which simply returns an int value of 5. Then pass that value to be used to update an Excel Web Access data part—updating the data in the Excel Web Access view.

Okay, let’s jump in and discuss Step 1.

Step 1: The first step was to deploy a WCF service to Azure and then build a wrapper service to call the Azure-deployed WCF service from an ASMX service deployed in IIS. This was, in essence, a test run for the jQuery service call. With some client-side code and the jQuery library deployed into SharePoint (say to the Site Assets folder), you can interact with Azure via the ASMX service. Thus, the second step: calling the WCF service direct from jQuery from within SharePoint. You create a WCF Service using the VS 2010 tools and then deploy into Azure. You can reference this blog post to get a sense for how to deploy services to Azure: https://blogs.msdn.com/b/steve_fox/archive/2010/06/15/integrating-a-custom-azure-service-with-business-connectivity-services.aspx.

Service Contract

…

namespace WCFServiceWebRole1
{
[ServiceContract]
public interface IService1
{

…

[OperationContract]
[WebGet]
int getRate();
}
}

Service Method

…

namespace WCFServiceWebRole1
{
public class Service1 : IService1
{
…

public int getRate()
{
int forecastRate = 5;
return forecastRate;
}

}
}

Step 2: Create an ASMX service that calls the WCF Azure service and deploy to IIS.

Web Method

…

[WebService(Namespace = "https://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class RateCall: System.Web.Services.WebService {

[WebMethod]
public int GetRate()
{
int rate = 5;
return rate;
}
}

Step 3: Create a new Empty SharePoint 2010 project in VS 2010. Add two modules to the project. Here you can see that I’ve called the one module AzureWebServiceCall (this contains the script that will call the ASMX service that calls the Azure service) and the other module JQueryCore, which contains the jQuery libraries. You can use a .txt or .js file; that is, AzureServiceCall.txt or AzureServiceCall.js will work as files that you can point to from a Content Editor Web Part).

For the JQueryCore module, you right-click and select Add and Existing Item and add the appropriate library to your project. For the AzureWebServiceCall code, you amend the text file with code that looks similar to the below. Note that the bolded code is relevant to the call into the web service.

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src=" https://blueyonderdemo/SiteAssets/SiteAssets/jquery-1.3.2.min.js" ></script>

//Vars that are used to update the fields in the page. Q1 will be used for the return int from Azure.

var Q1;
var Q2 = "5.5";
var Q3 = "6";
var Q4 = "7";

//JS variable for SOAP call (ASMX wrapper to Azure call.)

var soapEnv = "<soap:Envelope xmlns:xsi=' https://www.w3.org/2001/XMLSchema-instance' \
xmlns:xsd=' https://www.w3.org/2001/XMLSchema' \
xmlns:soap=' https://schemas.xmlsoap.org/soap/envelope/' > \
<soap:Body> \
<GetRate xmlns=' https://tempuri.org/' /> \
</soap:Body> \
</soap:Envelope>";

//Excel Services JavaScript bootstrap code.
var ewa = null;

if (window.attachEvent) {
window.attachEvent("onload", ewaOmPageLoad);
}
else {
window.addEventListener("DOMContentLoaded", ewaOmPageLoad, false);
}

function ewaOmPageLoad() {
if (typeof (Ewa) != "undefined") {
Ewa.EwaControl.add_applicationReady(ewaApplicationReady);
}
else {
// Error code should go here.
}
}

function ewaApplicationReady() {
ewa = Ewa.EwaControl.getInstances().getItem(0);
}

//Button click event leverages jQuery.

function btnGetData_onclick() {

jQuery.ajax({
url: " https://blueyonderdemo:8744/AzureGetRate.asmx" ,
type: "POST",
dataType: "xml",
data: soapEnv,
success: processResult,
contentType: "text/xml; charset=\"utf-8\""
});

return false;
}

function processResult(xData, status) {
//Creates and sets msg var to the int value that is returned from the service call in the SOAP package.

var msg = $(xData);
var Q1 = msg[0].text;

//Updates the field elements. Note that these are hard-coded save for Q1, but you could add return data or calculated fields here.

document.getElementById("txtbxQ1").value = Q1 + "%";
document.getElementById("txtbxQ2").value = Q2 + "%";
document.getElementById("txtbxQ3").value = Q3 + "%";
document.getElementById("txtbxQ4").value = Q4 + "%";

//EWA code to update cells in active workbook (but does not save back to server)
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q1_Forecast", getRangeComplete, [[Q1]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q2_Forecast", getRangeComplete, [[Q2]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q3_Forecast", getRangeComplete, [[Q3]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q4_Forecast", getRangeComplete, [[Q4]]);
}

</div>
</body>
</html>

The elements.xml file should deploy the files to a location in SharePoint, which you will use in two places: 1) you use above in the jQuery library reference and 2) you will load the .txt file using the Content Editor web part.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="https://schemas.microsoft.com/sharepoint/">
<Module Name="AzureWebServiceCall" Url="SiteAssets">
<File Path="SiteAssets\AzureServiceCall.txt" Url="SiteAssets/AzureServiceCall.txt" Type="GhostableInLibrary" />
</Module>
</Elements>

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="https://schemas.microsoft.com/sharepoint/">
<Module Name="JQueryCore" Url="SiteAssets">
<File Path="JQueryCore\jquery-1.3.2.min.js" Url="SiteAssets/jquery-1.3.2.min.js" Type="GhostableInLibrary" />
<File Path="JQueryCore\jquery-1.4.2.js" Url="SiteAssets/jquery-1.4.2.js" Type="GhostableInLibrary" />
</Module>
</Elements>

Step 4: Deploy to SharePoint

To do this, right-click the top-level SharePoint project and click Deploy. This will deploy the modules into a folder called SiteAssets in SharePoint.

At this point the core jQuery libraries and JavaScript files are deployed. You now need to add an Excel document that will use the return value from the Azure service call to update the fields in the Excel document. In this Excel document, note the following quarterly aggregates with four named cells (e.g. Q1_Forecast, and so on).

If you look at the JavaScript code, you’ll notice that you’re using the ewa object to get the active workbook and a specific range in that workbook: cell = “Forecast!Q1_Forecast”. Thus, the code takes the return int from Azure and updates the Q1_Forecast cell with that value. The other fields are simply calculated fields that pivot off whatever value is in Q1_Forecast (e.g. Q2_Forecast = Q1_Forecast * .20).

//EWA code to update cells in active workbook
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q1_Forecast", getRangeComplete, [[Q1]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q2_Forecast", getRangeComplete, [[Q2]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q3_Forecast", getRangeComplete, [[Q3]]);
ewa.getActiveWorkbook().getRangeA1Async("'Forecast'!Q4_Forecast", getRangeComplete, [[Q4]]);

An important note to the Excel Web Access code is that the ewa object will work only if there is one Excel Web Access web part on the page; if there is more than one, you’ll need to reference the web part by it’s specific ID. Dan Parish has a great write-up of this (along with some additional details on the EWA code) here: https://blogs.msdn.com/b/excel/archive/2007/10/29/excel-services-combining-the-ewa-and-api-using-ajax.aspx.

You need to make sure that when you create and upload the Excel document to a document library, that you then publish the named range (or other objects you’re using in the Excel document) to SharePoint using the Excel Services functionality. To do this, click the File tab, Save and Send, Save to SharePoint, and then select Publish Options. Select the items you want to expose to Excel Services and then click OK. The figure below shows the named ranges/cells that I’ve exposed through Excel Services.

Step 5: After the files are deployed and you’ve published your Excel document to SharePoint, you create a Content Editor Web Part to the page. Point the content editor web part to the location of the .txt file (with the JavaScript code above) in it—which if you followed along would be in the Site Assets library. To do this, click Insert, Web Part, Media and Content, and Content Editor, and click Add.

Then click Edit Web Part and add the link to the text file in the in the Content Link.

SharePoint now dynamically loads the .txt file with the jQuery/JavaScript in it. Again, you can use a .js file and it loads the same.

Step 6: You can now add the Excel Web Access web part on the web part page that loads the Excel document. To do this, click Insert, Web Part, Business Data, and Excel Web Access, and click Add. When the default web part loads, click ‘Click here to open tool pane.’ Add the link to the Excel Document in the Workbook field and add the name of the named range in the Named Item field.

You should now have something like the following loaded onto the page.

You can rearrange the web parts on the page as you desire, but when you click the Get Data button the following will happen:

1. jQuery calls the ASMX service deployed to IIS.

2. ASMX service calls WCF service deployed to Azure and then returns the int value of 5.

3. jQuery code takes the return SOAP package and extracts the returned int value.

4. JavaScript code leverages the returned int value and updates the calculated view (i.e. the Excel Web Access web part).

5. The percentages for each quarter in the Excel Web Access web part are updated from the one core return int of 5.

Summary

A long post, I know. Using jQuery is interesting; using it in the context of Azure service-calls to update views is compelling (think financial models that depend on a cloud-service that delivers daily integer values that have significant cascading effects on the model).

This post showed you how you could leverage an Azure WCF service call and wrap with an ASMX SOAP call, which you could then use in JavaScript and jQuery to update an Excel view. The next step would be to craft an app that will eventually live and act entirely in the cloud—i.e. it uses data stored in Azure and uses jQuery to interact with an Azure WCF service. As I move forward, I’m going to continue to bang away at this untilI have something that is completely lives in the cloud without any on-premises dependencies.I welcome any thoughts from you the community.

In the meantime, grind the code!

Steve

Azure, jQuery & SharePoint: Huh?

Additional resources