How to use IBM App Connect with Microsoft Excel Online

Supported product and API versions

To find out which product and API versions this connector supports, see Detailed System Requirements on the IBM Support page.

Connecting to Microsoft Excel Online

Complete the connection fields that you see in the App Connect Designer Catalog page or flow editor. If necessary, work with your Microsoft Excel Online administrator to obtain these values.

To generate these values and connect to Microsoft Excel Online, you need to register an application with the required permissions in Microsoft Azure, which will enable App Connect to integrate with Microsoft Excel Online by using APIs and protocols.

1. To register an application with Microsoft Azure, for use with App Connect:
   1. Log in to the Microsoft Azure portal, and then locate and click App registrations.
   2. If you have access to more than one tenant, switch to the tenant where you want to register the app by using the Directories + subscriptions filter in the banner and then click the Close icon (X) to return to the previous page.
   3. In the App registrations page, click New registration.
   4. In the Register an application page, specify a unique name for your app, select Accounts in any organizational directory (Any Azure AD directory - Multitenant) as the account type, and accept the default values for the remaining fields.
      
   5. Click Register. The Overview page for the application is displayed.
      
   6. Make a note of the Application (client) ID value because you need to specify it as a connection value when creating the account (by using either OAUTH2_PASSWORD or BASIC_OAUTH authorization) in App Connect.
2. To generate a client secret for your registered application:
   1. Next to Client credentials on the Overview page, click Add a certificate or secret. This displays the Certificates & secrets page.
   2. Click New client secret
   3. In the Add a client secret panel, specify a description for the secret (for example, App Connect secret) and then select an expiry period.
   4. Click Add. The generated client secret is displayed on the Certificates & secrets page.
   5. 
   6. Copy and store the client secret value because you need to specify it as a connection value when creating the account (by using either OAUTH2_PASSWORD or BASIC_OAUTH authorization) in App Connect. If you are using BASIC_OAUTH authorization, you'll also need to specify the client secret value while generating access and refresh tokens.
      Note: The client secret value won't be shown again in full after you leave this page.
3. Configure the permissions that App Connect needs.
   1. In the left pane, click API permissions and then click Add a permission > Microsoft Graph > Delegated permissions to add each of the following permissions in turn. You can search for and select a permission, and then click Add permissions.

      Permissions	Description
      Directory.ReadWrite.All	Read and write directory data. (Allows App Connect to read and write data in your organization's directory, such as users, and groups.)
      offline_access	Maintain access to data you have given it access to. (Allows App Connect to see and update the data you gave it access to, even when users are not currently using App Connect.)

      Note: Directory.ReadWrite.All requires admin consent.
   2. If the status of any permission is shown as Not granted for myDomain, click Grant admin consent for myDomain, where myDomain is your domain name. Then click Yes to confirm. (This updates the status of all permissions to Granted for myDomain.)
   
4. If you want to connect by using BASIC_OAUTH authorization, use an application such as IBM API Connect Test and Monitor or Postman to submit a POST request to generate an access token and a refresh token that will be used to interact with Microsoft Excel Online on your behalf. Specify the following parameters:
   • Request URL:

     https://login.microsoftonline.com/organizations/oauth2/v2.0/token

   • Content-Type: application/x-www-form-urlencoded
   • Request parameters:

     Key	Value
     client_id	Set this to the Application (client) ID value that was generated for your registered app.
     scope	Directory.ReadWrite.All offline_access
     grant_type	password
     client_secret	Set this to the client secret value that was generated under Certificates & secrets for your registered app.
     userName	Set this to the username that was used to log in to the Azure portal.
     password	Set this to the associated password for the username

• Example: Using Postman

  Postman Version 7.29.1 was used in these instructions, so there might be a slight variation in the fields that you see if your version is different.

  1. Start a new POST request and specify the request URL.
  2. Click the Body tab and select x-www-form-urlencoded. This option automatically adds the Content-Type: application/x-www-form-urlencoded setting in the request header.
  3. Specify the request parameters.

  When you click Send, an access token and refresh token are returned in the response. Make a note of these values because you need to specify them as connection values when creating the account.

To connect to a Microsoft Excel Online endpoint from the App Connect Designer Catalog page for the first time, expand Microsoft Excel Online, then click Connect.

General considerations

Before you use App Connect Designer with Microsoft Excel Online, take note of the following considerations:

• (General consideration) You can see lists of the trigger events and actions that are available on the Catalog page of the App Connect Designer.

  For some applications, the events and actions in the catalog depend on the environment and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

• (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

Microsoft Excel Online events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Rows

New row appended

Table rows

New table row appended

Microsoft Excel Online actions

Your flow completes these actions on this application.

Columns

Retrieve column data

Ranges

Retrieve cell ranges

Rows

Insert row

Retrieve rows

Delete row

Update row

Append row

Table rows

Append table rows

Retrieve table rows

Delete table row

Update table row

Tables

Create table

Retrieve tables

Delete table

Update table

Workbooks

Create workbook

Retrieve workbooks

Delete workbook

Rename workbook

Download workbook

Upload workbook

Worksheets

Create worksheet

Retrieve worksheets

Delete worksheet

Rename worksheet

More items are available when you have connected App Connect to Microsoft Excel Online.