How to use IBM App Connect with Microsoft Excel Online

Microsoft Excel Online is a spreadsheet software used to create, edit, and share Excel spreadsheets. Microsoft Office Online combines the most common Office features with real-time co-authoring capabilities that enable users to collaborate on shared documents, presentations, and spreadsheets.

Availability:
  • A connector in IBM App Connect Enterprise as a ServiceApp Connect Enterprise as a Service connector
  • A local connector in a Designer instance of IBM App Connect in containers (Continuous Delivery release)Local connector in containers (Continuous Delivery release) 12.0.5.0-r3 or later

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 connect App Connect to a Microsoft Excel Online account, select your preferred authorization method from the following table:
Table 1. Authorization methods and their applicability
Authorization method Applicability Authorization Scope (Permissions)
Provide credentials for App Connect to use (BASIC OAUTH) App Connect in containers Files.ReadWrite.All, offline_access, User.Read, and Sites.ReadWrite.All
Provide a username, password, and client credentials (OAUTH 2.0 PASSWORD) , App Connect in containers, and App Connect Enterprise as a Service Files.ReadWrite.All, offline_access, User.Read, and Sites.ReadWrite.All
Then, specify values in the connection fields for your chosen authorization method:
Table 2. Connection fields for your chosen authorization method. Descriptions of the connection fields are given after this table.
Provide credentials for App Connect to use (BASIC OAUTH) Provide a username, password, and client credentials (OAUTH 2.0 PASSWORD)
Client ID Username
Client secret Password
Access token Client ID
Refresh token Client secret
Table 3. Description of the connection fields
Connection field Description
Client ID The unique identifier generated after the Microsoft Azure app registration gets mapped to the specific project requests
Client secret The application client secret for the project-specific client ID
Access token The access token generated from the application client ID and client secret
Refresh token The refresh token generated from the application client ID and client secret
Username The username to log in to your Microsoft Excel Online account
Password The password for the specified username

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.

To connect by using either OAUTH2_PASSWORD or BASIC_OAUTH authorization, you'll need to obtain a client ID and a client secret for your registered app, and then configure permissions. If using BASIC_OAUTH authorization, you must also obtain an access token and a refresh token.
Note: These instructions assume that you are registering an application in Microsoft Azure for the first time.
  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.
      Figure 1. Microsoft Azure registering an application window
    5. Click Register. The Overview page for the application is displayed.
      Overview page for the registered application
    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.
      Generated client secret for the registered app
    5. In the Add a client secret panel, specify a description for the secret, then select an Expiry periodand click Add
    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.)
    Required permissions for the registered app
  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
  • 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.
    Configuring the request parameters in Postman

    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.

    Generated tokens in the response returned for the POST call in Postman

Note: The generated access token is valid for 1hour, and the refresh token will expire after 90 days of inactivity. So it is expected that you need to generate new tokens only if the refresh token has been revoked or has not been used in 90 days.
Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Catalog page, select the account, open its options menu (⋮), then click Rename Account.

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 (IBM® Cloud Pak for Integration or App Connect on IBM Cloud) 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

Restriction: The following are the restrictions in using the events for Microsoft Excel Online:
  • The event is emitted only if the row is appended to the existing list.
  • If you try to update an existing list, then no events are emitted.
  • If you delete some rows or table rows, then no events are emitted.
  • There won’t be any events that are emitted for a blank row. This case can occur if you append a new row by skipping some rows from the existing list.
  • If the event is started on an MS Excel worksheet that already has records, then the event won’t be triggered for existing rows. It gets triggered only for the new rows that are appended when the event flow is started.
  • You will not have control over polling configurations (for example, interval, size, and so on) as there are no controls available in the Microsoft Excel Online connector to configure these settings. The polling interval is set to run every minute in the background.
  • An event does not get updated if a poll has already started. As an example, consider a scenario where you are adding data to columns in Microsoft Excel Online, and the poll starts while you completed only half the required data (say, 3 out of 5 columns). The returned data reflects only what was filled (that is, 3 columns in the response). Even at a later stage, if the other rows are updated, the event will not get updated.

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

Rows
New row appended
Table rows
New table row appended

Microsoft Excel Online actions

These are actions on this application that you want a flow to complete.

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.

Examples

Dashboard tile for a template that uses Microsoft excel online

Use templates to quickly create flows for Microsoft Excel Online

Learn how to use App Connect templates to quickly create flows that perform actions on Microsoft Excel Online. For example, open the Templates gallery, then search for Microsoft Excel Online.

Microsoft excel online flow in detailed view

Use IBM App Connect to build flows that integrate with Microsoft Excel Online.

Read the blog in the IBM Community to learn how to retrieve and update Microsoft Excel Online worksheet rows with the status email sent through Gmail. Click Learn more to read the blog.