Google Sheets and Google Script calling SAP HANA Express

[lsubatin]

This is the third blog post, following the initial description of the requirement to integrate the results from a Predictive algorithm in HANA Express Edition. I will describe the last steps to have this integration up and running from the Google Sheets side.

Of course, you will need to create a Google Spreadsheet. Once you are there, you need to create a Google script. It sounds complicated, but it is as easy as going into Tools -> Script editor.



 

This will create a script that will be bound to your Google Spreadsheet and only to this Spreadsheet file. This means you will not see it as a separate executable script in your Google Drive and the behavior you program here will not show in other spreadsheets unless they are a copy of this Spreadsheet.

First, you need to create the custom menu. This is what I used for my spreadsheet:

function onOpen() {

  

//Adding the menu

  var spreadsheet = SpreadsheetApp.getActive();

  var menuItems = [

    {name: 'Get Employees', functionName: 'getEmployees'},

    {name: 'Get Expenses', functionName: 'getExpenses'},

    {name: 'Forecast', functionName: 'getForecast'},

  ];

  spreadsheet.addMenu('Expense Management', menuItems);

}

 

All of those functions are similar, so I will only go into the details of the most important one, the Forecast call, in a more generic fashion.

We want to create a brand new spreadsheet, so we skip validations of the current sheet and wheher it has data or not.  So this is how our function starts:

function getForecast() {

  

  var spreadsheet = SpreadsheetApp.getActive();

  

  if (!spreadsheet.getSheetByName("Forecast")){

      var foreSheet = spreadsheet.insertSheet("Forecast");

    } else {

      var foreSheet = spreadsheet.getSheetByName("Forecast");

      foreSheet.clear({contentsOnly:true});

    }

We are now going to call the our REST API, parse the data and present it in the cells in our sheet:

var forecastURL = "https://host:port/HANAGoogleSheets/expenses.xsodata/Forecast?$format=json&$orderby=PERIOD";

var options = { 'contentType' : "application/json" }; //you may also need 'validateHttpsCertificates' : false if you haven't installed properly signed certificates



var request = UrlFetchApp.getRequest(forecastURL, options);

Logger.log(request); //This is like console.log()



var response = UrlFetchApp.fetch(forecastURL, options);

var dataAll = JSON.parse(response.getContentText());

var data = dataAll.d.results; //Check the structure 



//Appending the tile - yes, you could get this dynamically from the metadata

foreSheet.appendRow([ "Month", "Year", "Period", "Amount in USD", "Value Type" ]);



//Adding some format - you could also do this dynamically  with foreSheet.getLastColumn() and foreSheet.getLastRow();



foreSheet.getRange("A1:E1").setFontWeight("bold");



//Looping and appending the data

  for (i in data){

    foreSheet.appendRow([ data[i].month, data[i].year, data[i].PERIOD, data[i].amount, data[i].VALUE_TYPE ]);

  

   }

Simply because everybody likes charts and they aid data visualization, you may want to add this code, where range are the columns you want taken into account for your LINE chart.

  var chart = foreSheet.newChart()

     .setChartType(Charts.ChartType.LINE)

     .addRange(range)

     .setPosition(3, 7, 0, 0)

     .build()



  

  foreSheet.insertChart(chart);

You could also create a PIE chart full of colors too!