Integrating SAP HANA XSA with Microsoft Office 365 Sharepoint Excel using Microsoft Graph API and NodeJS

In this Blog, I would like to show how to read data from Microsoft Office 365 using Microsoft Graph API and NodeJS and then write this data into SAP HANA XSA tables.  Our scenario requirement was to pull data from Excel files on Sharepoint on Microsoft Office 365 and write this data to SAP HANA XSA container tables.  We wanted to use the SAP HANA File Adapter which has a section for Sharepoint Configuration.  However, the Excel adapter as of HANA 2.0 SPS 3 can only connect to Sharepoint 2013 on premise systems and not Sharepoint on the Microsoft Office 365 Cloud.  So we had to come up with an approach to address this requirement and develop it.  This Blog describes the approach which will hopefully help other folks needing to accomplish this type of scenarios for integrating Microsoft Office 365 data with SAP HANA XSA .  There will be another Blog that describes how to trigger our custom developed NodeJS application from a virtual procedure through the file adapter through SAP HANA Smart Data Integration (SDI) layer.

Here is a screenshot of the ExcelAdapter with the Sharepoint URL we are trying to connect with and the error that is returned stating that the connection to the Sharepoint URL does not work:

First thing first – we need to connect to Sharepoint on Microsoft Office 365.  Microsoft provides the Graph API which allows users to invoke APIs across most of Office 365 functionality which is very impressive including the Sharepoint integration capabilities.  Check out the Microsoft Graph API with the Microsoft Graph Explorer: Microsoft Graph Explorer

Once you get familiar with the API and see how simple, powerful and flexible it is, you will see that you can call the Graph API and pass the Sharepoint site, workbook, worksheet and cell ranges and get the data back in JSON format very easily.  Very cool!  For the Graph API, we need our Sharepoint site, GUID for the drive, workbook, worksheet and the range for the worksheet:

In my example, I have:

https://graph.microsoft.com/v1.0/sites//sites/mycompany.sharepoint.com,5304406e-30bd-4b4e-8bd0-704c8a2e6eaa,ba9d641d-14c9-41f8-a9d0-e8c6a2cda00e/drives/b%21bkAEU70wTkuL0HBMii5uqh1knbrJFPhBqdDoxqLNoA4HcPFP9eqPTIUQCSbiDtgZ/items/01HX4WN4RSH2GZKQXRWNEKR2S5YZLIUHOS/workbook/worksheets(%27{00000000-0001-0000-0000-000000000000}%27)/Range(address=%27Sheet1%21A1:C50%27)

Now in order to call this API, we need to register this application on the Azure portal.

Click on Add an app:

Give the application name

Get the application id and secret – we will need these for authentication to invoke the interface:

In our case, we have named this sharepoint_excel_outlook_graph_api_hana_integration – the name could have been shorter but we wanted to have it be self descriptive enough since there were many other applications for the company.

We need to set the permissions for the app to allow reading the sharepoint data:

In order for the service to be called with an OAuth token with just the application id and secret, we need to apply for admin consent by the Azure admin which will allow permissions for the application to be triggered in background mode.

So once we have the necessary setup done to read the data from Sharepoint, we will write the application using NodeJS.

Here is the main app.js module that allows the application to be invoked from the command line that uses our custom modules hdbutility.js and sharepointutility.js:

//////////////////////////////////////////////////////////////////////////////////////////////////
//  app.js - main module to read the file from Sharepoint Office 365 and then save into HANA
//  Author - Jay Malla @Licensed To Code
//////////////////////////////////////////////////////////////////////////////////////////////////

var hdbutility = require('./hdbutility');
var sharepointutility = require('./sharepointutility');
// Read all of the main configurable parameters
var config = require('config');

//////////////////////////////////////////////////////////////////////////////////////////////////
//  Here is our main....
//////////////////////////////////////////////////////////////////////////////////////////////////


var sharepointExcelURL;
var schema;
var table;
var sqlArrayResults;

console.log("Let's start this journey");


//Command line usage (Note that the exact sequence is important)
//node.exe app.js -f sharepointurl -s schema -t table

// Let's extract the variables sharepointurl, schema, table from the command line
process.argv.forEach(function (value, index, array) {
    console.log(index + ': ' + value);

    if (array.length < 8) {
        console.error("Not enough parameters supplied");
        throw (new Error("Not enough parameters supplied"));
    }

    switch (index) {
        case 3:
            console.log('FileName' + ': ' + value);
            sharepointExcelURL = value;
            sharepointExcelURL.replace('\\', '');
            break;
        case 5:
            console.log('Schema' + ': ' + value);
            schema = value;
            break;
        case 7:
            console.log('Schema' + ': ' + value);
            table = value;
            break;
    }
});

//If not supplied through command line, then read from the config file
//if (!schema) {config.get('schema')};
//if (!table) {config.get('table')};
//if (!sharepointExcelURL) {config.get('sharepointExcelURL')};
var hdbConnectionDetails = config.get('hdbConnectionDetails');
var oauth_info = config.get('oauth_info');

mainlogic();


//////////////////////////////////////////////////////////////////////////////////////////////////
//  mainlogic is the main function that runs the main logic
//////////////////////////////////////////////////////////////////////////////////////////////////
async function mainlogic() {
    try {

        // Set the credentials from the configuration module which has read the default.json
        const credentials = {
            client: {
                id: oauth_info.client_id,
                secret: oauth_info.client_secret
            },
            auth: {
                tokenHost: oauth_info.tokenHost,
                authorizePath: oauth_info.authorizePath,
                tokenPath: oauth_info.tokenPath
            },
            options: {
                bodyFormat: 'form',
                authorizationMethod: 'body'
            }
        };

        ////////////////////////////////////////////////////////////////
        // Use Sharepoint Utility to get Excel
        var sharepointclient = sharepointutility.createClient(credentials);
        sharepointclient.getExcelFileFromSharepoint(sharepointExcelURL, schema, table, oauth_info.scope)
        // If Excel file is retrieved
        .then(result => {
                console.log(result);
                console.log("Excel File retrieved as array of SQL statements");
                sqlArrayResults = result;
                ////////////////////////////////////////////////////////////////
                // Save to HANA Database
                var hdbclient = hdbutility.createClient(hdbConnectionDetails);
                hdbclient.setSchema(schema);
                hdbclient.setTable(table);

                hdbclient.insertIntoHANA_ReturningPromise(sqlArrayResults)
                    .then(result => {
                        console.log(result);
                        console.log("Data uploaded to SAP HANA Table");
                    })
                    .catch(error => {
                        console.error(error);
                        console.log("Could not upload the data to SAP HANA table.  Please fix issues and try again.  Check config file and input parameters.");
                    });
            })
            .catch(error => {
                console.error(error);
                console.log("Could not read the Excel file from Sharepoint");
            });

    } catch (err) {
        console.log(err);
    }
}

Here is the NodeJS code that uses the Microsoft Graph Client on NodeJS to connect to Sharepoint and then call the Graph API to read in the contents.  From the contents, the code then creates an array of SQL statements that is later used to insert the data into the SAP HANA tables:

//////////////////////////////////////////////////////////////////////////////////////////////////
//  sharepointutility.js - sharepoint module to integrate with Sharepoint
//  Author - Jay Malla @Licensed To Code
//////////////////////////////////////////////////////////////////////////////////////////////////

var graph = require('@microsoft/microsoft-graph-client');

// Class sharepointutility - object constructor function
function sharepointutility(credentials) {

    // Set the credentials Info
    this.credentials = credentials;


    // We need to store a reference to this - since we will need this later on
    self = this;

    //////////////////////////////////////////////////////////////////////////////////////////////////
    // This method async function connects to Sharepoint 
    this.getExcelFileFromSharepoint = async function getExcelFileFromSharepoint(sharepointExcelURL, schema, table, inputscope) {
        return new Promise(async function (resolve, reject) {

            self.sharepointExcelURL = sharepointExcelURL;
            self.schema = schema;
            self.table = table;
            self.inputscope = inputscope;

            const oauth2 = require('simple-oauth2').create(credentials);
            var accessToken;

            const tokenConfig = {
                scope: inputscope // also can be an array of multiple scopes, ex. ['<scope1>, '<scope2>', '...']
            };

            // Get the access token object for the client
            try {
                const result = await oauth2.clientCredentials.getToken(tokenConfig);
                accessToken = result.access_token;
            } catch (error) {
                console.log('Access Token error', error.message);
                reject(error);
                return;
            }

            // Initialize Graph client
            const client = graph.Client.init({
                authProvider: (done) => {
                    done(null, accessToken);
                }
            });

            ////////////////////////////////////////////////////////////////
            // Get the Sharepoint Excel file
            var sharepointurl = sharepointExcelURL;
            const result = await client
                .api(sharepointurl)
                .get();
            ////////////////////////////////////////////////////////////////

            ////////////////////////////////////////////////////////////////
            // Let's do an HTTP post for the same:

            var request = require('request');
            var bearer = "Bearer " + accessToken;
            var httpurl = "https://graph.microsoft.com/v1.0";

            // Set the headers
            var headers = {
                'Content-Type': 'application/json',
                'Authorization': bearer
            }

            // Configure the request
            var options = {
                url: httpurl + sharepointurl,
                method: 'GET',
                headers: headers
            }

            // Start the request
            request(options, function (error, response, body) {
                if (!error && response.statusCode == 200) {
                    // Print out the response body
                    console.log(body);
                    var sqlArrayResults;
                    sqlArrayResults = self.generateSQLarrayFromResults(result.formulas);
                    resolve(sqlArrayResults);
                    return;
                }
            })
            ////////////////////////////////////////////////////////////////



/*
            ////////////////////////////////////////////////////////////////
            // Convert the Excel file results to an array of SQL
            var sqlArrayResults;
            sqlArrayResults = self.generateSQLarrayFromResults(result.formulas);
            resolve(sqlArrayResults);
            return;
            ////////////////////////////////////////////////////////////////
 */
        });
    };

    this.generateSQLarrayFromResults = function generateSQLarrayFromResults(sharepointTable) {
        var columnsString;
        var sqlArray = [];

        sharepointTable.forEach((element, index) => {

            //Assumption - the first row has the table headings
            if (index == 0) {

                var processArray = (array) => {

                    var sqlString = "(";

                    array.forEach((element, index) => {
                        console.log(element);
                        if (index < (array.length - 1)) {
                            sqlString = sqlString + element + ",";
                        } else {
                            sqlString = sqlString + element + ")";
                        }
                    });

                    return sqlString;
                }
                columnsString = processArray(element);

            } else {
                if (element[0] != '') { //As long as there are other entries                                                                                                  
                    var valuesArrayString;
                    var tempString = "insert into \"" + this.schema + "\".\"" + this.table + "\" " + columnsString + " values "; // + element[0] + "," + "'" + element[1] + "'" + "," + "'" + element[2] + "'" + ")";

                    var processValuesArray = (array) => {

                        var sqlString = "(";

                        array.forEach((element, index) => {
                            console.log(element);

                            if (index < (array.length - 1)) {
                                if (typeof (element) == "number") {
                                    sqlString = sqlString + element + ",";
                                } else {
                                    sqlString = sqlString + "'" + element + "'" + ",";
                                }
                            } else {
                                if (typeof (element) == "number") {
                                    sqlString = sqlString + element + ")";
                                } else {
                                    sqlString = sqlString + "'" + element + "'" + ")";
                                }
                            }
                        });
                        return sqlString;
                    }

                    var valuesArrayString;
                    valuesArrayString = processValuesArray(element);
                    tempString = tempString + valuesArrayString;
                    console.log(tempString);
                    sqlArray.push(tempString);
                }
            }
        });

        return sqlArray;
    }
}

//////////////////////////////////////////////////////////////////////////////////////////////////

exports.createClient = function (credentials) {
    return new sharepointutility(credentials);
}
//////////////////////////////////////////////////////////////////////////////////////////////////

Here is the code that connects to the HANA database and inserts the data into the tables – note that the schema and table names are parameterized:

//////////////////////////////////////////////////////////////////////////////////////////////////
//  hdbutility.js - database module save into HANA
//  Author - Jay Malla @Licensed To Code
//////////////////////////////////////////////////////////////////////////////////////////////////


// Class hdbutility - object constructor function
function hdbutility(hdbConnectionInfo) {

  // Set the hdbConnection Info
  this.hdbConnectionInfo = hdbConnectionInfo;

  //property method to set schema name
  this.setSchema = function (schema) {
    this.schema = schema;
  };

  //property method to set the table name
  this.setTable = function (table) {
    this.table = table;
  };

  // We need to store a reference to this - since we will need this later on
  self = this;

  //////////////////////////////////////////////////////////////////////////////////////////////////
  // This method async function runs the SQL Array of statements in the HANA database - but order is not gauranteed 
  this.insertIntoHANA_ReturningPromise = async function insertIntoHANA_ReturningPromise(sqlArray) {
    return new Promise(function (resolve, reject) {

      var inputSQLArray = sqlArray;
      var results = [];

      var hdb = require('hdb');
      var hdbclient = hdb.createClient(self.hdbConnectionInfo);

      hdbclient.on('error', function (err) {
        reject(err);
        return;
      });

      hdbclient.connect(function (err) {

        if (err) {
          reject(err);
          return;
        }

        // First delete the entries from the table
        var strQuery = 'delete from \"' + self.schema + '\".\"' + self.table + '\"';
        hdbclient.exec(strQuery, function (err, rows) {

          //hdbclient.end();
          if (err) {
            reject(err);
            return;
          }
          console.log('Table Contents before SQL Inserts:', JSON.stringify(rows));

          /////////////////////////////////////////////////////////////////////////
          // Recursive approach to go through Array and execute SQL statements
          var iterateOverArray = (index) => {
            // if the end of Array reached..
            if (index == inputSQLArray.length) {
              // Read all of the the entries in that table and log this to see if all records inserted...
              strQuery = 'select * from \"' + self.schema + '\".\"' + self.table + '\"';
              hdbclient.exec(strQuery, function (err, rows) {
                hdbclient.end();
                if (err) {
                  reject(err);
                  return;
                }
                console.log('Table Contents After SQL Inserts:', JSON.stringify(rows));
                resolve(JSON.stringify(rows));
                return;
              });

            } else {
              // If the end of the Array has not been reached....
              // Execute the insert into the table
              hdbclient.exec(inputSQLArray[index], (err, rows) => {

                //hdbclient.end();
                if (err) {
                  console.error('Execute error:', err);
                  //return callback(err);
                  reject(err);
                  return;
                }
                //otherwise capture the results and move to the next array member for the iteration
                console.log('Results executing SQL ' + inputSQLArray[index] + ' = ' + JSON.stringify(rows));
                results.push('Results executing SQL ' + inputSQLArray[index] + ' = ' + JSON.stringify(rows));
                iterateOverArray(index + 1);
              });
            }
          }

          /////////////////////////////////////////////////////////////////////////
          //Calling the recursive function...
          iterateOverArray(0); // Initiate the recursive function that iterates through the array and executes the SQL
          /////////////////////////////////////////////////////////////////////////

        });
      });
    });
  }
}

//////////////////////////////////////////////////////////////////////////////////////////////////
exports.createClient = function (hdbConnectionInfo) {
  return new hdbutility(hdbConnectionInfo);
}

//////////////////////////////////////////////////////////////////////////////////////////////////

So once we have the NodeJS program that we can invoke from the command line to read the data from Sharepoint and write the data into HANA, we need HANA to trigger this on demand dynamically.  So here is where HANA has this nice feature of a Virtual procedure using the file adapter that allows us to call our NodeJS program from the command line with dynamic parameters.  These details will be in another Blog to follow which will be available very soon.

I hope you found this Blog useful.  Note that you can use this code for invoking many other Microsoft Office 365 data and loading them into SAP HANA.  Please do leave your feedback and comments.

Thanks,

Jay

You can connect with me at – Jay Malla on LinkedIn