Skip to Content
Author's profile photo Paul J. Modderman

Tips for Creating a Google Apps Script/SAP Gateway Mashup

Having spent the last few month with my head way inside the process to integrate Google docs with an SAP backend through NetWeaver Gateway, I humbly offer a few tips to the blogosphere for anyone wanting to do the same thing. 

SAP Gateway: Use JSON Whenever Possible

This little phrase is your best friend for almost any interaction with SAP Gateway: $format=json.  This will instruct Gateway to send the result of the URI to your application in json format, making it lighter-weight and easily supported by Apps Script. 

But!  You have to know which services will support a json format.  If you decide that your app needs to understand the $metadata endpoint of a particular service, you can’t perform a $format=json: there are SAP annotations that require XML to see.  So your Apps Script code may have to deal with parsing both json and XML.  

Luckily, Apps Script has utilities that can handle either case.  To work with json, become familiar with JSON.parse() and JSON.stringify(), highlighted in the Apps Script documentation.  The same with XML – go through the tutorial on using the XML parsing utilities.  If you’re smart with how you handle XML, it doesn’t have to be too much more complicated than json. 

Google Apps Script: User Interface

If you decide to present a user interface on top of a Google document in your application, you have a couple options.  If the interaction paradigm calls for a user to make a set of decisions before they can interact with the actual editor, it is probably wise to use the UI Service.  If the interaction calls for the user to have some control over continuing use of the editor, think of using the sidebar HTML paradigm

The way I think about it is this: if your user interface has an interaction that is essentially saying “do something about this before you can do anything else”, then choose the UI Service.  If your interaction is saying “here are some things you can do with what you’re already doing”, consider the HTML service.

Whichever paradigm you use, you should consider what you’ll need to know.  Both the UI Service and the HTML Service depend on some knowledge of web applications; the UI service is structured and formalized with a well-documented API, while the HTML service is very flexible in what you can present and can include outside scripting libraries.  Both provide ways of handling client as well as server-side actions. 

And if you’re using the HTML service, get to know jQuery.  I mean it.  Just go learn it already.

Google Apps Script: Be Thoughtful Using Services

There are a host of options for using Google APIs for common tasks: parsing XML, storing key-value pairs in your documents, calling external web APIs, and many others.  They make your life easier.  Know them.

But you should also know that in some cases, using them too frequently can cause issues in your add-on.  I stumbled across that myself when I started receiving an error: “Uncaught ScriptError: Service invoked too many times in a short time: properties rateMax. Try Utilities.sleep(1000) between calls.” It took me longer than I’d like to admit to figure out that I was calling the PropertiesService too frequently. 

I wasn’t able to find an official document telling me a rate limit for calling Google APIs from within an Apps Script project, so I just went through and really optimized how I was using the PropertiesService. The biggest thing I did was to put my settings into an ordered JSON object, stringify it, and store the entire object as one property. That way, when I would need several things from the properties service I would only have to read it once and then parse out the JSON into the pieces I needed. 

Here’s an example of what I mean. I put a number of properties on a single javascript object, and store that single object as a property of the document in my application.

function exampleStoreSettings(){
  //Store user settings at some point in your application
  //First, a generic object
  var userSettings = {};
  //A couple of settings. Can really be anything you want, JSON is flexible
  userSettings.userName = 'Bob';
  userSettings.emailAddress = ''; 
  userSettings.currentAction = 'READ';
  userSettings.allowedActions = 'READ,UPDATE';
  //Store the object to the properties service
   .setProperty('USER_SETTINGS', JSON.stringify(userSettings));
function exampleReadSettings(){
  //Read those user settings at a different point in your application
  //Pull the JSON from the properties service
  var userSettings = JSON.parse(PropertiesService
  //Do whatever you want with the individual settings
  var userName = userSettings.userName;
  var emailAddress = userSettings.emailAddress;
  var currentAction = userSettings.currentAction;
  var allowedActions = userSettings.allowedActions;

That’s all for now.  Hit me up in the comments or private messages for any questions!

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.