Skip to Content
Technical Articles
Author's profile photo Jay Malla

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

 

 

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Denys van Kempen  and Philip MUGGLESTONE - Hey SAP HANA XSA Gurus - it would be great if you can check out my Blog.  I have reviewed many of your HANA XSA videos on YouTube and would like to share my experience of integrating with Microsoft Office 365 Sharepoint that may come in handy for clients.  Thanks, Jay

      Author's profile photo Denys van Kempen
      Denys van Kempen

      Hi Jay,

      Looks good.

      (minor observation: for code snippets, why not use GitHub 😉

      Best,

      Denys

      Author's profile photo Jay Malla
      Jay Malla
      Blog Post Author

      Thanks Denys - that's a good idea.

      I do have it on Github but I need to make it a public repository.

      Thanks,

      Jay

      Author's profile photo Zameer Ahamad
      Zameer Ahamad

      Hi Denys/Jay,

      I went through the blog, Thanks for explaining in detail.

      As per the latest version of DP Agent and SAP note, now we can able to access the data through SharePoint and Office 365 sites directly.

      2726024 - Creating a Remote Source using SharePoint - SAP HANA Smart Data Integration

      As per the note for the Excel Adapter configuration, The file should be saved in excel format(.xlsx) in the SharePoint/office 365 to read the data.

      I have a scenario where we need to read the the DATA through SDI adapter in SharePoint/office 365 directly, the data was maintained in LISTS format.

      How we can read the data of LISTS format in SharePoint/Office 365 using SDI? We have any adapter for reading the data directly in the form of LISTS.

      As of now, We are converting the LIST data in excel format from SharePoint/Office 365 option and loading csv file manually.

      I can see we have one more option to download the data in XML and the file will be downloaded locally,is there any possibility we  can read the same XML data using URL in SDI adapter using any adapter from the SharePoint.

      Kindly suggest what is the best way to put data in HANA DB using SharePoint Lists automatically without manual effort.

       

      Thanks and Regards

      Zameer Ahamad

      Author's profile photo Ed Dupree
      Ed Dupree

      Thanks for this Jay - it's very useful - I can't find your follow on Tech Article on invoking this from a virtual procedure - can you point me towards it?

      Cheers