How to update SAP Ariba Source from Excel

The CData Excel Add-In for SAP Ariba Source enables you to edit and save SAP Ariba Source data directly from Excel. This article explains how to transfer data from Excel to SAP Ariba Source. This technique is useful if you want to work on SAP Ariba Source data in Excel and update changes, or if you have a whole spreadsheet you want to import into SAP Ariba Source. In this example, you will use the Vendors table; however, the same process will work for any table that can be retrieved by the CData Excel Add-In.

Establish a Connection

If you have not already done so, create a new SAP Ariba Source connection by clicking From SAP Ariba Source on the ribbon.

In order to connect with SAP Ariba Source, set the following:

• API: Specify which API you would like the provider to retrieve SAP Ariba data from. Select the Supplier, Sourcing Project Management, or Contract API based on your business role (possible values are SupplierDataAPIWithPaginationV4, SourcingProjectManagementAPIV2, or ContractAPIV1).
• DataCenter: The data center where your account's data is hosted.
• Realm: The name of the site you want to access.
• Environment: Indicate whether you are connecting to a test or production environment (possible values are TEST or PRODUCTION).

If you are connecting to the Supplier Data API or the Contract API, additionally set the following:

• User: Id of the user on whose behalf API calls are invoked.
• PasswordAdapter: The password associated with the authenticating User.

If you're connecting to the Supplier API, set ProjectId to the Id of the sourcing project you want to retrieve data from.

Authenticating with OAuth

After setting connection properties, you need to configure OAuth connectivity to authenticate.

• Set AuthScheme to OAuthClient.
• Register an application with the service to obtain the APIKey, OAuthClientId and OAuthClientSecret.

  For more information on creating an OAuth application, refer to the Help documentation.

Automatic OAuth

After setting the following, you are ready to connect:

APIKey: The Application key in your app settings. OAuthClientId: The OAuth Client Id in your app settings. OAuthClientSecret: The OAuth Secret in your app settings.

When you connect, the provider automatically completes the OAuth process:

1. The provider obtains an access token from SAP Ariba and uses it to request data.
2. The provider refreshes the access token automatically when it expires.
3. The OAuth values are saved in memory relative to the location specified in OAuthSettingsLocation.

Retrieve Data from SAP Ariba Source

To insert data into SAP Ariba Source, you will first need to retrieve data from the SAP Ariba Source table you want to add to. This links the Excel spreadsheet to the SAP Ariba Source table selected: After you retrieve data, any changes you make to the data are highlighted in red.

1. Click the From SAP Ariba Source button on the CData ribbon. The Data Selection wizard is displayed.
2. In the Table or View menu, select the Vendors table.
3. In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
4. In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.

Insert Rows to SAP Ariba Source

After retrieving data, you can add data from an existing spreadsheet in Excel.

1. In a cell after the last row, enter a formula referencing the corresponding cell from the other spreadsheet; for example, . 👁 A local copy of a table. One row will be inserted. (QuickBooks is shown.)
   
2. After using a formula to reference the cells you want to add to SAP Ariba Source, select the cells that you are inserting data into and drag the formula down as far as needed. The referenced values you want to add will be displayed on the Vendors sheet. 👁 The range of changes to update. (QuickBooks is shown.)
   
3. Highlight the rows you want to insert and click the Update Rows button.

As each row is inserted, the Id value will appear in the Id column and the row's text will change to black, indicating that the record has been inserted.