SharePoint Online Connector - Source Code
<ApiConfig Name="SharePoint Online"
Slug="sharepoint-connector"
Id="9B0C106F-1FE2-4EC2-A103-C20E0885F3F1"
EngineVersion="9"
Version="12"
Desc="SharePoint Connector can be used to integrate SharePoint and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete SharePoint data in a few clicks!"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAACXBIWXMAAAsSAAALEgHS3X78AAAPLElEQVRo3r2ZebBcVZ3HP79z7u3u191vf8nbEgjJCyEJe0JBlCmHASmNyOgMCiEJAcFxwXVmLLVqpmTchtJyHZkZCsVAIETcEEWBQR0QdUQWjUkgEMwGWXgv21u7+95zfvPHvd2v+yWBRJw5Vaf69L29fL+/5Xt+53eFYx0XX0x3exd7v7WWviuWE6m3g9+6ywE0v+VvOz28wale6FUXe3QWSlGhrLBbRP6AMf9tjPlJ9L1vPwOQvfxKm81k/PCa1VpYfhUZG3Dg9ls53iHH8qFpb19GYA2777qTniuuFI/IS+vu9B2XXd7rVT/iVVc6a7u8gnMx6j2qigIYA9aCMYhzkTHmfmPsjaW71/4KoGXl1WZ4zWpfWHE1TdmQoW/c8ucn0LdsBbvuuoPeK5bL7nV3KsD0y5etcl6/6IOwI44qOOecU1VVNQriVQFQEUXECwLGBCaTwahijdzUWSx8eOctN0etV73DHrr9Vtd69bXks1l23/zvx0zAvCL4K1cm4JdNgu+5YvnXJMysxpgOH1Ui9d5r8lsWkAS6iIoIIgISqGAB1TiOVb1qJnP9gfHSr6dd+3e9h26/1bVfc509tPobOK/H5QHziuDXrqH3iuVoNlSA3mUrbpVs9vq4Uomdc96rhj4FrSDpWlQAERARTV5RAYVAEVy5XHFhsKgURY/0veu90w588+tu2nXvMi/d8h/MvP4Dfx4CxiS3jTV2z+rV9F258gbJZq+JS6XIo9aD8UnYoKqiCimRBDCSrqHqDQVNPiMZX6lUfBAOjFcq93zlRz+Twa/f7Ge8533svOmrr55A97IVvHDHbfQvv8q8eOca17/8qvMx5hNxqaQetV4Rr6pVq3sOB0/9tQR04hEURVUh4yrligvD13zqB3d/GuDQRMkCnPShf3h1BMLA0r98JcOx9emlz6kxeHBea5YXn1j/iJbXNAWknlDqjfTzqhDGpRKR8x854f0fOmVk9ddd73veZ7Z++QuvjsALa25DxNiRb32TGStWvZkgWOKiyHlVm4BPrF6dWgWXgk+AJySqRFTSkCJJkGreKEQuDMKJSvQhgOFSSeb/48eOTUY7P/j3HHjxBeOjSEgkD0Qwxkg+CM3oujuj/pWrvq1BeFlUKsWx99Z5T+y9OFV8OpVakibJi1RxIiTg0wgikdSElDFGjTEqNjBNYTDY39GxYMONnxma+f4PSy6X1ec+f+PLEggO7dol/jt3+6k3PDAK7o3v/0Buw8Hhv3RxDGAktVoSD4rWq55qoppaS9xa+MgUMkKqTEluCOqdM3baofGJ84F7Kt6ZnZ+/0b2SB4L42+s0ePsVC+NyuRMRV/O9iEF1/BdD+xY1BUGXc14dKg5wIjhj8CmAKvuqXFatL86n4CetX32v0hBKKoh6Y4jVLwHu8VUjrfs+iIHL//rIBPKr3vGxiZHhf8Xaqm5PTmMYVWW0XNYaMSPJD05dH2GqV2R09DDwTO4RYAxqkr1CAQdzAAZnzTF27XeNy2Q8qrB6HbS0wN8sbSQAfEajCIIgRlUaQKhSk5HkmtatX74SUYViESoRElXqwoZJ0gAuRhwqQYA6h6AtAHzkg5Va/NzzE0tnh+PNF8MP7gexcOnrkyTOX32tH983pAShQdCjWLM+weVoFm+YAPkCcmgYKZVQYxIvGEm2becgm4X2dmhtV4pFyOXEhuFg1NZxD8IzGPMob7v0MQAeehRKJcMlF3l++GBSIC69EMlfc60f37dPCAI9GjgxgjFJiPlUIo0xqAgu8dLRCRw8hJRLqLGJ8jiHhhmYcQI6YwYUmyEME28aUYwVcrkE4MQ4iDyBMV/hLW9cA8CPf2pZeqHjvoewYYjkr7nOj+8/CgHAWIt3DiqV5FoYJuBiB4GFpqakZD6MgKYEhhMCNkDiGO3sQk9ZAG1tSZilioUxijGSvFqXkDEBuSYhDKE08TBiruaSi7bJ/T+3+oYLHA8+TIAgtdCYuklYiy+X6ers5LJzFnPmiSfSVijgUPaNjvHbbdtZ+9hjR/ZCdcOSJNEljtG+fnThaRAEEMcJ8ckpiEmEwUiAmCRko4ojjpVi8XWUJp7g/p8v1Tdc8Bt58GGrF7/OBUDjn1Y3shT8kgXzuf2d1zHQ2wNBeFii3rdhA/vHxhBjUZnyW1XPuBjt7UdPPT15730j+EZVm6J2xmIEJiYimnIdRNFDmYcePbdy0fmbgoceNcGkleoIGIP3nlyxwE0rVzBn+nR+v20Hn7vvx2zeu5eulhYuOnUhS+bORdLTFtZwmDGMAe/RYjM6f2Hj9cOBT1kbSb2hiWdMSBRF5AvFuFJed+bj6xf/bvHplQBhUpdTAEYEH0WcNjCH/vYOnCq3/fKXrP3J/TCtC1R5YP16sq0tOGMhDCc3sSnGEBH8nLmQawIXJ8l5NOBVUqbhniQkRBETUi5VfGvbac+MjX0c+BcDdXo+BUTkPNYIE5UKS884nZ55JyeJl8kQdnRQFkOsOjWWJ6cqtHdAdw94NxlSRwP+St5ISARMTBAh15/02O+6bLho0Q1RuUz9TqwiEATsOXSIC+adzML+fqY1N/O2xYuYOX06BysVdr40CEGABEEt7BrjOr02vQdaWhO1sVPuNVj8SGszScJUQ8oI4GhubvZxvM2GZy8+jEBVgfCenz2zmbnTpzPQ3U1PWxuvnTuXFeecw5yeHn66ZQuVOMYEQbLTGjM5USSbQ3v7EtWZSvJYgB9+T2pERCQUptlwUR2BKUlowpCRiQnW/fa3PLZ9O0ZgRlsbxaYcZ8+axRl9fax96imwFjEGrdsPREFzTdDVffwWf3lvaLKLqgbW9psjxX51eu8x2SxBocBDT29m5a2rueArX+UXz20hjmOWLlzAawbmoFGEST0otZLBNBRsx5m49fGfrquhlCa1SeTWNMjoFBXJZDJ474nHx5MdOJ9n/TObee/d38G5pPLuaW1NbJACrwKWupr/yGDNy4A9IvDGe+n1YKqEVq2oUcQpvb3ccMlSvvu79Ty1excVr+T6enn3a19DYC3Oe7bt35/EeEpC6+RTBUhroKRs0DrwcmxgzWGkNC3GVI2R4EgekLQ2zwQBbz37bN565pkMjY5S8Z7WpiYKhQIAq3/9Pzy5Yyc2m00ONSKJIKffFxvQOvtkbBDgyxPE42OMjo2g3k/uBw1gX4FUbW0UEQmNVGy4+Jwboig6vCAzhghoz2bJBJamTIZcGFKOYzbt3s3XHn6Ej9//AN7aRLHqyCfHSg/ZJuL5Z1DOF/H5Ipm2DjpaWjHqKVfKdap0VOmcsq5ubMaTz5tm9IEjekABgoDBkRHeufYuCs3NtBYLZDMZxqOYfePjxOUy5PPYpNWCqQ9DSM++kLQvPBX1lI1hJJuna+ZsCvsH2TW091gtTm0jSw0dBgF59NYgPcRI2uWQBhBBANYyFkWMHTg4aYUwJCgWk45E/bksDaFqByKt8cEIRpPzg6pnjxO6p/UwwxpeGNxb5/2jxX9VPkUQceSabFO5/Oyqns4fGZD6U5hO9US1RS5hiAlDTBAgIjjv0WNsgFd7Q9XNLhBhb+yQzul0tabnAvOyCqQ1EiI+bMrRFtjPfqq7q2wwYoAI8GlXIm6YEAOxqsZeNVaIVYmT5oRBxGitdq+WIdU/Vq0Vig3NLUk2010eOrq6MWFwlNCp1T/peUEiaW8P2yYm7t2xcOA2HnyUQL1uolBcgHd1eVCXWNVyua7GEWtR59ByWcWINDSySA/tURm6utFsDkn7RdXQqnYnnCqj2SamFZvZOzoyRV7rgCceqNDammkbH9uwpFhYeS8wb2afBMbIW0258mmPTu0LJT/o3GEnJ61U4qDY3C3dfWc6Va0/0YlI0uzKZpEzzkFsAD6ueabadkzzTA8i0lMoKmNj1dK5Wi5UQydGTGg7OjJtY6O/Pr0pd+m9J/UPz970vNk8f7YX/sQxb/PulS9lMrcPj4zERsQmwlMXQtXTm3e1sEmc1OAJFWtlbjShm3ftdBirdVJpyWRECkVy42O+LbRf+v7sWR89L59xJ2163m5dMMfN2LiFoOnd18tEpWzwzjc2t8zkSav6jCsI6HvTZcGLF5wbxVGl3zflE51Mg0frOnTi6qwukw0tpYGMqKAShmJb2wINw9opzpZLZNDdTaXxe7tzmZs2zp/zh/OAgU3Pmy0L5ri+Dc+RN4Zg4j9vUvPOdzl/1P6OqTX5VSxBa3tSLVgTyWR9r1r3JOZI8V5tJdY1eVUR1TBjsk53tE+Mfymu2BZENDR2sBjYZ07ONz35wNwTh4eA8Kmn7SlNWf+HU2b7EzZuISvCs/NnEwD4W24+trj55BfIFIsKkLF2l0k6CyYNEZU0D6bG+1Qy1cavImoyGbKVeNPQkrO/XP9Xg8BWgMc32Fm5rC7uaHHf6etm3qYtbF4w0NBaPOZx+px+KmkvN2/kyUxpooIxGdInSFXwR4z3WmeayRYlaDEMKJT1CYCBzdszQ8OHvDWGrLXeGqMtgXUbFwyQ2fAcJ29sBH9MTynrx/orL08MuWkPjw/0bm4y8qtMoYim6qVJKE0+lanfwGpkap9RxATtYyOu18f3ApzT3h4fPHAw3rfo1HjXRMnvHB/XjSngZ0+dy7MLB47/MWvD+KdPkC+PMS8rFqDZmC+2WCF9KqmISAJsSrJOeqUKXjzE2bZWZgjfWztz2mMnqspdW3f4aRf+RfJfS86C88569c+JG8ZnPslQtpmCepd/flDWz+n+YVu5dEdLV5d1SFQNjdQTOsUTNfCKROSawtljI4ML4vJHAc4dHIFzT2Xwvx798z0nPtIoaMyTA73MS4/QZ1m9rmvk0COtXV0ZJ+K8ECMNyarUBEpUkYhCPpzjKuXTcJffMnP61ssOVczd01v0rwZHE/n+vx4L//gSAGduGzIAq3YMZudtH7pj+ksTmtk7rmzdp2wditi2L2L7/mTuOODYO66FUdX5e0aeXbbn4HkArx+OLcB9qhQfeZz/n3HLGhZsG0o8sH2oZrKzdu6/dPaO/b+YvmN/peVArLkDTrP7Yy0Oq7bvHdNZu4f/+Nq9I//8tRf3NgNceLBiq9+d//R2+LebjxvKn1xKLHx6JxTybDyhk0Xbh+QJB8zuUoDzdx08bcjrohKcDJLLCTt7rPz+Gol/s6q7cwzgTfvGzX2deY8q527dw2+efAIuu+S4cfwvqB+ixBx3rQgAAAAASUVORK5CYII="
>
<VersionHistory>
<Change Date="2023-08-23" Type="New">Allow CRUD operations on ListItems Table (e.g. Allow Insert, Update, Delete, Lookup).</Change>
<Change Date="2023-08-23" Type="New">Added new endpoint get_excel_worksheet_autodetect so user doesnt need Address Range (Dynamically detect max rows and columns for data read.</Change>
<Change Date="2023-06-19">Fixed typo in example description.</Change>
<Change Date="2023-04-12">Added 'Filter' parameter to 'get_list_items' and 'get_list_items_dynamic' endpoints"</Change>
<Change Date="2023-03-28">Updated examples and added two new tables - "Sites" and "Drives"</Change>
<Change Date="2023-02-24">Updated upload_file endpoint to refresh DriveId dropdown on UI when SiteId changes</Change>
<Change Date="2023-01-06">Added Retry for API Limit errors (status code 429 or 503)</Change>
<Change Date="2023-01-06">Added Login Prompt option (e.g. Force Login Prompt for new account).</Change>
<Change Date="2022-12-02">Added SystemLists table. Removed readonly columns from 'create_list_item' and 'update_list_item' endpoints.</Change>
<Change Date="2022-11-23">Added support for Lookup type columns/fields when inserting and updating a list.</Change>
<Change Date="2022-09-19">Added support for Lookup and Managed Metadata fields. If user needs to expand specific fields then they can set expand parameters for complex types.</Change>
<Change Date="2022-06-02">Added new endpoint get_list_items_dynamic to read complex datatypes (e.g. Lookup, Location). Other endpoint will show null value in such case use this one.</Change>
<Change Date="2022-03-16">Added ContinueOn404Error option for get_list_item endpoint (read single list item by ID) to prevent error if ID is bad.</Change>
<Change Date="2022-02-24">Added 'SiteId' parameter to 'upload_file' endpoint.</Change>
<Change Date="2022-01-27">Added server side WHERE clause support for ListItem Id column for SELECT, UPDATE and DELETE operations for faster response / lookup operations</Change>
<Change Date="2022-01-27">Added download_file_to_disk endpoint to download a file</Change>
<Change Date="2022-01-27">Added support for very large file upload</Change>
<Change Date="2021-10-05">Made parameter "expand" configurable</Change>
<Change Date="2021-10-05">Changed comments in examples</Change>
<Change Date="2021-03-29">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="Version 1.0" Url="https://graph.microsoft.com/v1.0" />
</ServiceUrls>
<Auths>
<Auth Type="OAuth" Name="UserCreds" Label="User Credentials" Desc="OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-register-app-v2."
HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=Default;Scope=[$Scope$];ScopeSeparator={space};ContentType=application/x-www-form-urlencoded;"
TestEndpoint="get_main_site">
<Params>
<Param Name="AuthUrl" Label="Authorization URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/authorize;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/authorize" />
<Param Name="TokenUrl" Label="Token URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/token;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/token" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True"/>
<Param Name="Scope"
Required="True"
Options="offline_access;Files.Read;Files.Read.All;Files.ReadWrite;Files.ReadWrite.All;openid;profile;Sites.Read.All;Sites.ReadWrite.All;User.Read"
MultiSelectSeparator=" "
MultiSelect="True"
Value="offline_access~Files.Read~Files.Read.All~Files.ReadWrite~Files.ReadWrite.All~openid~profile~Sites.Read.All~Sites.ReadWrite.All~User.Read~"
Desc="Permissions you want to use."
Example="offline_access~Files.Read~Files.Read.All~openid~profile~Sites.Read.All~User.Read~"
HelpLink=""/>
<Param Name="ReturnUrl"/>
<Param Name="SiteId"
Label="SiteId (select after pressing 'Generate Token')"
Required="True"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a default site you like to use for operations. You can always override this for various operations."
Value="root"
/>
<Param Name="ExtraAttributesForAuthRequest"
Label="Login Prompt Option"
Options="None=;Force login prompt=prompt=login;Force permission select=prompt=consent;"
Desc="Choose this if you want to force login prompt or permission prompt."
HelpLink="https://learn.microsoft.com/en-us/azure/active-directory/develop/v2-oauth2-auth-code-flow"/>
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
</Params>
<Notes>
<![CDATA[<p>Firstly, login into Azure Portal and there create an OAuth application:
<p />
<ol>
<li>Go to <a target="_blank" href="https://portal.azure.com/#home"><b>Azure Portal</b></a> and login there.</li>
<li>Then go to <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview"><b>Azure Active Directory</b></a>.</li>
<li>On the left side click menu item <b><a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a></b></li>
<li>Then proceed with clicking <b>New registration</b>.</li>
<li>Enter a name for your application.</li>
<li>Select <b>Accounts in this organizational directory only</b> as supported account type to access data in your company.</li>
<li>In <b>Redirect URI</b> section leave option selected at <i>Web</i>.</li>
<li>In nearby textbox enter <b>https://login.microsoftonline.com/common/oauth2/nativeclient</b> as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth</li>
<li>Use this Redirect URI in <b>Redirect URL</b> grid row.</li>
<li>Copy <b>Application (client) ID</b> and paste it into API Connection Manager configuration grid in <b>Client ID</b> row.</li>
<li>Then click on <b>Endpoints</b> link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
<ul>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize</li>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token</li>
<li>https://login.microsoftonline.com/common/oauth2/v2.0/authorize</li>
<li>https://login.microsoftonline.com/common/oauth2/v2.0/token</li>
</ul>
</li>
<li>Close "Endpoints" and create a </i>Client Secret</i> by clicking <b>Certificates & secrets</b>.</li>
<li>Proceed by clicking <b>New client secret</b> and setting expiration period. Copy the client secret and paste it into configuration grid in <b>Client Secret</b> row.</li>
<li>Use these scopes in Scopes field: <b>offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile Sites.Read.All Sites.ReadWrite.All User.Read email</b></li>
<li>Click <b>Generate Token</b> to generate tokens.
<div style="background-color: #f5f2c4; border-style: solid; border-color: #000000; border-width: 1px; margin-top: 3px; margin-bottom: 6px; padding: 6px;">
<b>NOTE:</b> If you are planning to use your current data connection/token for automated processes, we recommend that you use a generic account for token generation when the login box appears (e.g. sales_automation@mycompany.com instead of bob_smith@mycompany.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will fail. Another potentially unwanted effect of using a personal token is incorrect logging; the API calls (e.g. Read, Edit, Delete, Upload) made with that token will record the specific user as performing the calls instead of an automated process.
</div>
</li>
<li>That's it!</li>
</ol>
For more information you can read this link <a>https://docs.microsoft.com/en-us/graph/auth-register-app-v2</a>
Also
<a>https://docs.microsoft.com/en-us/graph/auth-register-app-v2</a>
]]>
</Notes>
</Auth>
<Auth Type="OAuth" Name="AppCreds" Label="Application Credentials" Desc="OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-v2-service"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-v2-service"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=ClientCredentials;Scope=[$Scope$];ScopeSeparator={space};"
TestEndpoint="get_main_site">
<Params>
<Param Name="TokenUrl" Label="Token URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/token;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/token" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True" Required="True"/>
<Param Name="Scope"
Required="False"
Hidden="True"
Value="https://graph.microsoft.com/.default"
Desc="Permissions you want to use."
Example="https://graph.microsoft.com/.default"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-v2-service"/>
<Param Name="SiteId"
Required="True"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
Value="root"
/>
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
</Params>
<Notes>
<![CDATA[<p>Firstly, login into Azure Portal and there create an OAuth application:
<p />
<h2>Step-1: Create OAuth App</h2>
<ol>
<li>Go to <a target="_blank" href="https://portal.azure.com/#home"><b>Azure Portal</b></a> and login there.</li>
<li>Then go to <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview"><b>Azure Active Directory</b></a>.</li>
<li>On the left side click menu item <b><a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a></b></li>
<li>Then proceed with clicking <b>New registration</b>.</li>
<li>Enter a name for your application.</li>
<li>Select one of <b> Accounts in this organizational directory only</b> as supported account type to access data in your company.</li>
<li>In <b>Redirect URI</b> section leave option selected at <i>Web</i>.</li>
<li>Copy <b>Application (client) ID</b> and paste it into API Connection Manager configuration grid in <b>Client ID</b> row.</li>
<li>Then click on <b>Endpoints</b> link and copy second, Token, URL to Token URL field in configuration grid. Usually they look similar to these:
<pre>
https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
https://login.microsoftonline.com/common/oauth2/v2.0/token
</pre>
</li>
<li>Close "Endpoints" and create a </i>Client Secret</i> by clicking <b>Certificates & secrets</b>.</li>
<li>Proceed by clicking <b>New client secret</b> and setting expiration period. Copy the client secret and paste it into configuration grid in <b>Client Secret</b> row.</li>
<li>Go to <b>API Permissions</b> section and add Microsoft Graph permissions for SharePoint and Drive, e.g. Sites.Selected, Sites.Read.All, Sites.ReadWrite.All, Files.Read.All, Files.ReadWrite.All.</li>
<li>Finally, <b>Grant admin consent</b> for your domain for your permissions.</li>
</ol>
<h2>Step-2: Register OAuth App in SharePoint Admin Portal</h2>
<ol>
<li>After we create OAuth App we need to add grant SharePoint access to that App using Admin Portal. Make sure you have admin permission to acces Portal. For detailed steps you may refer to <a href="https://learn.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs">this link</a>. Here are basic steps you can follow.</li>
<li>Log in to SharePoint Admin Portal and access below URL<br /> (Replace YOURCOMPANY)<br />
<pre>https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web</pre>
</li>
<li>Enter App Id and other information</li>
<li>Enter XML snippet which describes which permission you want to grant. Here is an example permission (App with FullControl / Read / Write)
<pre>
<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests>
</pre>
</li>
<li>Click Add to grant the permission. That's it!</li>
</ol>
<b>NOTE: </b>
After adding App in SharePoint if you ever remove the permission then you can visit the following URL and delete the App.<br />
<pre>https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web</pre>
]]>
</Notes>
</Auth>
</Auths>
<Template>
<EndPoint>
<Params>
<Param Name="DataFormat" Type="Property" Value="OData" />
</Params>
</EndPoint>
<EndPoint Name="ExpandFieldsSupport">
<Params>
<Param Name="expand"
Label="Expand (Output Fields)"
Desc="By default lookup fields are returned as (null). Select fields which you want to process (to speedup processing check only those fields which are (null) in the output)"
Value="fields"
OptionsEndPoint="get_list_fields"
OptionEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.lookup.listId || @.personOrGroup.chooseFromType)]"
OptionsEndPointLabelColumn="name"
OptionsEndPointValueColumn="name"
MultiSelect="True"
MultiSelectAllOnDefault="fields"
MultiSelectTemplate="fields/{@value}"
MultiSelectSeparator=","
/>
</Params>
</EndPoint>
<EndPoint Name="ListColumns">
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="188" />
<Column Name="parentReference.siteId" Label="ParentReferenceSiteId" DataType="DT_WSTR" Length="388" />
<Column Name="list.contentTypesEnabled" Label="ListContentTypesEnabled" DataType="DT_BOOL" />
<Column Name="list.hidden" Label="IsListHidden" DataType="DT_BOOL" />
<Column Name="list.template" Label="ListTemplate" DataType="DT_WSTR" Length="88" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.id" Label="CreatedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="createdBy.user.email" Label="CreatedByUserEmail" DataType="DT_WSTR" Length="124" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
</OutputColumns>
</EndPoint>
</Template>
<EndPoints>
<!-- ==================
ROOT
=================== -->
<EndPoint Name="get_main_site" Label="Read Main Site" Url="/sites/[$SiteId$]" Method="GET" CachedTtl="60">
<Params>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="500" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="siteCollection.hostname" Label="SiteCollectionHostname" DataType="DT_WSTR" Length="300" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- ==================
SITES
=================== -->
<EndPoint Name="list_sites" Label="List Sites" Url="/sites?search=*" Method="GET" Filter="$.value[*]" CachedTtl="60">
<Params>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="500" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="siteCollection.hostname" Label="SiteCollectionHostname" DataType="DT_WSTR" Length="300" />
<Column Name="createdDateTime" Label="CreatedAt" Length="25" DataType="DT_WSTR" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" Length="25" DataType="DT_WSTR" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
</OutputColumns>
</EndPoint>
<!-- =======================
DRIVES
============================ -->
<EndPoint Name="list_drives" Label="List Drives" Url="/sites/[$SiteId$]/drives" Filter="$.value[*]" Method="GET" CachedTtl="60">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="driveType" Label="DriveType" DataType="DT_WSTR" Length="60" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="quota.deleted" Label="QuotaDeleted" DataType="DT_I8" />
<Column Name="quota.remaining" Label="QuotaRemaining" DataType="DT_I8" />
<Column Name="quota.total" Label="QuotaTotal" DataType="DT_I8" />
<Column Name="quota.used" Label="QuotaUsed" DataType="DT_I8" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- =======================
DRIVE DETAILS
============================ -->
<EndPoint Name="get_drive" Label="Read Drive" Url="/sites/[$SiteId$]/drives/[$DriveId$]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="driveType" Label="DriveType" DataType="DT_WSTR" Length="60" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="quota.deleted" Label="QuotaDeleted" DataType="DT_I8" />
<Column Name="quota.remaining" Label="QuotaRemaining" DataType="DT_I8" />
<Column Name="quota.total" Label="QuotaTotal" DataType="DT_I8" />
<Column Name="quota.used" Label="QuotaUsed" DataType="DT_I8" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- ===========================
FOLDERS LIST
============================ -->
<EndPoint Name="list_folders" Label="List Folders" Url="/sites/[$SiteId$]/drives/[$DriveId$]/search(q='')" Filter="$.value[?(@.folder.childCount != null)]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- ===========================
FILES LIST
============================ -->
<EndPoint Name="list_files" Label="List Files" Url="/sites/[$SiteId$]/drives/[$DriveId$]/search(q='')"
Filter="$.value[?(@.file.mimeType != null)]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- ===========================
EXCEL FILES
============================ -->
<EndPoint Name="list_excel_files" Label="List Excel Files" Url="/sites/[$SiteId$]/drives/[$DriveId$]/search(q='.xlsx')" Filter="$.value[?(@.file.mimeType != null)]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- =======================
LISTS
============================ -->
<EndPoint Name="list_lists" Template="ListColumns" CachedTtl="10" Label="List SharePoint Lists" Url="/sites/[$SiteId$]/lists" Filter="$.value[*]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
</Params>
</EndPoint>
<!-- ==============================
SYSTEM LISTS
=================================== -->
<EndPoint Name="list_system_lists" Template="ListColumns" CachedTtl="60" Label="List SharePoint System Lists"
Url="/sites/[$SiteId$]/lists?$select=id,name,displayName,parentReference,list,description,createdDateTime,createdBy,lastModifiedDateTime,lastModifiedBy,webUrl,system"
Method="GET"
Filter="$.value[?(@.system)]"
>
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
</Params>
</EndPoint>
<!-- =======================
LIST FIELDS
============================ -->
<EndPoint Name="get_list_fields" CachedTtl="10" Label="Read List Fields" Url="/sites/[$SiteId$]/lists/[$ListId$]"
Method="GET">
<Params>
<Param Name="Filter" Hidden="True" Type="Property" Value="$.columns[?(@.displayName!='ID')]"
Options="All Fields=$.columns[?(@.displayName!='ID')];Lookup Fields=$.columns[?(@.lookup.listId || @.personOrGroup.chooseFromType)]" />
<Param Name="EnableCustomReplace" Type="Property" Value="True" />
<Param Name="SearchFor" Type="Property" Value=":{}" />
<Param Name="ReplaceWith" Type="Property" Value=":{blank:1}" />
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="expand"
Label="Expand"
Type="Query"
Value="columns"
Hidden="True"
/>
</Params>
</EndPoint>
<!-- =======================
LIST DETAILS
============================ -->
<EndPoint Name="get_list" Label="Read List" Url="/sites/[$SiteId$]/lists/[$ListId$]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="displayName" DataType="DT_WSTR" Length="188" />
<Column Name="parentReference.siteId" Label="ParentReferenceSiteId" DataType="DT_WSTR" Length="388" />
<Column Name="list.contentTypesEnabled" Label="ListContentTypesEnabled" DataType="DT_BOOL" />
<Column Name="list.hidden" Label="IsListHidden" DataType="DT_BOOL" />
<Column Name="list.template" Label="ListTemplate" DataType="DT_WSTR" Length="88" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.id" Label="CreatedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="createdBy.user.email" Label="CreatedByUserEmail" DataType="DT_WSTR" Length="124" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
</OutputColumns>
</EndPoint>
<!-- =========================
LISTS ITEMS
============================== -->
<EndPoint Name="get_list_items"
Label="Read List Items"
Template="ExpandFieldsSupport"
Url="/sites/[$SiteId$]/lists/[$ListId$]/items?expand=[$expand$]"
Filter="$.value[*]"
Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="Filter"
Key="$filter"
Type="Query"
Options="None=;Equal Search=fields/SomeColumn eq 'SomeValue';Substring Search=substringof(fields/SomeField,'abc');Starts With=startswith(fields/SomeField, 'abc')"
Desc="To filter data on a field, you will first need to index it: https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0"
HelpLink="https://zappysys.com/links?url=https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http"
/>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200"/>
<Column Name="createdDateTime" Label="CreatedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="createdBy.user.id" Label="CreatedById" DataType="DT_GUID" />
<Column Name="createdBy.user.displayName" Label="CreatedByDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="lastModifiedDateTime" Label="ModifiedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="lastModifiedBy.user.id" Label="LastModifiedId" DataType="DT_GUID" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedDisplayName" DataType="DT_WSTR" Length="200"/>
<!-- DYNAMIC CUSTOM FIELDS -->
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
<Column Name="fields.[$parent.name$]LookupId" Label="[$parent.name$]LookupId" Desc="[$parent.name$] Id" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.lookup.listId || @.personOrGroup.chooseFromType)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].Label" Label="[$parent.name$]Label" Desc="[$parent.name$] Id" DataType="DT_WSTR" Length="500"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].TermGuid" Label="[$parent.name$]TermGuid" Desc="Managed Meta [$parent.name$] TermGuid" DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].WssId" Label="[$parent.name$]WssId" Desc="Managed Meta [$parent.name$] WssId" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
</OutputColumns>
</EndPoint>
<EndPoint Name="get_list_items_dynamic" Template="ExpandFieldsSupport" Label="Read List Items (Dynamic Columns)" Url="/sites/[$SiteId$]/lists/[$ListId$]/items?expand=[$expand$]" IncludeParentColumns="True" Desc="Use this endpoint if you face issue with NULl values in some columns. This endpoint scans metadata from few sample rows and decides datatypes. Use this if other endpoing showing null values for Complex datatypes (e.g. Lookup, Location, Person)" Filter="$.value[*].fields" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="Filter"
Key="$filter"
Type="Query"
Options="None=;Equal Search=fields/SomeColumn eq 'SomeValue';Substring Search=substringof(fields/SomeField,'abc');Starts With=startswith(fields/SomeField, 'abc')"
Desc="To filter data on a field, you will first need to index it: https://support.microsoft.com/en-us/office/add-an-index-to-a-list-or-library-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0"
HelpLink="https://zappysys.com/links?url=https://learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http"
/>
</Params>
</EndPoint>
<EndPoint Name="get_list_item" Template="ExpandFieldsSupport" Label="Read List Item (By ID)" Url="/sites/[$SiteId$]/lists/[$ListId$]/items/[$ListItemId$]?expand=[$expand$]" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="ListItemId"
Required="True"
Type="Placeholder"
Desc="Specify a list item id (Record ID)"
IsKey="True" ReadAs="Id"
/>
<Param Name="ContineOn404Error" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200"/>
<Column Name="createdDateTime" Label="CreatedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="createdBy.user.id" Label="CreatedById" DataType="DT_GUID" />
<Column Name="createdBy.user.displayName" Label="CreatedByDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="lastModifiedDateTime" Label="ModifiedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="lastModifiedBy.user.id" Label="LastModifiedId" DataType="DT_GUID" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedDisplayName" DataType="DT_WSTR" Length="200"/>
<!-- DYNAMIC CUSTOM FIELDS -->
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
<Column Name="fields.[$parent.name$]LookupId" Label="[$parent.name$]LookupId" Desc="[$parent.name$] Id" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.lookup.listId || @.personOrGroup.chooseFromType)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].Label" Label="[$parent.name$]Label" Desc="[$parent.name$] Id" DataType="DT_WSTR" Length="500"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].TermGuid" Label="[$parent.name$]TermGuid" Desc="Managed Meta [$parent.name$] TermGuid" DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
<Column Name="fields.[$parent.name$].WssId" Label="[$parent.name$]WssId" Desc="Managed Meta [$parent.name$] WssId" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.defaultValue.value)]"
ColumnInfoMap="Name=name;"
/>
</OutputColumns>
</EndPoint>
<!-- CREATE -->
<EndPoint Name="create_list_item" Label="Create List Item" Url="/sites/[$SiteId$]/lists/[$ListId$]/items" Method="POST"
ContentType="application/json" DotAsPath="True" JsonRowFormat="Multicontent" Header="Expect:">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
</Params>
<Body>
<![CDATA[ {$rows$} ]]>
</Body>
<InputColumns>
<!-- DYNAMIC CUSTOM FIELDS -->
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false)]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
<!-- LOOKUP FIELDS -->
<Column Name="fields.[$parent.name$]LookupId" Label="[$parent.name$]LookupId" Desc="[$parent.name$] Id" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.lookup.listId || @.personOrGroup.chooseFromType)]"
ColumnInfoMap="Name=name;"
/>
</InputColumns>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200"/>
<Column Name="createdDateTime" Label="CreatedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="createdBy.user.id" Label="CreatedById" DataType="DT_GUID" />
<Column Name="createdBy.user.displayName" Label="CreatedByDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="lastModifiedDateTime" Label="ModifiedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="lastModifiedBy.user.id" Label="LastModifiedId" DataType="DT_GUID" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
</OutputColumns>
</EndPoint>
<!-- UPDATE -->
<EndPoint Name="update_list_item" Label="Update List Item" Url="/sites/[$SiteId$]/lists/[$ListId$]/items/[$ListItemId$]" Method="PATCH"
ContentType="application/json" DotAsPath="True" JsonRowFormat="Multicontent">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="ListItemId"
Required="True"
Type="Placeholder"
Desc="Specify a list item id"
/>
</Params>
<Body>
<![CDATA[ {$rows$} ]]>
</Body>
<InputColumns>
<Column Name="ListItemId" Label="Id" DataType="DT_WSTR" Length="200" MapToParam="True" Key="True" />
<!-- DYNAMIC CUSTOM FIELDS -->
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false)]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
<!-- LOOKUP FIELDS -->
<Column Name="fields.[$parent.name$]LookupId" Label="[$parent.name$]LookupId" Desc="[$parent.name$] Id" DataType="DT_I4"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.lookup.listId || @.personOrGroup.chooseFromType)]"
ColumnInfoMap="Name=name;"
/>
</InputColumns>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200"/>
<Column Name="createdDateTime" Label="CreatedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="createdBy.user.id" Label="CreatedById" DataType="DT_GUID" />
<Column Name="createdBy.user.displayName" Label="CreatedByDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="lastModifiedDateTime" Label="ModifiedOn" DataType="DT_DBTIMESTAMP"/>
<Column Name="lastModifiedBy.user.id" Label="LastModifiedId" DataType="DT_GUID" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedDisplayName" DataType="DT_WSTR" Length="200"/>
<Column Name="fields.[$parent.name$]" Label="[$parent.name$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$]"
ColumnInfoMap="Name=name;DataType=|string=text.maxLength,text.allowMultipleLines,choice.choices|number=number.decimalPlaces,number.displayAs|bool=boolean.blank|datetime=dateTime.format"
DataTypeMap="DT_I4=number;DT_WSTR=string;DT_DBTIMESTAMP=datetime;DT_BOOL=bool"
/>
</OutputColumns>
</EndPoint>
<!-- DELETE -->
<EndPoint Name="delete_list_item" Label="Delete List Item" Url="/sites/[$SiteId$]/lists/[$ListId$]/items/[$ListItemId$]" Method="DELETE">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True"
OptionsEndPoint="list_lists"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a list id to search items for"
/>
<Param Name="ListItemId"
Required="True"
Type="Placeholder"
Desc="Specify a list item id"
/>
</Params>
<InputColumns>
<Column Name="ListItemId" Label="Id" DataType="DT_WSTR" Length="200" MapToParam="True" Key="True" />
</InputColumns>
<OutputColumns>
<Column Name="Output" Label="Output" DataType="DT_WSTR" Length="2000" />
</OutputColumns>
</EndPoint>
<!-- ======================
EXCEL WORKSHEETS
======================= -->
<EndPoint Name="list_excel_worksheets" Label="List Excel Worksheets (Tabs)" Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets" Method="GET" Filter="$.value[*].name">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id to search items in"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a drive Id to search items in"
/>
</Params>
<OutputColumns>
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="120" />
</OutputColumns>
</EndPoint>
<EndPoint Name="get_excel_worksheet_autodetect" Label="Read Excel Worksheet (Auto Detect Range)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets('[$SheetId$]')/usedRange(valuesOnly=[$AutoDetectByValue$])" Method="GET" Filter="$.values[*]"
Desc="Use this endpoint to read data from a Sheet without supplying address range (e.g. A1:D100). This endpoint auto detects address range based on used cells in a sheet."
HelpLink="https://learn.microsoft.com/en-us/graph/api/worksheet-usedrange?view=graph-rest-1.0"
>
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
/>
<Param Name="SheetId"
Required="True"
OptionsEndPoint="list_excel_worksheets"
OptionsEndPointValueColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];FileId=[$FileId$]"
Type="Placeholder"
Desc="Specify a sheet name"
/>
<Param Name="AutoDetectByValue"
Desc="Auto detect cell range based on values only and do not consider cell formatting (for empty values) to detect the range."
Value="true"
Options="true;false"
/>
<Param Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" />
<Param Name="ArrayTransEnableCustomColumns" Label="First Row Has Column Names" Required="True" Type="Property" Value="True" Desc="Detect column names based on first row in supplied range." />
</Params>
</EndPoint>
<EndPoint Name="get_excel_worksheet" Label="Read Excel Worksheet (Specific Range)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets('[$SheetId$]')/range(address='[$Range$]')"
Method="GET" Filter="$.values[*]"
Desc="Use this endpoint to read data from excel worksheets with address range e.g. A1:D100. If you want dynamic range or unsure about end row then use the other endpoint with Auto Detect feature"
HelpLink="https://learn.microsoft.com/en-us/graph/api/range-get?view=graph-rest-1.0"
>
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
/>
<Param Name="SheetId"
Required="True"
OptionsEndPoint="list_excel_worksheets"
OptionsEndPointValueColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];FileId=[$FileId$]"
Type="Placeholder"
Desc="Specify a sheet name"
/>
<Param Name="Range" Type="Placeholder" Required="True" Value="A1:J100" />
<Param Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" />
<Param Name="ArrayTransEnableCustomColumns" Label="First Row Has Column Names" Required="True" Type="Property" Value="True" Desc="Detect column names based on first row in supplied range." />
</Params>
</EndPoint>
<!-- ======================
DOWNLOAD FILE CONTENTS
=========================== -->
<EndPoint Name="download_file" Label="Download File as a column field" Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]/content" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
/>
<Param Name="SaveContentAsBinary"
Required="True"
Type="Property"
Hidden="True"
Value="True"
/>
</Params>
<OutputColumns>
<Column Name="data" Label="Data" Desc="Use [Export Column] SSIS component to save file contents to disk" DataType="DT_IMAGE" />
</OutputColumns>
</EndPoint>
<!-- ======================
DOWNLOAD FILE to DISK
=========================== -->
<EndPoint Name="download_file_to_disk" Label="Download File to disk" Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]/content" Method="GET">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
/>
<Param Name="SaveContentAsBinary"
Required="True"
Type="Property"
Hidden="True"
Value="True"
/>
<Param Name="RequestTimeoutMs"
Type="Property"
Desc="Timeout in milliseconds after which download stops"
Value="7200000"
/>
<Param Name="FileOverwriteMode"
Type="Property"
Value="AlwaysOverwrite"
Required="True"
/>
<Param Key="ResponseDataFile"
Name="TargetFilePath"
Desc="Specify a disk file path to save file as"
Required="True"
Type="Property"
Value=""
Editor="FileSave"
/>
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Downloaded'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
<!-- ======================
DELETE FILE
=========================== -->
<EndPoint Name="delete_file" Label="Delete File from Sharepoint Site" Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/[$FileId$]" Method="DELETE">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
IsKey="True"
/>
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Deleted'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
<!-- ===========================
UPLOAD A FILE
=========================== -->
<EndPoint Name="upload_file" Label="Upload File"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/root:/[$SharePointFilePath$]:/createUploadSession"
Method="POST"
HelpLink="https://learn.microsoft.com/en-us/graph/api/driveitem-createuploadsession">
<Params>
<Param Name="SiteId"
Required="False"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionsEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="DiskFilePath"
Required="True"
Type="Placeholder"
Desc="Specify a disk file path"
Example="C:\MyParentFolder\Subfolder\file.txt"
Value=""
Editor="FileOpen"
/>
<Param Name="SharePointFilePath"
Required="True"
Type="Placeholder"
Desc="Specify target file path"
Example="MyParentFolder/Subfolder/file.txt"
Value=""
/>
</Params>
<EndPoint Name="upload_part"
Url="[$parent.[0].uploadUrl$]"
Method="PUT" Body="[$DiskFilePath$]">
<Params>
<Param Name="EnableSplitStreamForUpload" Type="Property" Value="True" Target="upload_part" Required="True" Hidden="True" Desc="Must be set to 'True'" />
<Param Name="BytesPerSplit" Type="Property" Value="33554432" Hidden="True" />
<Param Name="RequestTimeoutMs"
Type="Property"
Desc="Timeout in milliseconds after which upload stops"
Value="7200000"
/>
</Params>
</EndPoint>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="150" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="size" Label="Size" DataType="DT_I8" />
<Column Name="parentReference.path" Label="Path" DataType="DT_WSTR" Length="1000" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="500" />
<Column Name="parentReference.driveId" Label="DriveId" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.driveType" Label="DriveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="ParentRefId" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.name" Label="ParentRefName" DataType="DT_WSTR" Length="100" />
<Column Name="createdBy.user.id" Label="CreatedBy" DataType="DT_WSTR" Length="100" />
<Column Name="createdDateTime" Label="CreatedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="file.mimeType" Label="MimeType" DataType="DT_WSTR" Length="100" />
<Column Name="file.hashes.quickXorHash" Label="HashQuickXor" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha1Hash" Label="HashSha1" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha256Hash" Label="HashSha256" DataType="DT_WSTR" Length="200" />
</OutputColumns>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Sites" SelectEndPoint="list_sites" />
<Table Name="Lists" SelectEndPoint="list_lists" />
<Table Name="SystemLists" SelectEndPoint="list_system_lists" />
<Table Name="ListItems" SelectEndPoint="get_list_items"
LookupEndPoint="get_list_item"
UpdateEndPoint="update_list_item"
DeleteEndPoint="delete_list_item"
InsertEndPoint="create_list_item"
/>
<Table Name="Drives" SelectEndPoint="list_drives" />
<Table Name="[$parent.name$]"
Expand="True"
SelectEndPoint="get_list_items"
LookupEndPoint="get_list_item"
UpdateEndPoint="update_list_item"
DeleteEndPoint="delete_list_item"
InsertEndPoint="create_list_item"
DataEndPoint="list_lists" >
<Params>
<Param Name="SiteId" Value="" Hidden="True"/>
<Param Name="ListId" Value="[$parent.id$]" Hidden="True"/>
<Param Name="ListItemId" Value="" Hidden="True" IsKey="True" ReadAs="Id"/>
</Params>
</Table>
</Tables>
<Examples>
<Example Default="True" Group="ODBC" Label="Select lists from Lists table">
<Code>
<![CDATA[SELECT * FROM Lists]]>
</Code>
</Example>
<Example Group="ODBC" Label="Select data from a named list">
<Code>
<![CDATA[SELECT * FROM [Type here your list name] --Where Id=1234]]>
</Code>
</Example>
<Example Group="ODBC" Label="Insert data into a named list">
<Code>
<
VALUES ('John', 'Doe') WITH (Output=1)]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update data in a named list">
<Code>
<![CDATA[UPDATE [Type here your list name]
SET Name = 'Elizabeth', Surname = 'Johnson'
WITH (ListItemId='887',Output=1)
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyEmployeesList'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Delete item from a named list">
<Code>
<![CDATA[DELETE FROM [Type here your list name] WHERE Id=888
--OR--
DELETE FROM [Type here your list name] WITH (ListItemId='888')
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyEmployeesList'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Main site" Code="SELECT * FROM get_main_site" Desc="Gets main SharePoint site details" ></Example>
<Example Group="ODBC" Label="List sites" Code="SELECT * FROM Sites" Desc="Lists all available SharePoint sites" ></Example>
<Example Group="ODBC" Label="List drives" Code="SELECT * FROM Drives" Desc="Lists all drives"></Example>
<Example Group="ODBC" Label="Get drive">
<Code>
<![CDATA[SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List folders">
<Code>
<![CDATA[SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List files">
<Code>
<![CDATA[SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List Excel files">
<Code>
<![CDATA[SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get list fields">
<Code>
<![CDATA[SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get list items">
<Code>
<![CDATA[SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get list items (dynamic metadata)" Desc="Gets items of a specific list using dynamic metadata approach. Use this if other endpoint is showing null values for complex datatypes (e.g. Lookup, Location, Person)">
<Code>
<![CDATA[SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Create list item">
<Code>
<![CDATA[INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update list item">
<Code>
<![CDATA[UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
,ListItemId='775'
,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Delete list item">
<Code>
<![CDATA[DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List Excel file worksheets">
<Code>
<![CDATA[SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Excel Worksheet data - Auto Detect Range" Desc="Reads an Excel file without supplying Cell Address Range (It auto detects based on first and last cell)">
<Code>
<![CDATA[SELECT * FROM get_excel_worksheet_autodetect
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,AutoDetectByValue='true'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Excel Worksheet data for a specified Cell Address Range" Desc="Reads an Excel file from a specified Cell Range. To automatically read without cell range use other endpoint get_excel_worksheet_autodetect">
<Code>
<![CDATA[SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,Range='A1:K10000'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Upload a file to a SharePoint site">
<Code>
<![CDATA[SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='Uploads/Employees/List.xls')
--DriveId can be retrieved by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Download a file to a local disk from a SharePoint site">
<Code>
<![CDATA[SELECT * FROM download_file_to_disk
WITH(
DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
, FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
, TargetFilePath='C:\temp\List.xlsx'
--, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read a file content as BASE64 (Binary to string)" Desc="Read file content as base64 string (Binary to Base64) and also save Base64 to disk to recreate file.">
<Code>
<![CDATA[SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Get full image path from Image type field" Desc="By default Image field in your List will show JSON document which contains many pieces of your Image field. To get full path of your Image file you can use JSON_VALUE function and extract multiple fields and concat together to get full path like below.">
<Code>
<![CDATA[SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
FROM "My SharePoint List Name"]]>
</Code>
</Example>
</Examples>
</ApiConfig>
