Using Microsoft Excel for doing queries in SAP SuccessFactors OData API
Hello SAP community,
This blog post is similar with this one, but here we will see how to achieve the solution specifically for the SAP SuccessFactors OData API.
All the images and data used here was taken from SalesDemo instance, so we are using dummy data.
With these steps you will be able to extract data from SAP SuccessFactors OData API doing queries in Microsoft Excel, fetching the data quickly to work directly in your spreadsheet.
This sample was built with the Microsoft Excel for Office 365 version 1908.
1. Open Excel, go to Data tab, click in From Web
2. We are going to use one SAP SuccessFactors OData query ready for one SalesDemo instance in the DC4.
This query is returning the results in the json format due the parameter &$format=json
- Query = https://apisalesdemo4.successfactors.com:443/odata/v2/EmpJob?$top=10&$select=userId,company,costCenter&$format=json
If you are not familiar building OData queries, one good way is using the SAP SuccessFactors Integration center. More details in the KBA below (follow the steps 1 to 7):
2890064 – Using the integration center tool to export the OData query and helping with your OData upserts – SAP SuccessFactors HXM Suite
Another point that you need to be aware is regarding the datacenter where your SF company is located. You can find the URLs for each datacenter in the KBA:
2215682 – SAP Successfactors API URLs for different Data Centers
3. Select the Advanced, type your OData query in URL parts field, select the HTTP request header Authorization, type Basic and your credentials. The timeout parameter is optional.
The Authorization Basic value FNGUEFSVDAz…. in the example above is the credentials base64 encoded in the following format:
To see more about this process above you can check the OData developer handbook chapter 3.1.
If you are not familiar creating SAP SuccessFactors OData API username, you can follow also the instructions of this guided answers here.
Completing the steps, press OK button. In the next screen, press connect.
4. Double click in Record
5. Double click in List
6. Click In To Table
7. In the popup, click in OK
8. This is the output (Column1), expand clicking in the button indicated:
9. In the selection, remove the _metadata and press OK
10. This is the preview. If everything is OK, click in the button Close & Load
11. This will be the final result in your spreadsheet.
The Excel will now have the data loaded in the columns/lines/table view.
The work is done 🙂
Now you are able to fetch data directly from your SuccessFactors instance to your Microsoft Excel spreadsheet using OData API.
If you would like to try some advanced steps too, you can also use the following option: Advanced Editor and manually change the Query there.