Content

In this document we explain  how to create an Excel data source in Denodo that retrieves an Excel file stored in SharePoint 365 using the Sharepoint API. The same steps can be performed for JSON, XML  or Delimited files as well.

It is also possible to access SharePoint lists using OData following the steps described in How to connect Denodo to Sharepoint Online using OData with OAuth authentication. Unless you need to access SharePoint using OData the procedure explained in this document is the recommended approach.

Register an App in the Azure Portal

For the SharePoint application to be accessible, API permissions have to be configured. A client application token has to be generated for the access token. In order to generate the token the following has to be done in the Azure Portal:

• Login to the Azure portal
• Click on App registrations > New registration.
• Select the desired supported account types. The type of the application support depends on your requirement. For the purpose of this documentation we are using Accounts in this organizational directory only (Default Directory only - Single tenant).
• Provide a Redirect URI. It should be https (e.g. https://localhost:9443/oauth/2.0/redirectURL.jsp)

Client secret key generation

After having registered the app, we have to generate a new client secret key. In order to generate the key:

• In the Overview  section, click on Add a certificate or secret
• In the Certificates & secrets page select the option New client secret.
• After generating the Client secret, copy the Value which will be the client secret.

The API access is now set up to connect Denodo to SharePoint.

In the App Registration page in the Azure portal, select the app that has been registered for the SharePoint integration with Denodo:

• Copy the Application (client) ID this will be the Client identifier in the data source configuration.
• Copy the Value of the Client Secret generated for the new application from the previous step.
• The Directory (tenant) ID will be the tenant_id needed later.

Creating an Excel data source

In this section we will explain how to create an Excel data source in Denodo to read Excel files from SharePoint.

• From the Virtual DataPort Administration Tool or Design Studio, create an Excel Data Source.
• In the data source configuration, select the File Location as HTTP Client and select Configure.
• In the Edit HTTP Configuration dialog box set the HTTP Method to GET and provide the URL with the following format:

• In the Authentication section, select OAuth 2.0 for Authentication.
• Paste the Application (client) ID from the newly created application to the Client Identifier and the Value of the Client Secret to the Client Secret.

• Then select the Launch the Oauth 2.0 credentials wizard. The Oauth 2.0 credentials wizard will pop up.
• As for the  Token endpoint URL  use https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token where <tenant_id> matches the tenant_id copied in one of the previous steps.
• Provide the Authorization server URL (again, replace <tenant_id> with the appropriate value).

• Select the Redirect URI, provide the redirect URI that you used to register an application in the first step. Make sure that it matches.
• For the Scopes option provide the values  

• https://<tenant_name>.sharepoint.com/AllSites.Read
• offline_access

Remember to replace <tenant_name> with your actual tenant name. The value offline_access is required in order to obtain the refresh token.

• After providing all the URL’s click on the option Generate Authorization URL.
• When the Authorization URL is generated, click on the Open URL.
• Once the URL is opened on your desired  browser, provide the permission, and once the permission is given the redirect URL with code would be generated. In this example, the format of the URL will be:

• Copy the full URL and paste the same on the Paste the authorized response URL and select Obtain the OAuth 2.0 credentials.
• Select Ok.

Once the OAuth 2.0 Credentials are obtained , test the connection to check if the connection can be established.

Now you will be able to create a new Base view from this Excel data source.

References

Quickstart: Register an application with the Microsoft identity platform

Excel Sources