SAP BTP ABAP Environment integration journey with Microsoft – Part 1 – using Excel

[Martin-Pankraz]

👉🏿back to blog series or jump to GitHub repos🧑🏽‍💻

>> part2

Hello and welcome to your ABAP Cloud with Microsoft integration journey. Part 0 of this series got you covered with the prerequisites to get your dev environment on Azure up and running.

Today we will be generating an OData service for a Travel App hosted on BTP ABAP environment and integrate that with Microsoft Excel with “live feeds” that can be refreshed from the client.

Oh my god, not another post about Excel 😱sorry bfeeb8ed7fa64a7d95efc21f74a8c135. I couldn’t help it. See Paul’s monthly series for even more fun with Excel.

What can I say, Excel is a popular consumer. So here we go…

Create the OData service on your BTP ABAP environment

For this sample I ran through the SAP developer tutorial “Expose a Standard Core Data Service for ABAP Environment”, which creates a Travel App with Passengers, Airports, Countries, and Travel Agencies. During configuration pay special attention to:

• Step 6 “Create a service binding”: Choose Binding Type OData V2. (By the way: OData v4 with Excel OData connectoris functional since end of 2023 too).


• Step 10 “Add inbound into communication scenario”: Choose Basic Auth for development and certificate-based for production as per SAP’s best-practices for BTP.

🛈Note: Currently SAP supports OAuth2 only on SAP S/4HANA Cloud – ABAP Environment (aka. Embedded Steampunk). See my docs entry to learn about the configuration of SAP Principal Propagation with OAuth2SAMLBearer for Excel (Power Query).

See the available OAuth2 flows requiring human action during the request using service keys of your BTP ABAP Environment in Thomas's insightful blog post. They are a good fit for testing. For production and service to service communication X.509 would be recommended till OIDC is fully released. See below screenshot for reference.

Have a look at part 2 of the series to see X.509 in action.

You may extrapolate from the guidance given in this blog post to SAP API Management on Azure where needed.

Integration test with your favorite REST client

Copy the calculated OData Service URL from your communication scenario and provide the Communication-User credentials to your REST client to verify proper setup.

With that you are equipped to blow some steam like a punk 🤘See below a request sample using Postman. Visual Studio Code REST client might be a nice alternative.

Find the Postman collection on the associated GitHub repos ready for import.

Excel setup, data pull, and refresh

Since we have verified a fully functional OData v2 service, let’s open our Excel Desktop client and PULL SOME DATA via the ABAP environment (imagine Arnold Schwarzenegger’s booming voice 🦾 here).

Choose the entities you would like to load. You may add additional logic using the button “Transform Data”, which leads you to the Power Query Editor.

Till now, the OData create/change operations are still disabled for this Travel App example. You may manually alter one of the entries on the source via the ABAP Development Tools (SQL console). Have a look at the ABAP class “zcl_generate_travel_data_” for instance.

For an integrated solution, follow SAP’s second Travel App tutorial “Create Behavior Definition for Managed Scenario” to activate update functionality. I will wait while you do that…⌛💤

All set? Onwards to the OData update specifics 😊

OData has concurrency control and cross-site scripting guard rails

See details regarding the CSRF token and OData ETag handling in step 2 of the screenshot. It shows a PATCH operation, which updates only the specified fields.

Enjoy the updated data using the refresh button on Excel 😎

Explore write back scenarios beyond manual requests with REST clients

So far, we have been using methods to pull from Excel out of the box and update data outside of Excel. There are multiple options to execute the Steampunk (or SAP Integration Suite fronting steampunk) request directly from within “Excel Desktop” and “Excel Online”:

• VBA editor with https request methods to call the BTP ABAP environment (SAP API Management etc.)


• Excel Add-Ins or Microsoft Office developer tools (like SAP’s Analytics Cloud plugin for Excel)


• Power Automate (trigger from selected row in Excel Online only)

Let’s explore the last option in more detail. We will be calling the BTP ABAP environment from a workflow using the http connector like below:

The Excel Online (Business) Connector offers the required trigger to act upon a selected row on your Excel table.

🛈 Note: That is not yet available for Excel Desktop. Use a different trigger like http request or timer in such cases.

🛈 Find a snippet for the SAP cookie setting for the PATCH here.

Using the given identifier passed by the trigger (TravelUUID), retrieve the entire table row, and prepare the message for the ABAP environment hosted OData service. I chose a PATCH operation for efficient update.

Use API Management integration with Power Automate to further simplify the developers experience by lifting the burden of CSRF tokens and apply smart token caching.

The approach listed on the linked article is applicable to SAP API Management the same way.

 

Once configuration of the flow is finished, hit the play button…🥁drum roll🥁

…and marvel at the Teams message informing you about the successful OData driven update via the BTP ABAP environment 🤩

What else?

Up-level your Teams integration with adaptive cards if you feel like it. The first course of action would be adding a deep link back to the Excel sheet as a button for instance. Curious to hear your ideas. Let me know in the comments 😊

If you favor ODBC (SQL interface) over OData have a look here. You may apply all the steps taken in this blog also for a SQL integration except for write back.

Final Words

That’s a wrap 🌯you saw today how you can utilize the standard SAP developer tutorials for CDS and RAP to integrate a BTP ABAP environment hosted OData service into Microsoft Excel. We explored one approach to write back values into SAP in detail.

See part 2 of the series on how to configure enterprise grade authentication with X.509 certificates for this scenario.

Find all the resources to replicate this setup on this GitHub repos. Stay tuned for the remaining parts of the steampunk series with Microsoft Integration Scenarios from my overview post.

 

Cheers

Martin