How to integrate Fusion Cloud REST APIs into Excel using VBCS

In earlier posts, we have seen how the REST APIs in Fusion Cloud work and how we can leverage them for various use cases. Normally we invoke REST APIs either via integration layers like Oracle Integration Cloud (OIC) or some other cloud platforms like Azure, APEX etc.

But, what if there's a requirement to fetch the Fusion Cloud data into an Excel sheet ? Well, technically in some cases we can use ADFDI to achieve some part of this but it's not a desired option as it also opens options like inserting or updating new data into Fusion Clous. What if the requirement is only to provide an option to the users where they can refresh/fetch the latest Fusion data into Excel sheet with just one click ? Well, we can achieve this by using Oracle Visual Builder Excel add-in. Let's see how we to do this.

- Download Oracle Visual Builder Add-in for Excel from here

https://www.oracle.com/downloads/cloud/visual-builder-addin-downloads.html

- Install the Add-in on your machine

- Once installed, you will be able to see the Add-in in Excel options as shown below. Make sure it's enabled.

- Open a new workbook in Excel

- Now we will see a new option named 'Oracle Visual Builder' in Excel menu

- Let's click on Designer option

- This will bring up a pop-up asking the Web Address of the Fusion REST API

- We have to enter the Web Address as Instance Link/ followed by REST API endpoint followed by 'describe'

- For example, let's assume we want to invoke Get All Suppliers REST API to fetch all suppliers from Fusion Cloud.

Get All Suppliers API:
Instance_URL/fscmRestApi/resources/11.13.18.05/suppliers

- Now, the Add-in will try to connect to your Fusion Instance and you will be asked to provide Login credentials for a valid Fusion user.

- After successful login, you will be shown all the business objects provided by the REST API

For example, for our Supplier REST API, we will see Business Objects pertaining to Suppliers, Business Classifications, Contacts, Sites etc.

- Select the business object you would like to populate in the Excel and click Next. Let's select Suppliers for our use case

- Select Table Layout on the next screen and click Next

- Now, we will be asked to select any Optional business objects on next screen. Let's not select any in our use case and click Next.

- Click Finish

- Once done, you will see a new Sheet named Suppliers has been created and you will also see Layout Designer section on the right side

- To avoid performance issues, Oracle restricts the download of bulk data. So we have to mention at least some search criteria. This is very much like the Standard Suppliers page/form.

Under Query, we must enter at least one search criteria. Once done, click on the Download Data option under the Add-in menu.

let's add a search criteria for Supplier name containing 'xyz'

-  Click on Download Data button

- Once download is completed, you will see the Status details in a new section on the right side and actual data on left side. In our case, it extracted 5 Suppliers from the REST API.

- As we can see, by default, it shows all the fields provided by REST API under business object. So let's remove additional fields and keep only the ones we need.

To do so, let's navigate to Columns section in Layout Designer, select the columns we want to remove and click Delete button.

- Now, let's click on Download Data option again and this time it will download all the eligible Suppliers based on our search criteria and will generate the sheet with only our selected columns.

So, this way we can leverage Visual Builder Excel Add-in to provide flexibility to users by creating some excel sheets to show certain information from Fusion Cloud. This can also be used to create or update information in Fusion Cloud, depending on which REST API we use and base on the user account selected to invoke the REST API.