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 Paul Hardy. 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. There are currently challenges with a bunch of OData v4 services from SAP and the Excel OData connector.
• 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.

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

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.

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