Using SharePoint CSOM in Node.js

My post “Using SharePoint CSOM in HTML5 apps” describes the minimum set of js files you need to use the SharePoint JavaScript Client Side Object Model (CSOM) in the browser.

I was curious if you can also use SharePoint CSOM in Node.js. I found out, you can!  In this post I’ll show you how.

Warning: the SharePoint CSOM files are designed to run in the browser, so consider everything that follows as highly experimental!

The CSOM files

To use CSOM in the browser, you need the following 5 files from SharePoint:

  • /_layouts/1033/init.js
  • /_layouts/MicrosoftAjax.js
  • /_layouts/sp.core.js
  • /_layouts/sp.runtime.js
  • /_layouts/sp.js

If you want to learn more about the code in these files, you can use the DEBUG versions which you can find here:

  • /_layouts/1033/init.DEBUG.js
  • /_layouts/sp.core.DEBUG.js
  • /_layouts/sp.runtime.DEBUG.js
  • /_layouts/sp.DEBUG.js

A debug version of MicrosoftAjax.js is included in VisualStudio 2010.

Concatenated, the debug versions of these 5 files contain approximately 33.000 lines of JavaScript code, which I will refer to as the ‘CSOM code’ in the rest of this article. Our goal is to use the CSOM code on Node instead of the browser, without modifying the source files.

I started out by just concatenating the 5 source file in one big file and trying to run it with Node. Node will terminate when an error is thrown and point to the error. You will quickly realize that all the errors raised are related to the difference in execution context between a browser and Node. Node doesn’t have objects like document, window, XmlHttpRequest and navigator, which the CSOM code expects.

To summarize my findings, the CSOM code expects and requires the following:

  • a window object as global context
  • a DOM element named “__REQUESTDIGEST” which contains a digest value that will be included in HTTP request header X-RequestDigest.
  • a navigator object, identifying the user agent
  • a document object, with a URL property, used to identify the domain of the SP server
  • XMLHttpRequest to make server requests
  • authentication cookies

The trick is to emulate these objects, just enough to make the CSOM code run as intended and without errors. I ended up with the following code which mocks the browser environment expected by the CSOM code.

var window = global;
    
var navigator = {
    userAgent: "Node"
};
    
var formdigest = {
    value: 'DUMMY VALUE',
    tagName: 'INPUT',
    type: 'hidden'
};

var document = {
    documentElement: {},
    URL: 'http://yourdomain.sharepoint.com/',
    getElementsByName: function (name) {
        //console.log('getElementByName: ', name);
        if (name == '__REQUESTDIGEST') {
            return [formdigest]
        }
    },
    getElementsByTagName: function (name) {
        //console.log('getElementByTagName: ', name);
        return []
    }
};

Let’s have a closer look on the digest. Remember that our minimal aspx page contained a form digest control? This control will include a hidden form input element named “__REQUESTDIGEST”. The CSOM code will retrieve the value from the DOM, using document.getElementsByName(“__REQUESTDIGEST”)[0].value. As you can see, the code above emulates the document object and input element just enough to make this code work in the Node environment. We only need to set a valid digest value in the formdigest object. We’ll come to that later.

Remote authentication

In order to access the SharePoint server our Node SharePoint client needs to authenticate. Please see my post on remote authentication to SharePoint Online for more details. We will reuse the authentication code described in Node.js, meet SharePoint.

Emulating XMLHttpRequest

The CSOM code makes server requests using XMLHttpRequest. Luckily, there is already an implementation of XMLHttpRequest in Node and it works great. There’s only one workaround required in order to include the authentication cookies in the request. We will assign the authentication cookies to the XMLHttpRequest constructor and include them as part of the default headers. I made the following modification to the source of XMLHttpRequest:

var defaultHeaders = {
    "User-Agent": "node.js",
    "Accept": "*/*",
    "Cookie": this.constructor.authcookies
};

Now we can set the authentication cookies by using:

    // make sure the XMLHtpRequest includes the authentication cookies
    XMLHttpRequest.authcookies = 'FedAuth=' + client.FedAuth + '; rtFa=' + client.rtFa;

Obtaining a digest value

After authentication, but before the CSOM is used we need to obtain a valid digest value. The CSOM code expects the value to be available in a hidden input element which we emulated through the formdigest object. To get a digest value, we could fetch an aspx page with an embedded formdigest control and parse out the value. Another option is to use the sites.asmx SharePoint Web Service to request a digest value.

We will use the web service in the following code :

function requestDigest(params, callback) {

    var payload =
        '<?xml version="1.0" encoding="utf-8"?>' +
        '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' +
              ' xmlns:xsd="http://www.w3.org/2001/XMLSchema"' + 
              ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' +
        '  <soap:Body>' +
        '    <GetUpdatedFormDigest xmlns="http://schemas.microsoft.com/sharepoint/soap/" />' +
        '  </soap:Body>' +
        '</soap:Envelope>';

    var options = {
        method: 'POST',
        host: 'yourdomain.sharepoint.com',
        path: '/_vti_bin/sites.asmx',
        headers: {
            'Content-type': 'text/xml',
            'Cookie': 'FedAuth=' + params.FedAuth + '; rtFa=' + params.rtFa,
            'Content-length': payload.length
        }
    }

    var req = http.request(options, function (res) {
        var responseText = '';
        res.setEncoding('utf8');
        res.on('data', function (chunk) {
            responseText += chunk;
        })

        res.on('end', function () {
            //console.log('XML: ', responseText);

            if ((responseText == null) || (responseText.length <= 0)) {
                return;
            }
            var startTag = '<GetUpdatedFormDigestResult>';
            var endTag = '</GetUpdatedFormDigestResult>';
            var startTagIndex = responseText.indexOf(startTag);
            var endTagIndex = responseText.indexOf(endTag, startTagIndex + startTag.length);
            var newFormDigest = null;
            if ((startTagIndex >= 0) && (endTagIndex > startTagIndex)) {
                var newFormDigest = responseText.substring(startTagIndex + startTag.length, endTagIndex);
            }
            if ((newFormDigest == null) || (newFormDigest.length <= 0)) {
                return;
            }
            
            formdigest.value = newFormDigest;
            callback();
        })
    })

    req.end(payload);
}

To summarize, the client app on Node must:

  • Request a security token from Security Token Service
  • Submit the security token to SharePoint Online and receive authentication cookies
  • Request a digest value and insert the value into formdigest object

Once these steps are successfully completed, you can start using CSOM code, just like you would in the browser.

Running example

Here’s the final part:

// constructor for SharePoint Online client
SPO = function (url) {
    this.url = urlparse(url);
    this.host = this.url.host;
    this.path = this.url.path;

    // External Security Token Service for SPO
    this.sts = {
        host: 'login.microsoftonline.com',
        path: '/extSTS.srf'
    };

    // Form to submit SAML token
    this.login = '/_forms/default.aspx?wa=wsignin1.0';
};

SPO.prototype = {
    signin: signin
};

var client = new SPO('http://yourdomain.sharepoint.com/yoursite');

client.signin("yourusername", "yourpassword", function () {
    
    // make sure the XMLHtpRequest includes the authentication cookies
    XMLHttpRequest.authcookies = 'FedAuth=' + client.FedAuth + '; rtFa=' + client.rtFa;

    // as of this point, you can use the CSOM service as if you are in a browser......
    // for example, we will request the properties of the site 'teamsite'.
    var ctx = new SP.ClientContext("/teamsite"),
		web = ctx.get_web();

    ctx.load(web);
    ctx.executeQueryAsync(function () {
        var properties = web.get_objectData().get_properties();
        console.log(properties);
    });
    
});

SPO represents a CSOM client (for SharePoint Online). The signin method takes care of authentication and fetches the digest value. Once signin is completed, the authentication cookies are set on XMLHttpRequest. At that point we create a SP.ClientContext and set up a query to get web properties. CSOM will fire a query to the SharePoint server and we write the properties to the console.

Full code for this example is available here: https://gist.github.com/2404924. You will need to insert the CSOM code yourself.

Let’s run the code with Node:SNAGHTML3061025

And there you have it: the SharePoint Client Side Object Model running on Node! If you want to try this yourself, I recommend using node-inspector for stepping through your code and get a better understanding of the inner workings of CSOM.

Possible scenarios

CSOM is very useful when your requirements go beyond manipulating List data. CSOM on Node may be used in scenarios like:

  • data transfer/synchronization between SharePoint with other data sources including automated creation of list/columns
  • long running processes like monitoring or content analysis
  • automated permission maintenance
  • automated creation of sites
  • upgrade of list and site definitions
  • need for CAML queries on SharePoint data
  • support for additional protocols (e.g. Web sockets)

Let me know if you have suggestions/comments.

Thanks for reading!

Node.js, meet SharePoint

In this post I would like to introduce you to Node-SharePoint, a SharePoint client for Node.js available on GitHub and NPM. This Node module allows you to access SharePoint 2010 lists and items from Node.js. It is based on ListData.svc, the OData based REST API for SharePoint 2010. 

Getting started

Download and install Node (if you haven’t already): http://nodejs.org/#download.

This will also install NPM (Node Package Manager). Use NPM to install the SharePoint client module:

C:> npm install sharepoint

A first example

Create a JavaScript file called main.js and use the code below as a starting point:

var SP = require('sharepoint'),
    site = 'http://yourdomain.sharepoint.com/teamsite',
    username = 'yourusername',
    password = 'yourpassword';

var client = new SP.RestService(site),
    contacts = client.list('Contacts');

var showResponse = function (err, data) {
    console.log(data);	
}

client.signin(username, password, function () {
	
    // At this point, authentication is complete,
    // so we can do requests.

    // Example request: Get list items
    // showResponse is used as callback function
    contacts.get(showResponse)

});

Let’s highlight a few key topics:

  • require(‘sharepoint’) returns an ‘namespace’ object, which contains a RestService class.
  • an object of the SP.RestService class represents a client for the ListData service within the designated site.
  • ListData operates on lists, so we use client.list(listName) to create a List object.
  • client.signin implements the claims based authentication process for SharePoint Online. You can find more details about remote authentication in my  previous post.
  • once the signin is completed, the callback function is called. In the callback function you can start sending authenticated requests to SharePoint Online.
  • Please note that the SharePoint client uses the following callback convention:
function callback(err, data) {
   // err: is used to pass an error (string), if any

   // data: contains the output of the previous response or processing step
}

Provide your own credentials and site in main.js and run the file with node:

C:> node main.js

You should see the items of Contacts list in your window:

SNAGHTMLb94bb01

Here are some further example how to work with list items.

// get items ordered by FirstName		
contacts.get({$orderby:'FirstName'}, showResponse);

....

// get fist 3 items and total count of items in list	
contacts.get({$top:3, $inlinecount:'allpages'}, showResponse);

....

// get item with Id 412 from the Contacts list;
contacts.get(412, showResponse);

.....

// add a new item to the list
contacts.add({LastName: 'Peel', FirstName: 'Emma'}, showResponse)

....

// update an item	
contacts.get(412, function (err, data) {

    var changes = {
        // include the changes that need to be made
        LastName: 'Tell',
        FirstName: 'William',

        // pass the metadata from the fetched item
        // this includes the etag
        __metadata: data.__metadata
    };

    contacts.update(412, changes, function () {
        // at this point, the change is completed
        // so let's check by getting the item again
	     contacts.get(412, showResponse)
    })        
})

.....

// delete an item
contacts.del(412);

Hopefully this helps you get going in connecting Node.js to your SharePoint site! Let me know if you have questions/suggestions.

Node.js on Azure calling SAP Gateway

It’s hard to miss the growing excitement around Node.js, a server-side JavaScript environment, especially ever since Microsoft supports the Node.js project and even offers Node app hosting as a service on Windows Azure.

I was eager to get a first hands-on with Node on Azure, so in this post we are going to build a Node.js app running on Windows Azure, accessing SAP business data through SAP Gateway. I decided to go for ‘the Cloud’ all-the-way, so we will develop both a server and client (i.e. browser) application in JavaScript using Cloud9, a web based IDE. The browser app is based on SAPUI5. It displays a list of Sales Orders and the user can also request an overview in Excel, generated on-the-fly on the server. To easily work with the source code of the project, we will use a repository on GitHub.

Here are screenshots of the end result:

SNAGHTMLcac40b9

SNAGHTMLb1e5875

So if you are in for some exposure to cutting edge technologies, read on! Winking smile

The players

SAP Gateway is a SAP server add-on that allows you to access  business data in SAP systems through OData, an open web protocol for querying and updating data, built on common web technologies like HTTP, XML and REST.

SAPUI5 (officially known as “UI development toolkit for HTML5”) is a JavaScript library, currently in Beta, to build HTML5 apps.

Node.js is a server platform that is quickly gaining popularity. It is built on Chrome’s V8 JavaScript runtime and allows you to create server applications in JavaScript. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient.

Cloud9 is a web-based IDE + node.js runtime environment offered as a service (partially open sourced). In Cloud9 you can develop, debug and test node.js applications, all from within your browser. Cloud9 can use a GitHub repository to manage and share the source code. Once you have completed your application, you can deploy the application to Windows Azure.

Windows Azure is Microsoft’s cloud platform. One of the services is hosting a Node.js application in a scalable environment.

GitHub hosts source code repositories,using the Git version control system. GitHub offers public and private repositories. To work with GitHub, you need to use Git client software.

The Big Picture

Before we go into the details, let’s sketch how it all fits together:

image

For development, we’ll create a code repository on GitHub. This way we can add/update source code and resource files both from our local machine (via Git) and on Cloud9. We will use the repository to add all SAPUI5 js and css files that are required to our project files. In Cloud9 we use a clone of the GitHub repository. This way we can push and pull Cloud9 project files in and out of the GitHub repository.

Cloud9 is a fully web-based development environment: developers use a browser to access the Cloud9 code editor, system command prompt, debugger, Node.js console output etc. We can run a Node.js application directly on the Cloud9 environment and use a special url to access the running server app as a (test) user.

Actually we need 2 applications: a server application which runs on the Node.js server and acts as middleman to SAP Gateway (and possibly other data sources) and a client application (HTML5, SAPUI5)  which runs in the browser, but uses html/css/js files which are hosted on the server. It is important to keep these two apart, even if we develop both in JavaScript.

The client and server app interact in the following way:

Display Sales Orders in Data Table

  • the user starts the app by requesting the server url
  • the server sends the client application html file (index.html) which instructs the browser to load additional SAPUI5 resource files and the client js code.
  • the client app starts up and creates a SAPUI5 OData Model and Data Table to fetch and display Sales Order data. The data isn’t fetched from the SAP Gateway directly (not allowed due to the Same Origin Policy). Instead, the request is sent to the server, which forwards it to SAP Gateway. The server acts as a reverse proxy.
  • The server passes the OData response from SAP Gateway to the browser. Here, the OData feed is parsed using SAPUI5’s built in OData support and the extracted data is shown in the Data Table.

Download as Excel

  • When the user clicks on ‘Download as Excel’ button, the client app requests a special url from the server.
  • This will trigger the server to fetch the Sales Order data from SAP Gateway, parse it (but now on the server side!), convert it into an Excel format and return this to the user. The user experience is identical to requesting a downloadable file.

Once we have the application working in the Cloud9 environment, we can request deployment to Windows Azure. Cloud9 will generate configuration files, package the apps, upload and deploy to Azure.

Caveats:

  • This demo project involves quite some platforms and services. Within the scope of this blog post it is not feasible to provide a very detailed, click-by-click walk-through. Instead I will refer to tutorials for the respective services which contain detailed instructions and I will point out the main route and highlights along the way.
  • I recommend that you use Safari, Chrome or Firefox on GitHub and Cloud9. IE support is lagging (GitHub) or non-existent (Cloud9).

Step 1: Create a GitHub repository

First we need to create a repository on GitHub to manage and share our source code across environments.

  1. Sign-up with a (free) account for GitHub. A free account let’s you create public repositories.
  2. Follow the instructions on Set up Git to install Git local software and set up SSH keys
  3. Create a repository on GitHub. We’ll assume it is named: NODEJS_SAPGW
  4. Clone the repository to local machine
  5. Try out pushing and pulling content from your local machine into the repository until you become familiar with the process

Here are the important Git commands you will need:

// Clone repository
$ git clone git@github.com:[yourname]/NODEJS_SAPGW.git

// push content
git add .
git commit -m "Commit message"
git push

// pull content
git pull

I think GitHub is a great tool for developers, so I encourage you to check out the GitHub help center to learn more.about the other features of Git and GitHub. For our demo project the above commands are all we need.

Step 2: Develop on Cloud9

Now we have a repository on GitHub, we can start a project on the Cloud9 IDE.

  1. Signup on http://c9.io. It is very convenient to signup with your GitHub account. Look for the small GitHub icon.
    image
  2. After signup, you will see your GitHub repository (I blurred out my other repositories):
    image
  3. Select the repository and click ‘Clone to edit’ and select Shared Development server.
  4. The Cloud9 environment will make a Clone of the GitHub repository available in your Cloud9 environment.
  5. Once the Clone is completed, you see the following screen. Click on ‘Start Editing’.
    image
  6. This will bring you into the project environment. Create a folder called ‘Public’. This folder will contain all the files required for the client application. Add the index.html and app.js (described below) and push the changes to GitHub and pull to your local machine. Add the SAPUI5 resources on your local working directory and push to GitHub and pull from Cloud9. All this pushing and pulling sounds like a workout, but trust me, you’ll get the hang of it!

Client application

The client application consists of an index.html file and app.js file and uses SAPUI5 to fetch and parse OData and render a data table widget.

The index.html includes the SAPUI5 core library (which dynamically will load all dependencies), the application js file and a placeholder for the data table.

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv='X-UA-Compatible' content='IE=edge' />
        <title>Node.js calling SAP Gateway</title>

        <script id="sap-ui-bootstrap" 
           src="resources/sap-ui-core.js"
           data-sap-ui-theme="sap_goldreflection"
           data-sap-ui-libs="sap.ui.commons, sap.ui.table"></script>

        <script src="app.js"></script>

    </head>
    <body class="sapUiBody">
         <img src="images/sap_logo.png" />
         <div id="salesorders"></div>
         
    </body>
</html>

The app.js contains the client code:

// Let's define some shortcuts to increase
// readability of the code
var ODataModel = sap.ui.model.odata.ODataModel,
    TextView = sap.ui.commons.TextView,
    Label = sap.ui.commons.Label,
    DataTable = sap.ui.table.DataTable,
    Toolbar = sap.ui.commons.Toolbar,
    Button = sap.ui.commons.Button,
    Column = sap.ui.table.Column,
    Right = sap.ui.commons.layout.HAlign.Right,
    Begin = sap.ui.commons.layout.HAlign.Begin,
    SelectionMode = sap.ui.table.SelectionMode;

// Specify the SAP Gateway SalesOrder service as an OData model.
// Please note: we do not connect directly to SAP Gateway (no host specified)
// Instead, the calls are made to the Node.js server,
// which will proxy the calls to SAP GW
// This way, we comply with Same Origin Policy of the browser. 
var salesOrderService =
        "/sap/opu/sdata/IWFND/SALESORDER",

    // SAP Gateway only supports XML, so don't use JSON
    asJson = false,
    salesOrderModel = new ODataModel(salesOrderService, asJson),
    salesOrderCollection = "SalesOrderCollection";

// Create a button to request an Excel workbook from server 
var button = new Button({
    text: "Download as Excel",
    icon: 'images/excel.png',
    iconFirst: false,
    height: '24px',
    press: function () {
        window.location = "/workbook"
    }
});

var toolbar = new Toolbar({ 
    items: [button]
});

// Create a master table with sales orders 
var salesOrders = new DataTable({
    title: "Sales Orders",
    width: "600px",
    visibleRowCount: 20,
    toolbar: toolbar,
    selectionMode: SelectionMode.None,
    editable: false
});

// define the relevant column options
var salesOrderColumns = [
    { header: "Sales Order ID", value: "{SalesOrderID}", width: '100px' },
    { header: "Customer Name", value: "{CustomerName}", width: '100%' },
    { header: "Net", value: "{NetSum}", width: '100px', hAlign: Right },
    { header: "Tax", value: "{Tax}", width: '100px', hAlign: Right },
    { header: "Total", value: "{TotalSum}", width: '100px', hAlign: Right }
];

// create the columns
salesOrderColumns.forEach(function (options) {
    var label = new Label({ text: options.header }),
        template = new TextView({ text: options.value }),
        column = new Column({
            label: label,
            template: template,
            width: options.width,
            hAlign: options.hAlign || Begin
        });

    salesOrders.addColumn(column);
});

// connect the data table to the SalesOrder service
salesOrders.setModel(salesOrderModel);

// An OData request for the SalesOrderCollection 
// will return the sales orders. 
// Each sales order should result in a table row.
salesOrders.bindRows(salesOrderCollection);

// Put table in the DOM.
// placeAt will automatically defer if 
// DOM is not ready yet (like in this demo).
salesOrders.placeAt("salesorders");

With that, our client application is in place.

Server application

Node.js includes core library functions to create an HTTP server and client. You have probably seen the famous 6 lines of code to create a ‘Hello world’ http server in Node.js. The core library is intentionally kept light-weight and (relatively) low-level. But is very easy to extend the base capabilities of Node by including modules in your code. The Node.js community has built a wide range of modules that handle common tasks like XML parsing, static file serving, request routing etc.

To keep our server application compact, we will use 2 of these modules:

  • express.js: a web application framework on top of Node, to provide request routing (connecting incoming requests to JS functions) and serving the static files (the html, js and css files for the client application).
  • xml2js: to parse the SAP Gateway OData response into a JavaScript object

To install the modules in your Cloud9 project, you can use NPM (Node Package Manager). Just issue the following commands in the command field:

npm install express
npm install xml2js

image

This will install the modules (and their dependencies) in the node_modules folder of your Cloud9 project. Click the refresh button next to Project Files and you can see the results.

Now we have the required modules installed, we can coding our server.js file which contains the server application code. Based on the code of our client application, the server application needs to respond to the following requests:

  • / : redirect requests without pathname (i.e. server name only) to index.html in the ‘public folder
  • /sap/*: forward (proxy) all requests for SAP Gateway content and include authentication
  • /workbook: generate an Excel workbook on the fly and deliver as download
  • otherwise: serve static files for the client app if contained in the ‘public’ folder

So here is server.js, the code to implement the server application

var http = require('http'),
    express = require('express'),
    xml2js = require('xml2js'),
    app = express.createServer(),
    port = process.env.PORT;

// The SalesOrder service requires authentication
// get the username/password from the SCN page. 
var username = 'USERNAME',
    password = 'PASSWORD';

var sapgw = {
  host: 'gw.esworkplace.sap.com',

  // set up Basic authentication
  headers: {
    'Authorization': 'Basic ' + new Buffer(username + ':' + password).toString('base64')
  }
}

// Action: Proxy an incoming (ie. from the user's browser) request to the SAP Gateway
function proxy(user_request, user_response) {

  // We're proxying calls,
  // so copy the path from the user request
  sapgw.path = user_request.url;

  // Use a client request to call SAP Gateway
  http.get(sapgw, function (sapgw_response) {

    // Include a content type in the response header
    user_response.header('Content-Type', 'application/atom+xml;type=feed');

    // In Node, http responses are streams. You can just
    // pipe the response data from the Gateway to the user.
    sapgw_response.pipe(user_response);
  });
}

// Action: Generate an Excel workbook containing SalesOrders on the fly  
function workbook(req, res) {

  // We will fetch the SalesOrderCollection from SAP Gateway
  sapgw.path = '/sap/opu/sdata/IWFND/SALESORDER/SalesOrderCollection';

  // Kick-off by fetching the SalesOrderCollection..       
  http.get(sapgw, function (sapgw_response) {
    var xml = '';

    // Every time Node receives a chunk of data
    // from SAP Gateway, the 'data' event fires.
    // We just collect all chunks into a string
    sapgw_response.on("data", function (chunk) {
      xml += chunk
    });

    // The 'end' event fires when the SAP Gateway response is done 
    // We can start processing the xml string...
    sapgw_response.on("end", function () {

      // Node.js doesn't automatically parse the XML (like XmlHttpRequest),
      // so we need to do that explicitly.
      // We will use the xml2js module to parse the XML string
      // into a JavaScript object
      // Create a parser to convert the XML to JavaScript object
      var parser = new xml2js.Parser();

      // The 'end' event fires when the parser is done.
      // The resulting JS object is passed as parameter.
      parser.on('end', function (result) {
        // The result parameter is a complete representation 
        // of the parsed XML string.
        // We need to extract the values we need to render the workbook
        var rows = [],

        // columns is the subset of properties we want to include
        // in the Excel list
        columns = ['SalesOrderID', 'CustomerName', 'NetSum', 'Tax', 'TotalSum'];

        // the value of result['atom:entry'] is an array of objects, 
        // representing 's in the XML string.
        // Each entry represents a SalesOrder
        result['atom:entry'].forEach(function (entry) {
          var row = {},
            // properties points to the parsed properties 
            properties = entry['atom:content']['m:properties'];

          columns.forEach(function (property) {
            // get the value for the property
            row[property] = properties['d:' + property]['#'] || 
                            properties['d:' + property];
          });

          // add it to the rows 
          rows.push(row)

        })

        // We're done processing the response and have extracted all data required to 
        // create the workbook.
        // First, include the proper HTTP headers
        res.header('content-disposition', 'attachment;filename=SalesOrders.xml');
        res.header('Content-Type', 'application/vnd.ms-excel');

        // Next, render the contents of Excel workbook as
        // Excel 2003 XML format, using the Workbook.ejs template
        res.render('Workbook.ejs', {
          layout: false,
          rows: rows
        });

      });

      // Set the parser in motion with the xml data received
      // from SAP Gateway. The parser will turn the XML string into
      // a JS object. When done, the 'end' event will fire
      // and the 'end' event handler defined above will extract the values
      // and render the Workbook.
      parser.parseString(xml);
    });

  });
};

// Action: perform a redirect to the home page (index.html)   
function home(req, res) {
  res.redirect('/index.html')
}

// Now we will set up the routing for the server...
// Look if the request is for a static file in the public directory
// this is where the client side html, css, js and SAPUI5 resources are located
app.use(express.static(__dirname + '/public'));

// If you just call the server (root), redirect to homepage
app.get('/', home);

// Requests starting '/sap/' are proxied to the SAP Gateway
app.get('/sap/*', proxy);

// Route for workbook action
app.get('/workbook', workbook);

// Routes are set up,
// Now start the app server...
app.listen(port);

console.log('Server started on port ' + port);

You can find the username/password for the SAP Gateway demo environment on this SCN page.

The server application uses an ejs view template to generate the Excel workbook content. Express expects these views by default in the ‘views’ directory. An ejs template is a text file interspersed with JavaScript (very similar to classic asp or jsp pages). The view renderer uses the template and provided parameter to output a text string, in our case the Excel content. Here’s the relevant part of the view template (rows are passed as a parameter):

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="5" 
         ss:ExpandedRowCount="<%= rows.length+1 %>" 
          x:FullColumns="1"
          x:FullRows="1" 
         ss:DefaultRowHeight="15">
   <Column ss:AutoFitWidth="0" ss:Width="77.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="138.75"/>
   <Column ss:StyleID="s67" ss:AutoFitWidth="0" ss:Width="56.25" ss:Span="2"/>
    <Row ss:StyleID="s62">  
            <Cell><Data ss:Type="String">SalesOrderID</Data></Cell>
            <Cell><Data ss:Type="String">CustomerName</Data></Cell>
            <Cell><Data ss:Type="String">Net</Data></Cell>
            <Cell><Data ss:Type="String">Tax</Data></Cell>
            <Cell><Data ss:Type="String">Total</Data></Cell>
    </Row>
    <% rows.forEach(function(row){ %>
        <Row>   
            <Cell><Data ss:Type="String"><%= row.SalesOrderID %></Data></Cell>
            <Cell><Data ss:Type="String"><%= row.CustomerName %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= row.NetSum %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= row.Tax %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= row.TotalSum %></Data></Cell>
        </Row>
    <% }) %>
</Table>
</Worksheet">

Now, let’s run the application in debugging mode on Cloud9. The Output tab will show the output from the application initialization and any console.log statements you include in the code. It also provides the url where you can access the running application.

image

Click on the server url and our application opens up:

SNAGHTMLb193d38

Click on the ‘Download as Excel’ button and Excel workbook with the Sales Orders will open:

SNAGHTMLb1e5875

OK, we now have a working application in Cloud9! There’s a lot more to explore in the IDE. For example, try the debugging options: you can set breakpoints, just like in desktop IDEs. Code execution on remote server will halt and you can explore variable and parameter values, etc. You can also push your changes to the central repository on GitHub.

Here, we will continue our Quick Tour by deploying the application we just completed to Windows Azure.

Step 3: Deploy to Windows Azure

Cloud9 makes deployment to Windows Azure very easy. This tutorial shows all the steps in detail. You can skip the first steps because we already have a working app on Cloud9. Please start at “Create a Windows Azure account’.

After the initial set up, you can request deployment to Azure:

image

Cloud9 will offer to create default configuration files for you. Please accept.

Deploying can take a few minutes, while the application is packaged, uploaded and deployed on Azure:

SNAGHTMLb3a5c8b

Once deployment is active, you can click on it and see the details image

The Windows Azure management tool provides full details on the deployment:

image

Our application is now available on Windows Azure:

SNAGHTMLfdb040

http://nodesapgw.cloudapp.net

You can try it! It’s live.

You’ll notice that it takes quite some time to load the page. This is mainly caused by dynamic loading of SAPUI5 resource files. Optimizations are certainly possible, but outside the scope of this article.

This concludes our demo project to show how you can front-end SAP Gateway using Node.js on Azure.

‘Just because you can, doesn’t mean you should’

That’s a profound adage, so let’s wrap-up with a number of suggestions  why you may consider front-ending SAP Gateway in the Cloud:

  • Provide access to multiple backend systems through a single domain (origin). This way, you comply with the Same Origin Policy.
  • Integrate SAP data in an Azure based cloud solution (E.g. a hosted .Net, MS-SQL solution)
  • Offload non-business data http traffic of SAP Gateway to a more cost-effective platform.
  • Avoid exposing the SAP Gateway end-point to the public internet
  • Low-latency traffic by using caching to prevent repeatedly asking the same data via SAP Gateway. E.g. for type-ahead and suggestion/select drop-boxes in HTML5 apps, low-latency response are required. This is becoming more important with mobile apps where quick selections/suggestion lists are preferred of manual input of texts
  • Scale-out on demand
  • Protocol conversion: you may want to build client applications using other protocols (e.g. WebSockets) to expose business data. E.g. consider real-time auctions.
  • Content conversion: for example turning business data into Excel sheet as demonstrated in our application
  • Identity conversion: a consumer facing web application may allow consumers to login with their Facebook or Twitter account using OAuth2. The web application server can connect to SAP Gateway with a functional account.

Let me know if you have other suggestions/use cases.

Thanks for reading!

Helpful resources