Skip to main content

SharePoint Integration

Learn how to configure a SharePoint site and its Excel documents so that Ardoq can import them

G
Written by Gleb Nikonov
Updated over a week ago

The Sharepoint integration requires that an Azure administrator configures several parameters in your Microsoft Azure instance before the integration can connect. The examples here will use the Microsoft Graph Explorer for the configuration.

Overview

The Sharepoint integration is designed to automate the import of Excel files from a Microsoft Sharepoint site. This way you can write data to Excel files from any other service and have Ardoq import the file on a daily or weekly schedule.

Integration Pre-requisites

  1. Create a Sharepoint site dedicated to storing the Excel files you want to import into Ardoq

    1. Upload an Excel sheet into the Sharepoint site

  2. Create an Azure AD application (link)

    1. Save the application ID

  3. Create a client secret for the application

    1. Select the application

    2. Go to Certificates & secrets

    3. Select Client secrets tab

    4. Create a new client secret and save it

Option 1 — Set Up Full Sites Permissions

Now we'll need to configure permissions for the Azure AD app we created in pre-requisites step 2. This will give the Azure app full permissions to access any Sharepoint site in your tenant. If you prefer a more limited security approach, look at Option 2.

  1. Go to your app and to Manage > API Permissions.

  2. Click Add a permission

  3. Select the Microsoft Graph under Microsoft APIs

  4. Select Application permissions

  5. Search for "sites" in the search bar

  6. Enable Sites.FullControl.All and Sites.Selected

  7. Click Add permissions

You'll be taken back to the Configured permissions section, where you should click Grant admin consent for [your tenant].

Your permissions should now look like the following:

You can now proceed to creating a SharePoint connection in Ardoq.

Option 2 — Set Up Limited Sites Permissions

This approach gives your Azure app access only to the Sharepoint site with your Excel files and no others.

Step 1 — Grant Sites.Selected permission to Azure app

You'll need to grant your Azure app the Sites.Selected permission:

Step 2 — Get Sharepoint Site ID

  1. Go to Microsoft Graph Explorer

  2. Log in to your Microsoft account in Graph Explorer

We'll need to make a request in the Graph Explorer to get the Site ID for the Sharepoint site we created earlier.

In the Graph Explorer, set the request type to GET.

The request has the form:

https://graph.microsoft.com/v1.0/sites/{hostname}.sharepoint.com:/sites/{site_path}?$select=id

  • {hostname} is the name of your Sharepoint domain

  • {site_path} is the name of your Sharepoint site as it appears in the URL

For example:

https://graph.microsoft.com/v1.0/sites/ourdoq.sharepoint.com:/sites/TestUploadSpace?$select=id

Once executed our example query returns the following:

{
  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites(id)/$entity",
  "id": "ourdoq.sharepoint.com,7d5125e9-9f68-4e58-9c50-039bb6c097e6,a6cadcae-49aa-465c-8bd9-646e249cb576"
}

The id in this case is the Site ID and we want to save this value to use later.

Step 3 — Set Permissions for Sharepoint Site

We'll do this step by making an API call to set app permissions using an API client (like Postman, Insomnia, Bruno, etc.).

Configure OAuth 2.0

Create a new request in your API client and add an OAuth 2.0 authorization.

  1. Go to the Authorization tab

  2. Set the authorization type to OAuth2.0

  3. Set the following fields:

    1. Set Grant Type to Client Credentials

    2. Set Access Token URL to the endpoint: https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token

      1. Where {tenant_id} is your MS365 tenant ID

    3. Set Client ID to your Application (client) ID for the Azure application you created

    4. Set Client Secret to the value of the secret your created for your application

    5. Set Scope to offline_access https://graph.microsoft.com/.default

Configure Request

Set the request type to POST and use the following endpoint structure:

https://graph.microsoft.com/v1.0/sites/{Site ID}/permissions

Site ID is the ID we retrieved in the previous step.

Set the JSON body of the request to follow this form:

{
    "roles": [
        "write"
    ],
    "grantedToIdentities": [
        {
            "application": {
                "id": "{client_id}",
                "displayName": "{app_name}"
            }
        }
    ]
}

Client ID is the Application (client) ID of your Azure app. App name is the display name to give to the app.

The body of the request should look like this:

When this request is successfully completed it will return a 201 Created response.

Setting Up the Connection

The Sharepoint integration happens at the Excel file level. That is, every individual connection will only reference a single Excel file in your Sharepoint site. You will need to set up a new connection for every new Excel file you wish to import, but the Tenant ID, Application ID, and client secret will remain the same.

Connection Parameters

You'll need the following parameters to configure the Sharepoint integration within Ardoq:

  1. Tenant ID

    1. Which you can find with these steps

  2. File URL

    1. Which can be retrieved by opening your file from your Sharepoint site and copying the URL

    2. Ardoq expects the following formatting for the URL:

      https://ourdoq.sharepoint.com/:x:/r/sites/TestUploadSpace/_layouts/15/Doc.aspx?sourcedoc=%7BCAA10748-A989-487B-8691-F733B73E0D5D%7D&file=TPM%20-%20Technology%20Portfolio%20Management%20Excel%20Template(3).xlsx

  3. Application ID

    1. Which can be retrieved from the application you created in pre-requisities step 2

  4. Client secret

    1. Which you should have saved in the pre-requisites step 3

Ardoq Integration

In the Ardoq main menu go to Home > Import and Integrations, click View all integrations and select Sharepoint.

Go to the Connections tab and click Create new connection.

Give your connection a descriptive name and fill in the Sharepoint file URL, tenant ID, Application ID, and the client secret.

Importing Data

When you open your connection, the worksheets from your Excel file will be loaded in as individual tables (including any hidden worksheets you may have in the file):

Select the relevant tables and any columns you wish to import. From here you can perform the mapping and import process as usual, including setting a schedule for the import.

Troubleshooting

Client Secret Expiration

Keep in mind that the client secrets you generate have a default expiration period of 180 days. You'll need to create a new client secret and update your connection once the secret expires.

SharePoint URLs

Microsoft SharePoint has multiple ways to share a document through SharePoint, but not all of them are compatible with Ardoq's integration.

For instance, the URL generated by clicking a document in SharePoint and selecting Copy link will not work with the Ardoq integration.

That is, links to Excel files that use formats like the following will not work:

https://ourdoq.sharepoint.com/:x:/s/Site3/EURYKKPV-XxCnyXKGZz3IokBi8s-teaFh64P31QTjV4_SQ?e=YUMbCe

We recommend opening your Excel file in your browser and copying the URL from the address bar. The file format should look like the following:

https://ourdoq.sharepoint.com/:x:/r/sites/Site3/_layouts/15/Doc.aspx?sourcedoc={A3285844-F9D5-427C-9F25-CA199CF72289}&file=AR - Application Rationalization Excel Import Template(1).xlsx&action=default

Related Articles
Excel Integration
Customer Story: How Innovation Norway Automatically Updates Their Integration Landscape In Ardoq
Azure Integration
Embedding Presentations Into Microsoft SharePoint Using Iframes
How to map Microsoft Azure resources to Ardoq Solutions
Did this answer your question?