Dynamics 365 Business Central: Microsoft SharePoint Connection Setup page (Open Excel in Sharepoint and OneDrive)

Hi, Readers.
Today I would like to talk about the Microsoft SharePoint Connection Setup page in Business Central.
Why did this topic come to mind? Last week I received a strange question where an error occurs after a user clicks Open in Excel on any page.

I think you should know about the features Open in Excel and Edit in Excel in Business Central. If you click on them it is downloaded the data into Excel. It’s very convenient.

Today I reproduced this error in my test environment, this was a Sharepoint error.

Could not connect to the SharePoint site. SharePoint Server returned the following error: Could not connect to the following SharePoint site: ‘https://jbserpdemo.sharepoint.com/sites/BusinessCentral/’. SharePoint Server returned the following error: ‘The remote server returned an error: (401) Unauthorized.’..

Test Video: Only Open in Excel does not work, Edit in Excel can download to excel.

Why is this? After researching, I found that the user had set the wrong information on Microsoft SharePoint Connection Setup Page.

If you delete this setup, Open in Excel will work.

But that’s not the end of it. What happens if this setting is set correctly?
It seems we can connect SharePoint with Business Central using this page, but unfortunately, I didn’t find any official documentation about this setup page.

But I don’t want to give up, the following are my test results.

First I create a new site (Business Central) and a new Document Library (TestBC) in my Sharepoint.

Then add settings on the Microsoft SharePoint Connection Setup page.

Service ID: SERVICE 1
Description: Test Sharepoint
Location: https://crmbc572567.sharepoint.com/sites/BusinessCentral
Folder: Default\BC
Document Repository: TestBC
User Name: admin@CRMbc572567.onmicrosoft.com
Authentication Type: OAuth2

Test Video: Wow, you can open Excel in the browser directly, and the file is automatically saved to the specified folder in Sharepoint.

PS: You can leave the Document Repository and User Name empty, then the excel file will go into your personal OneDrive folder.
For example:

Test Video: Similar to the result above, the Excel file is opened in the browser, and the file is automatically saved to the specified folder in your personal OneDrive.

Update 2022/09/07: Microsoft are announcing deprecation of the legacy SharePoint Connection Setup page and SharePoint integration capabilities.
More details: Configure OneDrive integration

END

Hope this will help.

Thanks for reading.

ZHU