> For the complete documentation index, see [llms.txt](https://docs.elimity.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.elimity.com/sharepoint-lists/step-by-step-deployment-guide.md). # Step-by-step deployment guide This article describes how to use the SQL gateway in order to import Sharepoint list data into Elimity Insights. ## 1. Intro The SharePoint Lists integration does not have its own gateway. Instead, it uses the [SQL gateway](https://docs.elimity.com/gateways-sql/step-by-step-deployment-guide) with the **DuckDB** **driver** and the [ERPL web extension](https://erpl.io/) to read SharePoint lists and import them into Elimity Insights.\ \ The DuckDB driver requires SQL gateway `v4.0.0` or later, which is compatible with Elimity Insights server versions `>= 3.46.0`. To set it up, follow the steps below. ## **2. Creating a dedicated app registration in Entra ID** As usual we recommend creating a dedicated app registration for this connector. 1. In Entra, open your tenant's app registrations by typing 'app registrations' in the global search bar and clicking the **App registrations** service. 2. Click **New registration**. 3. Choose a name for this registration (e.g. `elimity-insights-sharepoint-lists`). 4. Leave the defaults for 'Supported account types' and 'Redirect URI' and click **Register**. Note down both the **client identifier** and the **tenant identifier**. ## **3. Generating credentials for the new app registration** The connector authenticates as the app registration using a client secret. Click **Certificates & secrets** in the menu on the left and add a new client secret. Immediately note down the **value** (Entra shows it only once). ## **4. Granting read permissions to the Graph API** Click **API permissions** and add this **Microsoft Graph → Application permission**, then grant admin consent: * `Sites.Read.All` — read site data ## 5. Configuring the gateway At this point you should follow the [installation note](https://docs.elimity.com/gateways-sql) and the [Configuring the gateway](https://docs.elimity.com/gateways-sql/step-by-step-deployment-guide#id-1.-configuring-the-gateway) section of the SQL gateway docs, keeping in mind the following: * Sharepoint Lists needs some SQL queries to run before data can be retrieved. To do this, add the following property to the SQL gateway's JSON configuration file, filling in the tenant identifier, client identifier and client secret you obtained in steps 1 and 2. * Also in the JSON configuration file, set the `driver` field to `"duckDb"` and the connectionStrings field to `[":memory:"]` . Example below: {% code overflow="wrap" %} ```json { "connectionStrings": [":memory:"], "driver": "duckDb", "setupQueries": [ "INSTALL erpl_web FROM community;", "LOAD erpl_web;", "CREATE TEMPORARY SECRET ms_graph (TYPE microsoft_graph, tenant_id 'SHAREPOINT_TENANT_ID', client_id 'SHAREPOINT_CLIENT_ID', client_secret 'SHAREPOINT_CLIENT_SECRET');" ], "jwtValidationBaseUrl": "https://example.elimity.com", "jwtValidationGatewayUrl": "https://gateway.example.com", "jwtValidationSourceId": "42" } ``` {% endcode %} You can now follow the rest of the SQL gateway deployment guide. ## 6. Querying Entity types and Relationship types from Sharepoint lists Instead of reading from a table, you read from a list with: {% code overflow="wrap" %} ``` graph_sharepoint_list_read( 'site-name', 'list-name', secret := 'ms_graph' ) ``` {% endcode %} The last argument references the secret defined in setupQueries by name. #### 6.1 Entity Type query examples This is an example which includes different queries for importing different entity types. Take note of the specific query format and attributes as described [here](https://docs.elimity.com/gateways-sql/step-by-step-deployment-guide#query-format). {% code overflow="wrap" %} ```json "entityTypes": [ { "attributes": [ { "description": "", "id": "description", "name": "Description", "type": "string" } ], "icon": "label", "id": "businessRole", "plural": "Business Roles", "query": ''' SELECT Code, Title, Description FROM graph_sharepoint_list_read( 'https://4s0pkg.sharepoint.com', 'BusinessRoles', secret := 'ms_graph' ) ''', "singular": "Business Role" }, { "attributes": [ { "description": "", "id": "system", "name": "System", "type": "string" }, { "description": "", "id": "businessRoleCode", "name": "Business Role Code", "type": "string" } ], "icon": "label", "id": "technicalRole", "plural": "Technical Roles", "query": ''' SELECT Code, Title, System, BusinessRoleCode FROM graph_sharepoint_list_read( 'https://4s0pkg.sharepoint.com', 'TechnicalRoles', secret := 'ms_graph' ) ''', "singular": "Technical Role" }, { "attributes": [ { "description": "", "id": "email", "name": "Email", "type": "string" }, { "description": "", "id": "department", "name": "Department", "type": "string" }, { "description": "", "id": "businessRoleCode", "name": "Business Role Code", "type": "string" }, { "description": "", "id": "technicalRoleCode", "name": "Technical Role Code", "type": "string" } ], "icon": "person", "id": "user", "plural": "Users", "query": ''' SELECT UserId, Title, Email, Department, BusinessRoleCode, TechnicalRoleCode FROM graph_sharepoint_list_read( 'https://4s0pkg.sharepoint.com', 'Users', secret := 'ms_graph' ) ''', "singular": "User" } ] ``` {% endcode %} #### 6.2 Relationship Type query examples This is an example which includes different queries for importing different relationship types. Take note of the specific query format and attributes as described [here](https://docs.elimity.com/gateways-sql/step-by-step-deployment-guide#query-format). ```json "relationshipTypes": [ { "attributes": [], "from": "user", "query": ''' SELECT UserId, BusinessRoleCode FROM graph_sharepoint_list_read( 'https://4s0pkg.sharepoint.com', 'Users', secret := 'ms_graph' ) WHERE BusinessRoleCode IS NOT NULL ''', "to": "businessRole" }, { "attributes": [], "from": "user", "query": ''' SELECT UserId, TechnicalRoleCode FROM graph_sharepoint_list_read( 'https://4s0pkg.sharepoint.com', 'Users', secret := 'ms_graph' ) WHERE TechnicalRoleCode IS NOT NULL ''', "to": "technicalRole" } ] } ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.elimity.com/sharepoint-lists/step-by-step-deployment-guide.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.