Overview and motivation
When SAP Process Automation became available some months ago I was very interested to try it out. With a recent engagement we worked on there was the need to repeatedly capture data from a website, decide if a change in date occurred since last time and to update an Excel sheet with formulas.
I decided to write this blog as I found some of the functions I used are not shown in that detail and I hope that it can save you hours of research work. As with most things in life: Once you know "how-to" all becomes "easy"!
Scenario architecture
The architecture is simple: SAP Process Automation (SPA) starts an unattended agent (through a job) at periodic times to do three things:
- Start web browser with a certain URL and read price data.
- Post the data to a web service on a web server that we own. That step is just for practice to explain you how to send a POST with SPA.
- Open an instance of MS Excel and a certain workbook file to:
- Check if price data has changed since the last run and if so,
- Copy the template sheet into a new sheet with today's date as the name
- Multiply price data with template data
- Save and close MS Excel
SAP Process Automation: Scenario Architecture
Let us now run through the scenario.
Scenario run
I've prepared a commented recording of the agent run for a better understanding before we look into the automation itself.
Automations explained
Finally I prepared a commented walk-through the automation in SAP Process Automation.
A few things I'd like to explain in detail. SPA allows you to get website data in a no-code manner. If you can apply this, retrieval is easy with the built in function of the automation editor. In my case the data would only show if I'd hovered over a menu with the mouse and would disappear right after I would have left the area.
In such cases - instead of capturing the website screen's element data, you can search with your browser's debugger if the data is available in a JavaScript variable or object. You can then do code injection in the following manner:
First, add one "Execute Script" activity to your automation flow. In the input parameters list add the injection statements into the expression field in brackets, for example:
["
var prices = [];
for (let name of __vieapps.prices.products) prices.push(
{\"name\" : name.EnglishTitle, \"Zone1Price\" : name.Zone1Price, \"Zone2Price\" : name.Zone2Price}
);
prices
"]Just think of this as what you would type in manually in the browser's debugger: Define an array variable then use the object of the website called _vieapps to give the price data and push the data line by line into an array of strings which we format like JSON. In the video you see the successive step which converts it then into JSON.
Next - how can web services be called? These are 2 steps: A script step to define the service call - that makes it very flexible to deal with all types of services including OAuth2 etc authentications. And then a "Call Webservice" activity to submit it. Please find a template for a POST below.
let priceDatax = [];
var wsCallData = {
resolveBodyOnly : true,
method: 'POST',
url: 'https://xxxxx.yyyy.beeceptor.com/my/api/pathx',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(priceData)
};
return wsCallData;Therefore wsCallData is the input for the "Call Webservice" step.
Closing
You have seen how to read website data from hidden elements through code-injection. Keep this approach in mind for cases where the data is not visible and only the JavaScript object can reveal the data you need.
You also saw how to submit data to a webservice and finally more or less complex manipulations of MS Excel workbook data.
What scenario would you like to have covered? Let me know in the comment section!
Credits:
Credits go to baptiste_sa_sap without whom I might still be figuring out how to retrieve the price data and who showed me the hint of code injection into SAP Process automation. 🙏
- SAP Managed Tags:
2 Comments
