Setting up the Business Central add-in for Excel in Business Central on-premises

Note

Azure Active Directory is now Microsoft Entra ID. Learn more

This article provides guidance for how to configure Business Central on-premises so that users can edit data in Excel by using the Business Central add-in for Excel.

Note

This article does not apply to the older Excel add-in that was available for installation with the Dynamics NAV Client connected to Business Central client by using the Business Central Setup in versions older than 2019 release wave 2.

Overview

You can set up the Business Central deployment to support an add-in that enables users in the Business Central client to work with data from list pages in Excel. Users can get fresh data from Business Central into Excel and push updated data from Excel to Business Central.

Without this add-in, users can open a list page in Excel from the Open in Excel action on the page. But the Open in Excel action doesn't allow them to push changed data back to Business Central. When this add-in is set up, the Edit in Excel action is added. The add-in is based on the Microsoft Dynamics Office Add-In available from the Office Store.

Learn more about how users work with Excel from Business Central at Viewing and Editing in Excel From Business Central.

Note

The Excel add-in is not available in the mobile apps.

Prepare Business Central

Your on-premises deployment must meet the following prerequisites:

• Microsoft Entra ID used to authenticate users.

  The Business Central Server instance, clients, and users must be configured for Microsoft Entra authentication, including OData.

  • For Business Central 2022 release wave 1 (v20) and later, go to Configure Microsoft Entra authentication with OpenID Connect.

    Important

    Be sure to set WSFederationLoginEndpoint parameter of Business Central Server instance. Otherwise, you'll get an error that the realm is not defined when trying to edit data using Edit in Excel.

  • For Business Central 2021 release wave 2 (v19) and earlier, go to Configure Microsoft Entra authentication with WS-Federation.

• OData enabled and uses Secure Sockets Layer (SSL) for authentication.

  Learn more in Using Security Certificates with Business Central On-Premises.

• Business Central Web Server configured to use SSL (https).

  Learn more in Configure SSL to Secure the Connection to Web Client.

• If your deployment is multitenant, Business Central Web client must accept host names for tenants.

  Learn more in Configure the Web Server to Accept Host Names for Tenants.

• Business Central client computers have a supported version of Excel.

  Learn more in System Requirements.

Expose the Business Central application Web API in Microsoft Entra ID

When Business Central is configured for Microsoft Entra authentication, the Business Central application is registered as an application in a Microsoft Entra ID. Before the Excel add-in can be configured, you must configure the Business Central application in Microsoft Entra ID to expose its Web API.

Note

The API may have already been exposed as part of the Microsoft Entra authentication setup. You can also use the following steps to verify.

1. Sign in to the Azure portal.

2. Search for and select Microsoft Entra ID.

3. Under Manage, select App registrations, and then select the app registration for Business Central authentication.

4. Under Manage, select Expose API.

5. On the Expose API page, if the Application ID URI box is filled out, then API is already exposed, so you don't have to do anything else. Otherwise, select Set to expose the API.

6. Under Manage, select Token configuration, and then do the following steps:

   1. On the Optional claims page, select Add optional claim.

   2. In the Token type pane, choose Access.

   3. Under Claim, select email and upn, and then select Add.

      If a dialog appears with the checkbox Turn on the Microsoft Graph email, profile permissions (required for claims to appear in token), select the checkbox, and then select Add.

7. After you perform the previous steps, especially step 6, the application might issue a token to the Excel add-in. It's recommended to clear any caches to ensure that you get the upn and email claims. To clear the cache of the Excel add-in, do the following steps:

   1. In Excel, right-click the Excel add-in pane, and then select Inspect to open the browser developer tools.
   2. Go to the Application tab.
   3. In the Storage pane, clear instances of https://az689774.vo.mssecnd.net under Local Storage, Session Storage, Cookies, and Shared Storage. To clear, right-click an instance and select Clear.
   4. Refresh the add-in by closing and opening the Excel file again or pressing Ctrl+R while being inside the developer tools.
   5. If the Excel add-in prompts you to sign in again, then you'll receive a token with the new claims the next time you sign in.

Learn more about how to expose the Web API at Quickstart: Configure an application to expose web APIs.

Register and configure an application in Microsoft Azure

When Microsoft Entra authentication was set up for your Business Central deployment, a Microsoft Entra tenant was created in Microsoft Azure, and an application for Business Central was registered in the tenant. The Excel add-in requires that you add (register) a separate Microsoft Entra application in the Microsoft Entra tenant. Learn more guidelines at Register your application with your Microsoft Entra tenant.

1. Sign in to the Azure portal.

2. Search for and select Microsoft Entra ID.

3. Under Manage, select App registrations > New registration.

4. On the Register an application page, fill in the following information, and then select Register:

   Setting	Description
   Name	The name of your application as it will display to your users, such as Excel Add-in for Business Central.
   Supported account types	Specifies which accounts that you would like your application to support. For purposes of this article, select Accounts in this organizational directory only.

5. Modify the app's manifest to configure OAuth2 implicit grant flow and a spa type reply URL for the Excel add-in.

   From the application's Overview, select Manifest. You can choose to edit the manifest using the Microsoft Graph App Manifest (New) tab or the AAD Graph App Manifest (Deprecating Soon) tab. We recommend the Microsoft Graph App Manifest (New) tab.

   Microsoft Graph App Manifest (New)

   1. In the editor, set "web.implicitGrantSettings.enableIdTokenIssuance" and "web.implicitGrantSettings.enableAccessTokenIssuance" keys to true:

      "enableAccessTokenIssuance": true,
      "enableIdTokenIssuance": true
      

   2. In the "spa.redirectUris":[] key, add the following lines:

      "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/DynamicsApp.html",
      "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/Authenticated.html",
      "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/AuthenticationDialog.html",
      "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/SignOutDialog.html"
      

      Remember to add a comma before or after this entry, depending on where you add it in the list.

   3. Select Save.

   AAD Graph App Manifest (Deprecating Soon)

   1. In the editor, set "oauth2AllowIdTokenImplicitFlow" and "oauth2AllowImplicitFlow" keys to true:

      "oauth2AllowIdTokenImplicitFlow": true,
      "oauth2AllowImplicitFlow": true,
      

   2. In the "replyUrlsWithType":[] key, add the following lines:

      {
          "url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/DynamicsApp.html",
          "type": "Spa"
      },
      {
          "url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/Authenticated.html",
          "type": "Spa"
      },
      {
          "url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/AuthenticationDialog.html",
          "type": "Spa"
      },
      {
          "url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/App/SignOutDialog.html",
          "type": "Spa"
      }
      

      Remember to add a comma before or after this entry, depending on where you add it in the list.

   3. Select Save.

      ---

6. Grant the Excel add-in application permission to access the Business Central application Web API.

   Give the Microsoft Entra application for the Excel add-in delegated permission to access the Business Central application Web API, which you exposed earlier in this article. This permission allows users of the Excel add-in to access the OData web services to read and write data.

   1. From the application's Overview page, select API Permissions.
   2. Select the Add a permission
   3. On the APIs my organization uses, select the Business Central application.
   4. Select Delegated permission.
   5. Search for Business Central API by using registered application's name or application ID.
   6. Select the permission from the list, then select Add Permission.

   Learn more at Quickstart: Configure a client application to access web APIs.

7. On the application's Overview page, copy the Application (Client) ID that is assigned to Excel add-in application.

   You need the application ID to configure the Business Central Server instance.

You've completed the work you have to do in the Azure portal. The next configuration is done on the Business Central Server instances.

Configure the Business Central Server instance

You add the Excel add-in to the Business Central Server instances in your deployment. You can use either the Business Central Server Administration tool or Set-NAVServerConfiguration cmdlet in the Business Central Administration Shell.

1. In the Business Central Server Administration tool, in the Microsoft Entra ID section, set the Excel add-in Microsoft Entra ID client ID field to the application (client) ID for the Excel add-in application that you copied from the Azure portal.

   If you're using the Set-NAVServerConfiguration cmdlet, set the ExcelAddInAzureActiveDirectoryClientId key.

   Set-NAVServerConfiguration -ServerInstance <Business Central server instance> -KeyName ExcelAddInAzureActiveDirectoryClientId -KeyValue <application ID>
   

   Note

   Make sure the Microsoft Entra app ID URI is set to the App ID URI of the registered app for Business Central in the Microsoft Entra tenant.

2. In the Client Services section, set the Web Client Base URL field to the base URL of the Business Central Web client.

   This value is the root portion of all URLs that are used to access pages in the web client. The value must have the format https://[hostname:port]/[instance], such as https://MyBCWebServer/BC or https://cronusinternationltd.onmicrosoft.com/BC240.

   If you're using the Set-NAVServerConfiguration cmdlet, set the PublicWebBaseUrl key.

   Set-NAVServerConfiguration -ServerInstance <Business Central server instance> -KeyName PublicWebBaseUrl -KeyValue <web client URL>
   

3. In the OData Services section, configure or verify the following settings:

   1. Set the OData Base URL field to the public URL for accessing OData services.

      The URL must have the following format https://<hostname>:<port>/<instance>/ODataV4/, such as https://localhost:7048/BC240/ODataV4/.

      With the Set-NAVServerConfiguration cmdlet, set the PublicODataBaseUrl key.

      Set-NAVServerConfiguration -ServerInstance <Business Central server instance> -KeyName PublicODataBaseUrl -KeyValue <OData URL>
      

   2. Select the Enable OData Services check box.

      If you're using the Set-NAVServerConfiguration cmdlet, set the ODataServicesEnabled key to true.

      Set-Navserverconfiguration -ServerInstance <Business Central server instance> -KeyName ODataServicesEnabled -KeyValue true
      

   3. Select the Enable API Services check box.

      If you're using the Set-NAVServerConfiguration cmdlet, set the ApiServicesEnabled key to true.

      Set-NAVServerConfiguration -ServerInstance <Business Central server instance> -KeyName ApiServicesEnabled -KeyValue true
      

   4. Select the Enable OData Services SSL check box.

      If you're using the Set-NAVServerConfiguration cmdlet, set the ODataServicesSSLEnabled key to true.

      Set-NavServerConfiguration -ServerInstance <Business Central server instance> -KeyName ODataServicesSSLEnabled -KeyValue True
      

Prepare devices and network for the Excel Add-In

Network services such as proxies or firewalls must allow routing between each client device on which the add-In is installed and many service endpoints. Learn more about the endpoints in Preparing your network for the Excel Add-In.

Next steps

The next step is to get the add-in installed in Excel of the users. As an administrator, you can set up Centralized Deployment in Microsoft 365 admin center to automatically install the add-ins to users in your organization. Or you can allow users to install it themselves from the Office Store. Learn more at Get the Business Central Add-in for Excel in the business functionality help.

Related information

Configuring Business Central Server
Authenticating Users with Microsoft Entra ID