I’ll gladly admit: I was really excited when the announcement came last week that Google Docs and Sheets would be open to third-party add-ons developed in Google Apps Script.  It opens the door to all sorts of interesting application integration capabilities, and enhances them with the collaboration tools and ubiquity of Google Drive.

The software developer in me immediately thought “I’ll bet I can plug that into Gateway!”  The power of SAP NetWeaver Gateway to take anything in a Business Suite system and expose it for consumption through OData REST services means that a wide variety of consumers can make use of this data – and Docs and Sheets are no exception.  There’s a built-in API for communicating with other internet services, powerful XML parsing, and even support for an html-based UI paradigm.

So here’s how I created a simple add-on to read and display data from a Gateway service in a Google Sheets document.  When it’s all finished and pulling data, it should look something like this (including the sample data I pulled from my service):

final look apps script project.PNG

Prerequisites:

  • A Google account, so you can create documents on your Drive.
  • A Gateway service that returns some sort of useful entity data.  I created a simple service that just spits out contrived data for contracts that I stored in a z-table on the SAP side – but this could be any service you want.
  • Basic knowledge of javascript.  Really not much expertise needed – it doesn’t take much code to hook up this minimalist tool.

Set up the Apps Script Project:

  • Create a spreadsheet anywhere in your Drive:
    • Sign in at docs.google.com
    • Click the “Create” button on the left side
    • Choose “Spreadsheet”
  • Create an Apps Script project:
    • Open the new spreadsheet
    • Click the “Tools” menu
    • Choose “Script Editor…”
    • On the wizard that appears, choose “Blank Project”

You should see an untitled project with one source code file (Code.gs) and an empty myFunction() declaration like this:

beginning apps script project.PNG

You can name the project anything you like.  When the project is complete and running in your spreadsheet, the name of the project will be the first layer of the add-on menu to navigate to this functionality. 


Build Your Code:

We can code up this simple example in about 150 lines of javascript and html.  I’ll split the coding into 3 blocks so as to explain what is going on.

First, take out all the existing code in the Code.gs file.  Replace it with the below code:


function onInstall() { 
   onOpen(); 

  
function onOpen() { 
   SpreadsheetApp.getUi().createAddonMenu() 
     .addItem('Retrieve SAP Data', 'showSidebar') 
     .addToUi(); 

  
function showSidebar() { 
   var ui = HtmlService.createHtmlOutputFromFile('Sidebar') 
              .setTitle('Data Viewer'); 
  
   SpreadsheetApp.getUi().showSidebar(ui); 
  


This code is adding the menu under the “Add-ons” menu that appears on the spreadsheet.  It enables you to have the menu structure you wish for your application.  In the next step we’ll create the html file necessary to render a sidebar menu.


Why is there an onInstall() and an onOpen(), and onInstall() just calls onOpen()?  It has to do with how installations work in Apps Script.  When you install an add-on, your document is already open and thus does not trigger the onOpen() event.  The onOpen() method is added to onInstall() so that when the script is installed, it also performs the menu setup and other tasks that would normally occur when the document opens.


The addItem() call is registering the function showSidebar() to be triggered when you click on “Retrieve SAP Data”.  This is how we’re going to open the sidebar menu that we create.


Here’s what mine looks like:

menu structure.png

Next, create an html file in your project.  In the menus follow File…New…Html file, and name your html file “Sidebar”.  Put the following code in:


<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> 
<!-- The CSS package above applies Google styling to buttons and other elements. --> 
  
<div class="sidebar branding-below"> 
   <form> 
   <div class="block" id="credentials">SAP Connection Information<br> 
     <input type="text" id="serviceaddress" placeholder="Service Address"><br> 
     <input type="text" id="userid" placeholder="User ID"><br> 
     <input type="password" id="password" placeholder="Password"> 
   </div> 
   <div class="block" id="button-bar"> 
     <button class="blue" id="read-data">Read Data</button> 
   </div> 
  </form> 
</div> 
  
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"> 
</script> 
<script> 
  
   $(function() { 
     $('#read-data').click(readData); 
   }); 
  
   function readData() { 
  
     var token = $('#userid').val() + ':' + $('#password').val(); 
     var serviceaddress = $('#serviceaddress').val(); 
  
     //Call out to the Code.gs script function readContracts 
     google.script.run 
       .readData(token, serviceaddress); 
  
  } 
  
</script> 


Here we’ve defined a simple user interface that has four components: an input that accepts a URI for the Gateway service you want to read, an input that accepts an SAP user name, an input that accepts an SAP password, and a button that triggers the read activity.  We’ve also used the included jQuery to register an event on the button so that when you click “Read Data” you’ll call out to the appropriate function to read the data you’ve chosen.  The nice thing about the html files used for the sidebar is that they can be customized with javascript/css/html like any other part of the web, so they can be very flexible and built to suit a lot of different needs.


Next, go back to the Code.gs file, and insert the below code after the showSidebar() function.


function readData(authinfo, serviceaddress) { 
  
   //Clean up anything currently on the spreadsheet 
   SpreadsheetApp.getActiveSheet().clear(); 
  
   try { 
     var options = { 
       headers: { 
         'Authorization': 'Basic ' + Utilities.base64Encode(authinfo) 
       }, 
     }; 
  
     //UrlFetchApp is a powerful built-in library from Google 
     var response = UrlFetchApp.fetch(serviceaddress, options); 
  
     //Various XML parsing. 
     var xml = response.getContentText(); 
     var document = XmlService.parse(xml); 
     var root = document.getRootElement(); 
     var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom'); 
     var entries = document.getRootElement().getChildren('entry', atom); 
  
     //This loop handles the writing of the response data 
     //to the spreadsheet. 
     var row = 1; 
     for (var i = 0; i < entries.length; i++) { 
       //Navigate the xml response document to get properties/values
       var content = entries[i].getChildren('content', atom); 
       var properties = content[0].getChildren(); 
       var values = properties[0].getChildren(); 
    
       //First pass through the outer loop we will use to also write 
       //the header row, using the properties as column headers. 
       if (i == 0) { 
         var headercolumn = 0; 
         for (var j = 0; j < values.length; j++) { 
           headercolumn++; 
           SpreadsheetApp 
             .getActiveSheet() 
             .getRange(1, headercolumn) 
             .setValue(values[j].getName()); 
         } 
       } 
    
       //Now write the values of the properties 
       row++; 
       var column = 1; 
       for (var j = 0; j < values.length; j++) { 
         SpreadsheetApp 
           .getActiveSheet() 
           .getRange(row, column) 
           .setValue(values[j].getText()); 
    
         column++; 
       }
     } 
   } 
   catch(err) { 
     //Write any retrieve or parse errors into A1 of the main sheet 
     SpreadsheetApp 
       .getActiveSheet() 
       .getRange('A1') 
       .setValue(err.message); 
   }   


This function is the meat and potatoes of this demo.  We’re handling basic authorization, the http request and retrieve of service data, and parsing/writing that information into the document.  If I was writing this code for a professional application you can bet I’d be following a few more Code Complete conventions.


(You can see that in this demo we use basic authentication: taking the user ID and password from the sidebar, using the Google provided Utilities.base64Encode(), and passing that into the header of the request. To turbocharge your security/single sign-on, Google also provides some built-in OAuth handling you can check out in the documentation.)


UrlFetchApp is another powerful built-in tool in Apps Script.  This lets you handle the http/https requests and get the responses.  In this case to get at the xml that Gateway is handing back to us, we use the HTTPResponse method getContentText().  It’s not built into a full document model yet, so we use yet another built-in tool to create and parse the xml: XmlService.  Any of the response, document, entry, or properties variables in this code are based on parsing that xml.

The <entry> node holds a single data record of the response, which is why we base the outer for loop on the number of <entry> nodes.  On the first pass through the loop we also examine the properties of the data, so that we can write out a header line for the data.  After that, it’s just a matter of popping the data into cells of the spreadsheet.

That’s basically it!  We now have a small script that can pull out data from a Gateway service.  This is very very basic stuff – if you want to do anything interesting, you’ll obviously have a lot more work in front of you.  Nevertheless, I hope this is food for thought!

To report this post you need to login first.

16 Comments

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

  1. Ralf Handl

    Hi Paul,

    Cool stuff!

    Next level of convenience would be to use the Gateway Catalog service at <base url>/IWFND/CATALOGSERVICE;v=2/ to produce a list of available services, access the service document of the selected service to produce a list of available entity sets, then access the data in the selected entity set.

    Thanks for sharing this!

    –Ralf

    (0) 
    1. Paul J. Modderman Post author

      I was just thinking in my car on the way to work this morning about going the next step with this knowledge, and you’ve given me a great idea.  Thanks!

      (0) 
  2. Prabaharan Asokan

    Hi paul,

    Nice blog.Is it possible to display a document(pdf or .doc) present in SAP into a google doc.(with the help of netweaver gateway)

    Regards

    Prabaharan

    (0) 
      1. Paul J. Modderman Post author

        Your question is actually something I’m working on myself at the moment.  If/when I get it working I will make a separate blog post about it.

        Thanks,

        Paul

        (0) 
          1. Paul J. Modderman Post author

            I don’t have anything at the moment.  This is a pretty specific topic – I’d be surprised if any reference materials exist that can answer your exact question.

            (0) 
  3. Hemendra Sabharwal

    Thanks Paul for sharing the integration points in Google documents with NetWeaver Gateway. I will use this feature to one of my App.

    I followed your blog to populate data from SAP into spreadsheet, but it is giving me DNS error.

    However, when I tested ODATA url in gateway client, it is working fine.

    Please find the screenshot attached./wp-content/uploads/2014/05/error_450851.png

    Thanks again

    Regards

    Hemendra

    (0) 
    1. Paul J. Modderman Post author

      I am not sure if this is the issue you’re running into, but one thing to keep in mind: when the Google javascript is making the call to your SAP backend, it is not running on your local machine…it’s running on a Google server somewhere.  So the URL that you’re connecting to must be publicly reachable. 

      Authentication is an issue as well.  In my example I’ve just used the simple ‘basic’ authentication scheme…but I’m sure that no one would want to use that in a productive environment. 

      (0) 
      1. Guy de Bruyn

        When I read about the UrlFetch service I got excited about the possible SAP integration options. However, realizing that the calls are being executed from Google servers and hence the services have to be publicly reachable, the challenges are much bigger now if you want to make it secure. Is there an easy way to integrate with client-side JavaScript, and use for example Ajax to make calls from the browser (inside the company network) to other HTTP resources (SAP) within the company network? 

        (0) 
        1. Paul J. Modderman Post author

          I would point you toward the HtmlService API provided by Google.  It allows you to create a slice of a web-enabled interface (called a ‘Sidebar’ in many places) docked to one side of the Doc/Sheet that you’re working with.  Inside it you can do many of the HTML/CSS/JavaScript tricks you may be used to doing, including whatever ajax-y libraries you want to include. 

          To my understanding the sidebar stuff is all client-side.  My Chrome developer tools yell at me when I mess up my js syntax in the sidebar, so I think it’s running on my machine.  🙂

          (0) 
  4. Ferris Argyle

    Paul, was great meeting you at D-Code Vegas and seeing Cloud Shuttle.

    As an FYI for others

    • You don’t need to jump through the XmlService hoops. As Paul mentions in another post, ensure that SAP is returning JSON; then simply walk through the JSON object and push each result record to an array; you can then then setValues() of the sheet to the array of arrays. 
    • Paul, you’re absolutely right that HtmlService runs client-side; the new iframe sandboxing supports most client-side capabilities and 3rd party libraries.  However, building integrations client-side has security implications which need to be addressed, among other complicating factors.  OAuth is a well-accepted standard for authorizing distributed systems; mobile adoption and Fiori will increasingly drive SAP application availability outside the corporate firewall.

    Here’s the INT263 code lab we presented at D-Code showing Spreadsheets integration without XmlService; we’ve also augmented it to demonstrate updating SAP services from Google Forms.

    (0) 

Leave a Reply