Connecting to SharePoint

A SharePoint Connection shows a SharePoint document library in Files, as a folder named after the Connection, so your reports can use the files in it. Magic Suite reaches the library through Microsoft Graph, signing in as an app that you register in Microsoft Entra ID.

Before you start

  • Register an app in Microsoft Entra ID and give it permission to the files, as below
  • Have an administrator to hand who can grant admin consent for your organisation
  • Find the Drive ID of the document library to show, as below

Registering the app

  1. Sign in to the Azure portal, open Microsoft Entra ID, then App registrations, and click New registration

    App registrations in Microsoft Entra ID, with New registration

  2. Enter a name, such as Magic Suite SharePoint, leave the other options as they are, and click Register

    Registering a new application

  3. On the app's Overview page, note the Application (client) ID and the Directory (tenant) ID

    The app's Overview page, showing its Application (client) ID and Directory (tenant) ID

  4. Open API permissions and click Add a permission

    The app's API permissions page

  5. Choose Microsoft Graph

    Choosing Microsoft Graph

  6. Choose Application permissions

    Choosing Application permissions

  7. Under Files, tick Files.ReadWrite.All, and under Sites, tick Sites.ReadWrite.All, then add the permissions
  8. Click Grant admin consent for your organisation, and confirm when asked. This needs an administrator

    The added permissions, with Grant admin consent

    The permissions once admin consent is granted

  9. Open Certificates & secrets and create a new client secret. Copy its Value straight away: it is shown only once, and it is the Connection's Secret. Note when it expires, as the Connection stops working then

    Creating a client secret under Certificates and secrets

Finding the Drive ID

Each document library has a Drive ID, which usually starts with b!. Microsoft's Graph Explorer can find it:

  1. In SharePoint, choose a file or folder at the top of the library you want to show. One with an unusual name works best, with its extension for a file
  2. Sign in to Graph Explorer
  3. Set the request URL to https://graph.microsoft.com/v1.0/me/drive, then open Modify permissions and consent to Files.Read.All
  4. Open the Request body view

    The Request body view in Graph Explorer

  5. In Sample queries, search for search driveitems, and click the result to fill in the Request body
  6. In the Request body, replace Contoso in queryString with the exact name of your file or folder, then click Run query

    The search driveitems query, with the file's name as its queryString

  7. In the results, the Drive ID is the driveId under value, hitsContainers, hits, resource, parentReference

    The search results, showing the driveId

Creating the Connection

  1. In Admin, open Connections, then click Create
  2. Choose Microsoft SharePoint as the type
  3. Enter a name and, optionally, a description. The name is the folder the library appears as in Files, so choose one your users will recognise. It cannot contain /
  4. Fill in:
    • Application ID: the app's Application (client) ID
    • Secret: the client secret's Value
    • Configuration: see Configuration below
  5. Read Only starts ticked, so nothing can change or delete the files in the library. Untick it if your reports need to save files there
  6. Optionally, set Credentials Expiry to the date the client secret expires, so the Connections list shows when it needs renewing
  7. Click Test Connection, then Save

If Test Connection fails, check the Application ID, the Secret and TenantId, and that admin consent was granted, then check DriveId.

Configuration

The Configuration is JSON. TenantId and DriveId are required.

SettingWhat it doesDefault
TenantIdThe app's Directory (tenant) ID, from its Overview page.None
DriveIdThe Drive ID of the document library to show, which usually starts with b!.None
DrivePathThe folder within the library to show as the top folder, such as /Reports. Leave it as / to show the whole library./

For example, to show only the Reports folder of a library:

{
  "TenantId": "6c1d2e3f-0a4b-4c5d-8e9f-123456789abc",
  "DriveId": "b!AbCdEfGhIjKlMnOpQrStUvWxYz0123456789",
  "DrivePath": "/Reports"
}

Tip: Connections, Tools, Configure a Connection fills in the Configuration with a form, with help on every setting.

Using it in reports

The library appears in Files as a folder with the Connection's name. Reports use its files with the [File.Xxx:] macros, such as [File.XlsxTable:] and [File.CsvTable:], with a path that starts with that folder. For example, for a Connection named SharePoint:

[File.Exists: fileName="/SharePoint/Sales.xlsx"]

Note: A search in Files, or through the Magic Suite API, does not look in SharePoint folders.

More information

Reconnection

Reconnection

Reconnection

timed out

timed out

Attempt of

Reload
An unhandled error has occurred. Reload 🗙