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:
https://<tenant_name>.sharepoint.com/sites/site/_api/Web/GetFileByServerRelativePath(decodedurl='<location of an Excel file in the sharepoint>')/$value
Note: For some scenarios, the site parameter of the URL may need to be modified according to the company’s Sharepoint structure. Review this URL with your Sharepoint administrator to make sure you use the correct one for your specific installation.
- 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
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.
Quickstart: Register an application with the Microsoft identity platform