Skip to Content
Technical Articles

Creating a Google Chatbot connected to SAP Graph API

<<Disclaimer: though I am a current employee at Google and former employee at SAP, I am not a developer and the code below shall not serve as a production version and can only support proof-of-concept. Also, the opinions expressed in this article are my own and do not reflect the official opinion or positioning of Google or SAP>>

You know that idea you always keep at the bottom of your backlog for that elusive day you’ll find enough time to address? For me, it’s the possibility to connect a chatbot to an SAP system. And it only took a global lockdown to finally get to it. Let’s do this !

Creating a Google Chatbot connected to the SAP Graph API

What you need

In a typical full implementation of a solution to link a chatbot to a backend system, there are lots of technical requirements like a serverless service to handle the communication or a complex firewall routing to your backend. However, thanks to Google Meets, Google Apps Script, and SAP Graph API, you can start building a proof of concept today, and the only thing you’ll need is a Google G Suite Account.

Note: The Google Chat application, and the developer platform, are only available to G Suite accounts. You will not be able to develop or test a bot with only an @gmail.com account.

High-Level Diagram

Step 1: Activating the chatbot

The first step is to be able to interact with a chatbot. I chose the Google Chat API mostly because I use it daily but also because the developers tutorial is very straightforward: Google Apps Script bot for Google Chat.

Warning: if you have multiple Google Accounts, always make sure that the correct account is selected at the top right of the Google Apps Scripts screen

Create a chatbot from template

Simply copy the Chat Bot template, rename it, and save it. Then, use menu `Publish` > `Deploy from Manifest…` before clicking `Get ID` to copy the Deployment ID.

Warning: at time of publication, the new UI editor in Google Apps Scripts does not yet support the Publish function. Make sure to use the classic editor as in the screenshots

The Google Chat bot Template. Source: Google

Getting the Deployment ID from the Apps Scripts App. Source: Google

Publish the chatbot

Next, we need to publish the chatbot. Simply follow the Publishing Bots instructions. This is done from the Google API Console. First, create a Project (e.g. SAP Chatbot). 

Warning: if you have multiple Google Accounts, make sure that the correct one is selected at the top right of the Google Apps Scripts screen

Create a new Project to Enable the API. Source: Google

Select the Project in the API Console. Source: Google

Configure the Google Chat API

Make sure the right Project is selected at the top left. Search and select the Google Chat API. Once activated, click on `Manage` and start the configuration. Under `Connection Settings`, select `Apps Script Project` and paste your Deployment ID.

Activate the Chat API. Source: Google

Click Configuration for Google Chat API. Source: Google

Google Chat API Configuration. Source: Google

Test the template chatbot

From chat.google.com, use the `Find People, rooms, bots1 box and add your new chatbot. Type anything in the chat box and the robot will just repeat it back to you.

Search for your chatbot. Source: Google

Testing the chatbot. Source: Google

This might not be impressive yet, but you’ve just created a chatbot and completed Step 1. Now is the time to discover the SAP side of the equation.

Step 2: Connect to the SAP Graph API

SAP announced at the TechEd 2019 the release of a beta version of its Graph API, as “the easy-to-use API for the data of the Intelligent Enterprise from SAP. It provides an intuitive programming model that you can use to easily build new extensions and applications using SAP data.” (source

Testing the Graph API

My favorite feature is the API Sandbox, which enables the testing of all available functions. Go to beta.graph.sap and click on `Explore API Sandbox`. In the `Sample Queries` box, type `Sales Orders`, open the `Sales Orders` menu and click `Retrieve a list of sales orders.`. In the Explorer, confirm the service URL (https://api.graph.sap/beta/SalesOrders) and click `Run Query`. 

Search for the Sales Orders service. Source: SAP

Running the Default Sales Orders query. Source: SAP

Parameters can be passed to the query. For instance, to reduce the load on the demo server, let’s only retrieve the top 5 orders based on the Gross Amount, click on `Query Parameters` and enter `$top=5` and `$orderby=grossAmount desc`. Notice how the service URL gets updated. Run the query,


Running the Sales Orders query for the top 5 by Gross Amount. Source: SAP

Last, click on the `Headers` link next to the `Body` and `Query Parameters`. Copy and save the `Authorization` token.

Note: Copy / pasting the authorization token is only a shortcut for this tutorial. In production, the token should be short-lived and user-specific. Therefore, they couldn’t / shouldn’t be hard-coded.

The authorization token under the Headers tab. Source: SAP

Calling the Graph API from the Google Chat Bot

Note: all the code listed in the current document can be found in this GitHub repository

We now have a Google Chat bot and we’re familiar with the SAP Graph API. Let’s connect the two. In the Google Apps Scripts, let’s create a new variable to store the token and new function to perform the API call. We will need to build the right headers for authorization based on the token and parse the returning JSON file.

Code from Test.js

var TOKEN = <<TOKEN>>;

function testAPI() {
  // URL to the API
  var url = 'https://api.graph.sap/beta/SalesOrders'; // SAP Graph API

  // Pass security credentials
  var headers = {'Authorization':'Bearer ' + TOKEN }

  // Pass headers
  var options = {
    'method': 'GET',
    'contentType': 'application/json',
    'headers': headers
  };

  // Perform the call and parse the JSON result
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Only keep the ‘value’ element of the response
  var valueList = data['value'];

  console.log(valueList); 
}

Save your changes and call menu `Run` > `Run function` > `testAPI`. Then, call menu `View` > `Logs`. You should see a JSON response similar to this one.

Logs from test API call. Source: Google

At this point, you have connected the pieces of the puzzle. Your chatbot can interact with the SAP Graph API. All we need is a nice user experience.

Step 3: Build the user experience

A perfect chatbot should be able to interpret intentions and parameters from a natural conversation. Dialogflow does this brilliantly but connecting to it is not the intention of this blog post. For now, we’ll rely on the `interactive cards` concept to support the user experience.

Building the menu card

Menus work with buttons that contain at a minimum a text and an action. Let’s create a function `buildCardMenu` with a list of buttons. For now, a single button to display all sales orders will be enough. Let’s also create a function `createCardResponse` to format the message correctly. In the default `onMessage` function, let’s simply call both functions.

Code from Code.gs

/**
 * Responds to a MESSAGE event in Google Chat.
 *
 * @param {Object} event the event object from Google Chat
 */

function onMessage(event) {
  var widgets = buildCardMenu();
  return createCardResponse(widgets);
}

/**
* Build Card for default Menu
*/

function buildCardMenu() {
  // Prepare a collection of buttons
  var buttonList = [];
  
  // Add button for Sales Orders
  var button = {textButton: {
    text: 'Sales Orders<br/>',
    onClick: {
      action: {
        actionMethodName: 'displaySalesOrders'
          }
        }
    }};
  buttonList.push(button);

  // Collect all buttons and add header
  var widgets = [{
    textParagraph: {
      text: '<b>Please select a command</b><br/>'
    }
  }, {
    buttons: buttonList
  }];
 
  return widgets;
}

/**
* Create Card Response
* @param{object} widgets - content for the card
*/
function createCardResponse(widgets) {
  return {
    cards: [{
      sections: [{
        widgets: widgets
      }]
    }]
  };
}

Now, if you send any content to the chatbot, it will return the menu card. If you click on it, nothing should happen. Let’s solve that.

Google Chat Bot showing a Menu Card

Building the list card

To react to the buttons, let’s expand on the default `onCardClick` function. Let’s create and call a new function `getSalesOrders`, reusing the content of our test API call. The goal here is to build a list with ID, amount, and currency code. Don’t forget to add the token variable at the top of your script.

In order to reuse the code for future calls to lists of customers or lists of items, we will separate the API call from the card building. Therefore, let’s create and call a new function `buildCardList` with the list from `getSalesOrders`. Notice how the button will call the action ‘displaySalesOrderById’ with the corresponding ID.

/**
* Default Values
*/
var TOKEN = <<TOKEN>>;

/**
 * Responds to a CARD_CLICKED event triggered in Google Chat.
 * @param {object} event the event object from Google Chat
 * @return {object} JSON-formatted response
 * @see https://developers.google.com/hangouts/chat/reference/message-formats/events
 */
function onCardClick(event) {
  // React to buttons clicked on the cards based on the actionMethodName
  var content = '';
  var widgets = '';
  switch( event.action.actionMethodName ) {
    case 'displaySalesOrders':
      content = getSalesOrders();
      widgets = buildCardList(content);
      break;
      
    default:
      return { 'text': 'Unknown command' };
  }
  
  return createCardResponse(widgets);
}

/**
* Build Card Format for Lists
* @param(array) content - list of values with id / amount / currency code
*/
function buildCardList(content) {
  // Build a array of buttons with item as id / amount / currency_code
  // Use id as parameter for the button
  var buttons = [];

  // Process each of the order items in the content array
  for( var i = 0; i < content['values'].length; i++ ) {
    var content_line = content['values'][i];

    // Convert id / amount / currency as string
    // Such as 1234: 245 USD
    // Each line becomes a button for the use to click
    var button_text = {textButton: {
      text: content_line['id'] + ' : ' + content_line['amount'] + ' ' + content_line['currency'] + '<br/>',
        onClick: {
          action: {
            actionMethodName: 'displaySalesOrderById',
            parameters: [{
              key: 'id',
              value: content_line['id']
            }]
          }
        }
    }};
    buttons.push(button_text);
  }

  // Collect all buttons and add header
  var widgets = [{
    textParagraph: {
      text: '<b>' + content['type'] + '</b>'
    }
  }, {
    buttons: buttons
  }];

  return widgets;
}

/**
* Get Sales Orders from the SAP Graph API
*/
function getSalesOrders() {
  // Build the call to the SAP Graph API
  // URL to the API
  var url = 'https://api.graph.sap/beta/SalesOrders?$top=5&$orderby=grossAmount desc'; 

  // Pass the security credentials
  var headers = {'Authorization':'Bearer ' + TOKEN }

  // Pass headers
  var options = {
             'method': 'GET',
             'contentType': 'application/json',
             'headers': headers
             };

  // Perform the call and parse the JSON result
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Only keep the ‘value’ element of the response
  var value_list = data['value'];

  // Build the return list as an array with id / amount / currency
  var order_list = [];
  for( var i = 0; i < value_list.length; i++ )
  {
    order_list.push( {'id' : value_list[i]['id'],
                      'amount' : value_list[i]['grossAmount'],
                      'currency' : value_list[i]['currency_code']} );
  }

  // Build header and combine with order list
  var content = { 'type' : 'Top 5 Sales Orders by Gross Amount',
                 'values' : order_list };   

 return content;
}

Now, if you call the chatbot, the result should look like this. 

Google Chat Bot showing a List Card

Building the details card

Last, we need to display the details of a selected Sales Order. To do so, let’s expand the `onCardClick` function to handle the `displaySalesOderById` event, passing the ID parameter.

We also need a new function `getSalesOrderById` similar to the `getSalesOrder` to list all fields and corresponding values (note that the list of fields is not exhaustive in the coding below).

Once that is done, let’s create a new function `buildCardDetails` to build a card out of the field / value pairs.

/**
 * Responds to a CARD_CLICKED event triggered in Google Chat.
 * @param {object} event the event object from Google Chat
 * @return {object} JSON-formatted response
 * @see https://developers.google.com/hangouts/chat/reference/message-formats/events
 */
function onCardClick(event) {
  // React to buttons clicked on the cards based on the actionMethodName
  var content = '';
  var widgets = '';

  // Distribute actions based on the actionMethodName
  switch( event.action.actionMethodName ) {
    case 'displaySalesOrders': // Display Sales Orders
      content = getSalesOrders();
      widgets = buildCardList(content);
      break;
      
    case 'displaySalesOrderById': // Display one Sales Order with Id
      content = getSalesOrderById(event.action.parameters[0]['value']);
      widgets = buildCardDetails(content);
      break;
      
    default:
      return { 'text': 'Unknown command' };
  }

  // Convert response to the right card format
  return createCardResponse(widgets);
}

/**
* Get Sales Orders for specific Sales Order Id from the SAP Graph API
* @param {id} Identification of the Sales Order
*/
function getSalesOrderById(id) {
  // Build the call to the SAP Graph API
  // URL to the API
  var url = 'https://api.graph.sap/beta/SalesOrders/' + id; // SAP Graph API

  // Pass security credentials
  var headers = {'Authorization':'Bearer ' + TOKEN }

  // Pass headers
  var options = {
    'method': 'GET',
    'contentType': 'application/json',
    'headers': headers
  };

  // Perform the call and parse the JSON result
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  // Collect all values as field name / value pairs as an array
  // Example: {‘field’:’Sales Order ID’, ‘value’: 1234 }
  var order_list = [];
  order_list.push( {'field' : 'Sales Order ID', 'value' : data['id'] } );
  order_list.push( {'field' : 'Gross Amount'  , 'value' : data['grossAmount'] } );
  order_list.push( {'field' : 'Tax Amount'    , 'value' : data['taxAmount'] } );
  order_list.push( {'field' : 'Net Amount'    , 'value' : data['netAmount'] } );
  order_list.push( {'field' : 'Currency'      , 'value' : data['currency_code'] } );
  order_list.push( {'field' : 'Customer ID'   , 'value' : data['customerID']  } );
  order_list.push( {'field' : 'Contact ID'    , 'value' : data['contactID'] } );
  order_list.push( {'field' : 'Ship To ID'    , 'value' : data['shipToID'] } );
  order_list.push( {'field' : 'Owner ID'      , 'value' : data['ownerID'] } );
  order_list.push( {'field' : 'Order Date'    , 'value' : data['orderDate'] } );

  // Build header and combine with field list
  var content = { 'type' : 'Details for Sales Order: ' + id,
                  'values' : order_list };   

 return content;  
}

/**
* Build Card Details
*/
function buildCardDetails(content) {
  // Build the return card showing each field and value
  // Values were passed in content as a array of field name / value pairs
  // Example: {‘field’:’Sales Order ID’, ‘value’: 1234 }
  // Convert to a string, such as Sales Order ID : 1234
  var text = "";
  for( var i = 0; i < content['values'].length; i++ ) {
    var content_line = content['values'][i];
    text += content_line['field'] + ' : ' + content_line['value'] + '<br/>';
  }

  // Return the header and the content
  var widgets = [{
    textParagraph: {
      text: '<b>' + content['type'] + '</b><br/>' + text
    }
  }];

  return widgets;
}

If all goes according to plan, the chatbot should be able to display something like this:

Google Chat Bot showing a Details Card

Notes on debugging

At times, your chatbot might become unresponsive. This can happen for a number of reasons but in my experience, it is frequently caused by wrongly formatted messages. The easiest way to solve these problems is to use the menu `View` > `Stackdriver Logging`, which leads to the Apps Script Dashboard. You can also use commands like Loggger.log() or Console.log() to create a trace of your code.

List of executions and status from the Apps Script Dashboard. Source: Google

Future Steps

Connect to your own SAP system

In this exercise, we have been using the SAP Graph API sandbox, which is only a developer preview. You can register on beta.graph.sap to try and join the short list of pre-selected partners who have access to the full solution.

Connect to DialogFlow

The current user experience is very guided and limited. By connecting to a full chatbot engine like DialogFlow, the end-user experience will be vastly improved. Typical queries could look like “show me all the open sales orders” or “what are the open quotes for customer XYZ ?” or “give me a list of all finished products”. This could also be supported in multiple languages.

Publication and Authorization

The code we just built supports one-on-one interaction in the chat but would need to be extended to chat rooms. Also, with proper authorization setup, the chatbot would be able to access some information like the user ID to match with the SAP owner ID field in order to deliver more advanced scenarios like “show me my list of open sales quotes”.

Conclusion

If you were able to follow all the steps to completion, congratulations! You now have the building blocks of a full Google Chat Bot connected to an SAP system via its Graph API. It is now your turn to expand on these concepts and share your experience with the rest of the communities !

More Information

 

2 Comments
You must be Logged on to comment or reply to a post.