SharePoint Online Connector - Source Code
<?xml version="1.0" encoding="utf-8"?>
<ApiConfig Name="SharePoint Online"
Slug="sharepoint-connector"
Id="9B0C106F-1FE2-4EC2-A103-C20E0885F3F1"
EngineVersion="17"
Version="17"
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 Ver="17" Date="2025-01-30" Type="New">Sorted Files and Folders in Dropdown selection on UI, also included full path rather than name.</Change>
<Change Ver="17" Date="2025-01-30" Type="New">Added webUrlPath column in many endpoints, which list Files and folders. This path included a relative path after /Documents/ part in webUrl</Change>
<Change Ver="17" Date="2025-01-30" Type="New">Reduced Folder selection dropdown limit to 200 items to load UI faster.</Change>
<Change Ver="17" Date="2025-01-30" Type="New">Added new endpoint copy_item to copy file or folder.</Change>
<Change Ver="17" Date="2025-01-30" Type="New">Added new endpoint move_rename_item to move or rename file or folder.</Change>
<Change Ver="16" Date="2024-01-24" Type="New">Added granting SharePoint permissions to app using PowerShell in authentication instructions.</Change>
<Change Ver="16" Date="2024-12-19" Type="Fix">Default search type (recursive) fails in Application Credentials, now non-recursive is enforced if its Application credentials.</Change>
<Change Ver="16" Date="2024-12-19" Type="New">Added a default value and a new option to Redirect URL parameter in User Credentials authentication (https://zappysys.com/oauth).</Change>
<Change Ver="16" Date="2024-12-19" Type="Modified">Updated authentication instructions with explanatory images.</Change>
<Change Ver="16" Date="2024-12-19" Type="Fix">Made Redirect URL parameter required in User Credentials authentication (https://zappysys.com/oauth).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">(*.xls) Legacy Excel Format (Office 97-2003) not listed in various excel endpoints e.g. list_excel_worksheets / get_excel_worksheet_autodetect.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Show additional columns for list_excel_worksheets (new columns are ID, Position, Visible).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Use ID rather than Name for SheetId parameter in get_excel_worksheet and get_excel_worksheet_autodetect endpoints to allow to rename of tab with not getting error.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new parameter ContinueOn404Error in many endpoints to allow 404 file not found error.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new tables FileReaderWriterCsv / FileReaderWriterJson to support CSV / JSON data Read/Write scenarios.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new endpoints create_csv_file, create_json_file to write CSV and JSON file data using rows / columns mapping.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new endpoints get_csv_file, get_json_file to read CSV and JSON file data using rows/columns mapping (without downloading to local disk).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added retry on error code 423 (File is locked).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new option (UseSourceFileTimeStamp) to set Created/Modified on Uploaded file same as Source File in upload_file endpoint. By default, Upload time is used to set Creation/Modified date on target file after upload is done.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new option (ConflictBehavior) in upload_file endpoint to replace/rename if target file exists.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new connection level options (ExtraHeaders and SearchOptionForNonIndexedFields).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new SearchPath option for list_files, list_folders, list_excel_files endpoints.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new SearchType option with Recursive and Non-Recursive scan modes for list_files, list_folders, list_excel_files endpoints.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a new OrderBy parameter for list_files, list_folders, list_excel_files endpoints.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a ODataType fields to allow Update / Insert for Multi Select Choice, Lookup, PersonOrGroup Field types. When you set multi value choice set MyFieldNameODataType along with Value. Example: [MyMultiChoiceFieldOdataType]='Collection(Edm.String)'. For Lookup/PersonOrGroup Edm type must be Edm.Int32 and for Choice must be Edm.String. Other values not allowed in this field.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added Insert / Update option for Multi Select Choice Field (Previously not possible) - Must set MyMultiChoiceField='["AA","BB"]' and [MyMultiChoiceFieldOdataType]='Collection(Edm.String)' both fields.</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a Default Drive Id parameter at connection level, Updated Auth SiteId Param to have extra option entry (root).</Change>
<Change Ver="15" Date="2024-10-22" Type="New">Added a UseRawCache option in get_list_fields to avoid duplicate API calls and fetch Columns metadata faster.</Change>
<Change Date="2024-04-18" Type="New">Dynamic columns of SharePoint Lookup fields in "insert_list_item" and "update_list_item" endpoints are now RAW.</Change>
<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 doesn't 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="Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access.
Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox,
all scenarios involving user actions should use delegated access."
HelpLink="https://learn.microsoft.com/en-us/entra/identity-platform/delegated-access-primer"
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"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2" />
<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"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2" />
<Param Name="ClientId" Label="Client ID" Required="True" HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True"/>
<Param Name="Scope"
Required="True"
Options="offline_access;Sites.Read;Sites.Read.All;Sites.ReadWrite;Sites.ReadWrite.All;Sites.FullControl;Sites.FullControl.All;Files.Read;Files.Read.All;Files.ReadWrite;Files.ReadWrite.All;openid;profile;User.Read"
MultiSelectSeparator=" "
MultiSelect="True"
Value="offline_access~Files.ReadWrite~Files.ReadWrite.All~openid~profile~Sites.ReadWrite~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="https://learn.microsoft.com/en-us/graph/permissions-reference"/>
<Param Name="ReturnUrl" Required="True" Label="Return URL" Value="https://zappysys.com/oauth" Options="https://zappysys.com/oauth"/>
<Param Name="SiteId"
Label="Default Site Id (select after pressing 'Generate Token')"
Required="True"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsExtra="Root Site=root"
Type="Placeholder"
Desc="Specify a default site you like to use for operations. You can always override this for various operations. Selection of this parameter needs at minimum Sites.Read.All permission otherwise it will fail to list all sites. If it fails, please enter SiteId manually. You can find SiteId by visiting this URL in browser (assuming you visted SharePoint site and already logged in) https://{your-company}.sharepoint.com/_api/site --OR-- for sub-site use https://{your-company}.sharepoint.com/sites/{your-site}/_api/site. Find Id from the response (e.g. <Id m:type="Edm.Guid">)."
Value="root"
/>
<Param Name="DriveId"
Label="Default Drive Id (select after pressing 'Generate Token')"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a default Drive Id you like to use for operations."
/>
<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|423" Hidden="True" Desc="429 is API limit reached, 423 is File locked"/>
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
<Param Name="SearchOptionForNonIndexedFields" AllowDuplicateHeader="True" Type="Header" Label ="Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)" Key="Prefer" Options="Search Only Indexed=;Search Both Indexed and Non-Indexed=HonorNonIndexedQueriesWarningMayFailRandomly" Desc="If you wish to do certain operations e.g. search / order by on non-indexed fields then you have to set this option to HonorNonIndexedQueriesWarningMayFailRandomly. By default filter / orderby on non-indexed fields not allowed."/>
<Param Name="ExtraHeaders" Label="Extra Headers (e.g. Header1:AAA||Header2:BBB)" Options="MyHeader1:AAA;MyHeader1:AAA||MyHeader2:BBB"/>
<Param Name="IsAppCred" Value="0" Hidden="True" Desc="For internal use only" DefaultValue="0"/>
</Params>
<Notes>
<![CDATA[
<p>Follow these simple steps below to create Microsoft Entra ID application with delegated access:<p/>
<div class="alert alert-warning">
<strong>WARNING</strong>:
If you are planning to automate processes, we recommend that you use a <em>Application Credentials</em> authentication method.
In case, you still need to use <em>User Credentials</em>, then make sure you use a system/generic account (e.g. <code>automation@my-company.com</code>).
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 start to fail.
</div>
<ol>
<li>
Navigate to the <a target="_blank" href="https://portal.azure.com/#home">Azure Portal</a> and log in using your credentials.
</li>
<li>
Access <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview">Microsoft Entra ID</a>.
</li>
<li>
<p>
Register a new application by going to
<a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a>
and clicking on <strong>New registration</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/start-new-app-registration-in-microsoft-entra-id.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start new app registration in Microsoft Entra ID"
title="Starting new app registration in Microsoft Entra ID"
width="1000"
height="490" />
<div class="alert alert-info">
<strong>INFO:</strong>
Find more information on how to register an application in <a target="_blank" href="https://docs.microsoft.com/en-us/graph/auth-register-app-v2">Graph API reference</a>.
</div>
</li>
<li>
<p>
When configuration window opens, configure these fields:
</p>
<ul>
<li>
<strong>Supported account type</strong>
<ul>
<li>Use <code>Accounts in this organizational directory only</code>, if you need access to data in your organization only.</li>
</ul>
</li>
</li>
<li>
<strong>Redirect URI</strong>:
<ul>
<li>Set the type to <code>Public client/native (mobile & desktop)</code>.</li>
<li>Use <code>https://zappysys.com/oauth</code> as the URL.</li>
</ul>
</li>
</ul>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/register-app-in-microsoft-entra-id.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Register app in Microsoft Entra ID"
title="Registering app in Microsoft Entra ID"
width="1000"
height="840" />
</li>
<li>
<p>
After registering the app, copy the <strong>Application (client) ID</strong> for later:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/copy-client-id-of-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy client ID of Microsoft Entra ID app"
title="Copying client ID of Microsoft Entra ID app"
width="1000"
height="560" />
</li>
<li>
<p>
Copy <strong>OAuth authorization endpoint (v2)</strong> & <strong>OAuth token endpoint (v2) URLs</strong> to use later in the configuration:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/copy-auth-and-token-urls-in-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy Auth and Token URLs in Microsoft Entra ID app"
title="Copying Auth and Token URLs in Microsoft Entra ID app"
width="1000"
height="560" />
</li>
<li>
<p>
Now go to SSIS package or ODBC data source and use the copied values in <strong>User Credentials</strong> authentication configuration:
</p>
<ul>
<li>In the <strong>Authorization URL</strong> field paste the <strong>OAuth authorization endpoint (v2) URL</strong> value you copied in the previous step.</li>
<li>In the <strong>Token URL</strong> field paste the <strong>OAuth token endpoint (v2) URL</strong> value you copied in the previous step.</li>
<li>In the <strong>Client ID</strong> field paste the <strong>Application (client) ID</strong> value you copied in the previous step.</li>
<li>
In the <strong>Scope</strong> field use the default value or select individual scopes, e.g.:
<ul>
<li>
<code>email</code>
</li>
<li>
<code>offline_access</code>
</li>
<li>
<code>openid</code>
</li>
<li>
<code>profile</code>
</li>
<li>
<code>User.Read</code>
</li>
<li>
<code>Sites.Read.All</code>
</li>
<li>
<code>Sites.ReadWrite.All</code>
</li>
<li>
<code>Files.Read.All</code>
</li>
<li>
<code>Files.ReadWrite.All</code>
</li>
</ul>
</li>
</ul>
</li>
<li>
Press <strong>Generate Token</strong> button to generate Access and Refresh Tokens.
</li>
<li>
<em>Optional step</em>. Choose <strong>Default Site Id</strong> from the drop down menu.
</li>
<li>
Click <strong>Test Connection</strong> to confirm the connection is working.
</li>
<li>
Done! Now you are ready to use the API Connector!
</li>
</ol>
]]>
</Notes>
</Auth>
<Auth Type="OAuth" Name="AppCreds" Label="Application Credentials"
Desc="Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed.
Use it when: 1. The application needs to run in an automated way, without user input
(for example, a daily script that checks emails from certain contacts and sends automated responses).
2. The application needs to access resources belonging to multiple different users
(for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants).
3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin."
HelpLink="https://learn.microsoft.com/en-us/entra/identity-platform/app-only-access-primer"
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" HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2" />
<Param Name="ClientId" Label="Client ID" Required="True" HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2" />
<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://learn.microsoft.com/en-us/graph/permissions-reference" />
<Param Name="SiteId"
Label="Default Site Id"
Required="True"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsExtra="Root Site=root"
Type="Placeholder"
Desc="Specify a site"
Value="root"
/>
<Param Name="DriveId"
Label="Default Drive Id"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a default Drive Id you like to use for operations."
/>
<!--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|423" Hidden="True" Desc="429 is API limit reached, 423 is File locked"/>
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
<Param Name="SearchOptionForNonIndexedFields" Type="Header" AllowDuplicateHeader="True" Label ="Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)" Key="Prefer" Options="Search Only Indexed=;Search Both Indexed and Non-Indexed=HonorNonIndexedQueriesWarningMayFailRandomly" Desc="If you wish to do certain operations e.g. search / order by on non-indexed fields then you have to set this option to HonorNonIndexedQueriesWarningMayFailRandomly. By default filter / orderby on non-indexed fields not allowed."/>
<Param Name="ExtraHeaders" Label="Extra Headers (e.g. Header1:AAA||Header2:BBB)" Options="MyHeader1:AAA;MyHeader1:AAA||MyHeader2:BBB"/>
<Param Name="IsAppCred" Value="1" Hidden="True" Desc="For internal use only" DefaultValue="1"/>
</Params>
<Notes>
<![CDATA[
<p>
Follow these simple steps to create Microsoft Entra ID application with application access permissions:
<p/>
<ul>
<li>
<p>
Create an OAuth app
</p>
</li>
<li>
<p>
Grant application SharePoint Online permissions (optional, for granular permissions)
</p>
<div class="alert alert-info alert-title">
This step allows to grant OAuth application granular permissions,
i.e. access configured <em>specific</em> <code>Sites</code>, <code>Lists</code>, and <code>List Items</code>.
</p>
</li>
</ul>
<h3>
Step-1: Create OAuth app
</h3>
<ol>
<li>
Navigate to the <a href="https://portal.azure.com/#home" target="_blank">Azure Portal</a> and log in using your credentials.
</li>
<li>
Access <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview">Microsoft Entra ID</a>.
</li>
<li>
Register a new application by going to
<a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a>
and clicking on <strong>New registration</strong> button:
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/start-new-app-registration-in-microsoft-entra-id.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start new app registration in Microsoft Entra ID"
title="Starting new app registration in Microsoft Entra ID"
width="1000"
height="490" />
<div class="alert alert-info">
<strong>INFO:</strong>
Find more information on how to register an application in <a target="_blank" href="https://docs.microsoft.com/en-us/graph/auth-register-app-v2">Graph API reference</a>.
</div>
</li>
<li>
<p>
When configuration window opens, configure these fields:
</p>
<ul>
<li>
<strong>Supported account type</strong>
<ul>
<li>e.g. select <code>Accounts in this organizational directory only</code> if you need access to data in your organization only.</li>
</ul>
</li>
</li>
<li>
<strong>Redirect URI</strong>:
<ul>
<li>Set the type to <code>Public client/native (mobile & desktop)</code>.</li>
<li>Leave the URL field empty.</li>
</ul>
</li>
</ul>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/register-app-in-microsoft-entra-id.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Register app in Microsoft Entra ID"
title="Registering app in Microsoft Entra ID"
width="1000"
height="840" />
</li>
<li>
<p>
After registering the app, copy the <strong>Application (client) ID</strong> for later:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/copy-client-id-of-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy client ID of Microsoft Entra ID app"
title="Copying client ID of Microsoft Entra ID app"
width="1000"
height="560" />
</li>
<li>
<p>
Then copy <strong>OAuth authorization endpoint (v2)</strong> & <strong>OAuth token endpoint (v2) URLs</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/copy-auth-and-token-urls-in-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy Auth and Token URLs in Microsoft Entra ID app"
title="Copying Auth and Token URLs in Microsoft Entra ID app"
width="1000"
height="560" />
</li>
<li>
<p>
Continue and create <strong>Client secret</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/app-credentials/add-client-secret-for-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Add Client secret for Microsoft Entra ID app"
title="Adding Client secret for Microsoft Entra ID app"
width="1000"
height="560" />
</li>
<li>
<p>
Then copy the <strong>Client secret</strong> for later steps:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/app-credentials/copy-client-secret-of-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Copy Client secret"
title="Copy Client secret"
width="1000"
height="560" />
</li>
<li>
<p>
Continue by adding permissions for the app by going to the <strong>API permissions</strong> section, and clicking on <strong>Add a permission</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/start-adding-permissions-to-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Start adding permissions to Microsoft Entra ID app"
title="Starting adding permissions to Microsoft Entra ID app"
width="1000"
height="520" />
</li>
<li>
<p>
Select <strong>Microsoft Graph</strong>:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/select-graph-api-permissions-for-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select Graph API permissions for Microsoft Entra ID app"
title="Selecting Graph API permissions for Microsoft Entra ID app"
width="1000"
height="420" />
</li>
<li>
<p>
Then choose <strong>Application permissions</strong> option:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/App-Credentials/select-app-permissions-for-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select app permissions for Microsoft Entra ID app"
title="Selecting app permissions for Microsoft Entra ID app"
width="1000"
height="320" />
</li>
<li>
<p>
Continue by adding these <strong>Sites</strong> permissions (search for <code>site</code>):
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/App-Credentials/select-sharepoint-scopes.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select SharePoint Online application scopes"
title="Selecting SharePoint Online application scopes"
width="850"
height="430" />
<div class="alert alert-info">
<strong>INFO:</strong>
If you want to access specific lists or list items (table-level vs row-level security) rather than the full site,
then add <code>Lists.SelectedOperations.Selected</code> or <code>ListItems.SelectedOperations.Selected</code> permissions ,
just like in the previous step (search for <code>list</code>).
</div>
<div class="alert alert-warning">
<strong>WARNING:</strong>
If you add any of these permissions
- <code>Sites.Selected</code>, <code>Lists.SelectedOperations.Selected</code>, or <code>ListItems.SelectedOperations.Selected</code> -
you must grant the app the SharePoint permissions for the specific resource (e.g. a <code>Site</code>, a <code>List</code>, or a <code>ListItem</code>).
Follow instructions in <em>Grant SharePoint permissions to the OAuth app (optional)</em> section on how to accomplish that.
</div>
</li>
<li>
<p>
Finish by clicking <strong>Add permissions</strong> button:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/add-permissions-to-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Add permissions to Microsoft Entra ID app"
title="Adding permissions to Microsoft Entra ID app"
width="270"
height="70" />
</li>
<li>
<p>
Now it's time to <strong>Grant admin consent</strong> for your application:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/App-Credentials/grant-admin-consent-for-microsoft-entra-id-app.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Grant admin consent for Microsoft Entra ID app"
title="Granting admin consent for Microsoft Entra ID app"
width="1000"
height="590" />
</li>
<li>
<p>
Confirm all the permissions are granted:
</p>
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/App-Credentials/admin-consent-granted-successfully-in-entra-id.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Admin consent granted successfully in Entra ID"
title="Admin consent granted successfully in Entra ID"
width="1000"
height="260" />
</li>
<li>
<p>
Now go to SSIS package or ODBC data source and use the copied values in <strong>Application Credentials</strong> authentication configuration:
</p>
<ul>
<li>In the <strong>Token URL</strong> field paste the <strong>OAuth token endpoint (v2) URL</strong> value you copied in the previous step.</li>
<li>In the <strong>Client ID</strong> field paste the <strong>Application (client) ID</strong> value you copied in the previous step.</li>
<li>In the <strong>Client Secret</strong> field paste the <strong>Client secret</strong> value you copied in the previous step.</li>
<li><em>Optional step</em>. Choose <strong>Default Site Id</strong> from the drop down menu.</li>
</ul>
</li>
<li>
Click <strong>Test Connection</strong> to confirm the connection is working.
</li>
<li>
Done!
</li>
</ol>
<h3>
Step-2 (optional): Grant SharePoint permissions to the OAuth app (optional)
</h3>
<p>
If you used <code>Sites.Selected</code>, <code>Lists.SelectedOperations.Selected</code> or <code>ListItems.SelectedOperations.Selected</code> permission in the previous section,
you must grant the app the SharePoint permissions for the specific resource (e.g. a <code>Site</code>, a <code>List</code>, or a <code>ListItem</code>).
You can do it using <strong>PowerShell</strong> or <strong>SharePoint admin center</strong> (obsolete method).
</p>
<h4>
Granting SharePoint permissions using PowerShell
</h4>
<p>
Unfortunately, there is no user interface available to control these permissions yet.
For now, granting permissions has to be accomplished via Microsoft Graph API
<a target="_blank" href="https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=http" class="font10px-force">[Microsoft reference]</a>:
</p>
<div class="alert alert-warning alert-title">
You must be the owner of the resource to grant permissions
(i.e. belong to SharePoint owners group or be the owner of the <code>Site</code> or <code>List</code>).
</div>
<ol>
<li>
Open PowerShell (run as admin).
</li>
<li>
<p>
Call the following PowerShell code to grant <code>read</code> and <code>write</code> permission for the app we created earlier
(assuming <strong>Application (client) ID</strong> is <code>89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e</code>):
</p>
<pre><code>
# More info at:
# - https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
# - https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.sites/?view=graph-powershell-1.0
# Find SharePoint Site Id by following these steps:
# - Login into SharePoint Online
# - Open this URL https://{your-company}.sharepoint.com/_api/site in the browser
# NOTE: For a subsite use https://{your-company}.sharepoint.com/sites/{your-subsite}/_api/site
# - Find 'Id' element in the response (e.g. <d:Id m:type="Edm.Guid">efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3</d:Id>)
# - Copy the Site Id, i.e.: efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3
# Set $siteId variable to the retrieved Site Id:
$siteId="efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3"
# Find your Application Id (i.e. Client Id) in the Azure Portal, in App Registrations page:
# https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade
$applicationId="89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e"
# Available permissions: read, write, fullcontrol, owner ('write' includes 'read' permission)
$appPermission="write"
# Step-1: Install 'Microsoft.Graph.Sites' module if it's not installed
if (-not (Get-Module Microsoft.Graph.Sites -ListAvailable))
{
Install-Module Microsoft.Graph.Sites
}
# Step-2: Load module
Import-Module Microsoft.Graph.Sites
# Step-3: Login (use Azure admin or SharePoint owner account)
DisConnect-MgGraph
Connect-MgGraph
# Step-4: Set parameters for API call (set permissions, Site ID and Application ID)
$params = @{
roles = @($appPermission)
grantedTo = @{
application = @{id = $applicationId}
}
}
# Step-5: Grant permissions
New-MgSitePermission -SiteId $siteId -BodyParameter $params
# Done!
Write-Host "Granted SharePoint permissions to application '$applicationId' for site '$siteId'."
</code>
</pre>
</li>
<li>
That's it! Now you can use the API Connector!
</li>
</ol>
<h4>
Granting SharePoint permissions using SharePoint admin center (obsolete method)
</h4>
<p>
If you used <code>Site.Selected</code> permission you can link it SharePoint site in SharePoint admin center
<a target="_blank" href="https://learn.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs" class="font10px-force">[SharePoint reference]</a>.
Follow these simple steps to accomplish that:
</p>
<ol>
<li>
Log in to SharePoint admin center using this URL:
(replace <code>YOURCOMPANY</code> with your company name):
<pre>https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appinv.aspx</pre>
<div class="alert alert-info">
<strong>INFO:</strong>
To view all the registered apps in SharePoint, visit this page: <code>https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web</code>.
</div>
</li>
<li>
In the <strong>App Id</strong> field enter <strong>Application (client) ID</strong> you copied in the previous step.
</li>
<li>
<p>
In the <strong>Permission Request XML</strong> field enter XML snippet which describes which SharePoint permissions you want to grant to the OAuth app, e.g.:
</p>
<pre><code><AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests></code></pre>
<div class="alert alert-info">
<strong>INFO:</strong>
This example gives app <code>FullControl</code>, but you can also grant it <code>Read</code> or <code>Write</code> permissions.
</div>
<li>
Click <strong>Create</strong> to grant the permission to your OAuth app.
</li>
<li>
That's it! Now you can use the API Connector!
</li>
</ol>
]]>
</Notes>
</Auth>
</Auths>
<Template>
<EndPoint>
<Params>
<Param Name="DataFormat" Type="Property" Value="OData" />
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" />
</Params>
</EndPoint>
<Param Name="T_SiteIdParam"
Required="False" Label="Site Id (Re-Select Drive Id after you change this)"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"/>
<Param Name="T_DriveIdParam"
Label="Drive Id"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="SiteId=[$SiteId$]"
Type="Placeholder"
Desc="Specify a drive"
Order="1"
/>
<Param Name="T_TargetDriveIdParam"
Label="Target Drive Id (Enter if different than source DriveId)" Required="False"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="SiteId=[$TargetSiteId$]"
Type="Placeholder"
Desc="Specify a drive"
Order="1"
/>
<Param Name="T_FilterParam" Label ="Record Filter (Client Side)"
Type="Property"
Value="$.value[*]"
Required="true"
Options="All Files and Folders=$.value[*];
All Files=$.value[?(@.file.mimeType != null)];
All Folders=$.value[?(@.folder.childCount != null)];
All Files Under a Specific Folder ID=$.value[?(@.file.mimeType != null && @parentReference.id=='SOME-FOLDER-ID-GOES-HERE')];
All Excel Files (XLSX or XLS)=$.value[?(@file.mimeType != null && @name=~/(\.xlsx|\.xls)$/)];
File or Folder Name Equals=$.value[@name=='somename')];
File Name Equals=$.value[?(@.file.mimeType != null && @name=='somefile.txt')];
Folder Name Equals=$.value[?(@.folder.childCount != null && @name=='somefile.txt')];
File Id Equals=$.value[?(@.file.mimeType != null && @id=='SOME-FILE-ID-GOES-HERE')];
File Name Contains=$.value[?(@.file.mimeType != null && @name=~/somename$/)];
File Name With Regex Pattern=$.value[?(@.file.mimeType != null && @name=~/^somefile_\d\d\d.txt$/)];
File Name With Regex Pattern (Multiple)=$.value[?(@.file.mimeType != null && @name=~/somefile.txt|somefile.json/)]"
/>
<Param Name="T_SearchTypeParam" Label="Search Type (For UI Only - Default=Recursive - i.e. Blank)"
Options="Recursive All Files (Keep Blank) - not supported for Application Credentials with Sites.Selected Scope=;
Non-Recursive / Children All Files (Only for Non-Shared Items)=children;
Recursive *.xlsx Files - For User Creds Only=search(q='.xls');
Recursive *.csv Files - For User Creds Only=search(q='.csv');
Recursive *.json Files - For User Creds Only=search(q='.json');
Recursive Custom Extension Files - For User Creds Only=search(q='.myext')"
Desc="NOTE: Not Supported with Application Credentials which uses Sites.Selected Scope. If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property)."
ValueTemplate="<<[$IsAppCred$]|~|Eq|~|1|~|children|~|{$value$},FUN_IF>>"
Order="2" />
<Param Name="T_SearchPathParam" Label="Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed"
OptionsExtra="Example#1 (Recursive Scan - Under Root)=/root;
Example#2 (Recursive Scan - Under Subfolder Path)=/root:/folder/subfolder:;
Example#3 (Non-Recursive Scan - Under Folder Id)=/items/ABCD123-SOME-FOLDER-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)]"
Desc="This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder)."
Order="3"
MultiSelectLimit="200"
/>
<Param Name="T_ItemIdParam"
Label="File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed"
OptionsExtra="Example#1 (File/Folder Id)=01R65Qxxxxxxx123;
Example#2 (File Name)=root:/myfolder/file.xyz:;
Example#3 (Folder Name)=root:/myfolder/subfolder:"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[*];SearchPath=[$SearchPath$]"
Desc="Specify a File/Folder Id or Path. Syntax: [{item-id}] -OR- [root:/{item-path}:] (Example: root:/myfolder/myfile.xyz: -OR- root:/myfolder: )"
Required="True"
Type="Placeholder"
MultiSelectLimit="1000"
ValueTemplate="<<{$value$}|~|StartsWith|~|root:|~|{$value$}|~|items/{$value$},FUN_IF>>"
Order="4"
/>
<Param Name="T_FileIdParam"
Label="File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed"
OptionsExtra="Example#1 (File Id)=01R65Qxxxxxxx123;
Example#2 (File Name)=root:/myfile.xyz:;
Example#3 (File Name in child folder)=root:/subfolder/myfile.xyz:"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.file.mimeType != null)];SearchPath=[$SearchPath$]"
Desc="Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )"
Required="True"
Type="Placeholder"
MultiSelectLimit="1000"
ValueTemplate="<<{$value$}|~|StartsWith|~|root:|~|{$value$}|~|items/{$value$},FUN_IF>>"
Order="4"
/>
<Param Name="T_FileIdExcelParam"
Label="Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed"
OptionsExtra="Example#1 (File Id)=01R65Qxxxxxxx123;
Example#2 (File Name)=root:/myfile.xlsx:;
Example#3 (File Name in child folder)=root:/subfolder/myfile.xlsx:"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.file.mimeType != null)];SearchPath=[$SearchPath$]"
Desc="Specify a file Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xlsx: )"
Required="True"
Type="Placeholder"
MultiSelectLimit="200"
ValueTemplate="<<{$value$}|~|StartsWith|~|root:|~|{$value$}|~|items/{$value$},FUN_IF>>"
Order="4"
/>
<Param Name="T_SheetIdParam"
Required="True"
Label="Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 )"
OptionsEndPoint="list_excel_worksheets"
OptionsEndPointLabelColumn="Name"
OptionsEndPointValueColumn="Id"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];FileId=[$FileId$]"
Type="Placeholder"
Desc="Specify a sheet id or name (e.g. {00000000-0001-0000-0000-000000000000} -OR- Sheet1"
Order="5" />
<Param Name="T_TargetSearchTypeParam" Label="Target Search Type (For UI Only - Default=Recursive - i.e. Blank)"
Options="Recursive All Files (Keep Blank) - For User Creds Only=;
Non-Recursive / Children - All Files=children;"
Desc="If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property)."
ValueTemplate="<<[$IsAppCred$]|~|Eq|~|1|~|children|~|{$value$},FUN_IF>>"
Order="2"
InvokeOrder="1"
/>
<Param Name="T_TargetSearchPathParam" Label="Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed"
OptionsExtra="Example#1 (Recursive Scan - Under Root)=/root;
Example#2 (Recursive Scan - Under Subfolder Path)=/root:/folder/subfolder:;
Example#3 (Non-Recursive Scan - Under Folder Id)=/items/ABCD123-SOME-FOLDER-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$TargetSiteId$];SearchType=[$TargetSearchType$];DriveId=<<[$TargetDriveId$]|~|[$DriveId$]|~|[$TargetDriveId$],FUN_IF_EMPTY>>;Filter=$.value[?(@.folder.childCount != null)]"
Desc="This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder)."
Order="3"
MultiSelectLimit="200"
InvokeOrder="2"
/>
<Param Name="T_TargetFolderIdParam"
Label="Target Folder Id (e.g. 01R65Qxxxxxxx123 -OR- root) - Max 200 items listed"
OptionsExtra="Root Folder=root;Your Folder Id=01R65Qxxxxxxxxxxx123"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$TargetSiteId$];DriveId=<<[$TargetDriveId$]|~|[$DriveId$]|~|[$TargetDriveId$],FUN_IF_EMPTY>>;SearchType=[$TargetSearchType$];SearchPath=[$TargetSearchPath$];Filter=$.value[?(@.folder.childCount != null)]"
Desc="Specify a Folder Id (Example: 01R65Qxxxxxxx123 -OR- root)"
Type="Placeholder"
MultiSelectLimit="200"
Order="4"
/>
<EndPoint Name="ItemOutput">
<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="webUrl" Label="webUrlPath" DataType="DT_WSTR" Length="1500" ValueTemplate="<<{$value$}|~|/Documents(?<path>/.*){{0,path}},FUN_REGEX_EXTRACT>>" />
<Column Name="createdBy.user.id" Label="createdBy_user_id" DataType="DT_WSTR" Length="200" />
<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="createdBy.application.id" Label="createdBy_application_id" DataType="DT_WSTR" Length="200" />
<Column Name="createdBy.application.displayName" Label="createdBy_application_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="lastModifiedBy_user_id" DataType="DT_WSTR" Length="200" />
<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="lastModifiedBy.application.id" Label="lastModifiedBy_application_id" DataType="DT_WSTR" Length="200" />
<Column Name="lastModifiedBy.application.displayName" Label="lastModifiedBy_application_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<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.name" Label="parentReference_name" DataType="DT_WSTR" Length="200" Desc="This is only returned when calling lookup or /children call (e.g. /items/root:/[folder-name] OR /items/[item-id] )" />
<Column Name="parentReference.siteId" Label="parentReference_siteId" DataType="DT_WSTR" Length="200" />
<Column Name="parentReference.path" Label="parentReference_path" DataType="DT_WSTR" Length="1000" Desc="This is only returned when calling lookup or /children call (e.g. /items/root:/[folder-name] OR /items/[item-id] )" />
<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="file.hashes.quickXorHash" Label="file_quickXorHash" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha1Hash" Label="file_sha1Hash" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha256Hash" Label="file_sha256Hash" DataType="DT_WSTR" Length="200" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
<Column Name="remoteItem.parentReference.id" Label="remoteItem_parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.parentReference.driveId" Label="remoteItem_parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="remoteItem.parentReference.driveType" Label="remoteItem_parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="remoteItem.parentReference.name" Label="remoteItem_parentReference_name" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.parentReference.siteId" Label="remoteItem_parentReference_siteId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.parentReference.path" Label="remoteItem_parentReference_path" DataType="DT_WSTR" Length="1000" />
<Column Name="remoteItem.shared.scope" Label="remoteItem_shared_scope" DataType="DT_WSTR" Length="100" />
<Column Name="remoteItem.shared.sharedDateTime" Label="remoteItem_shared_sharedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="remoteItem.shared.sharedBy.user.id" Label="remoteItem_sharedBy_user_id" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.shared.sharedBy.user.email" Label="remoteItem_sharedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="remoteItem.shared.sharedBy.user.displayName" Label="remoteItem_sharedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="remoteItem.sharepointIds.listId" Label="remoteItem_sharepointIds_listId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.listItemId" Label="remoteItem_sharepointIds_listItemId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.listItemUniqueId" Label="remoteItem_sharepointIds_listItemUniqueId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.siteId" Label="remoteItem_sharepointIds_siteId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.siteUrl" Label="remoteItem_sharepointIds_siteUrl" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.tenantId" Label="remoteItem_sharepointIds_tenantId" DataType="DT_WSTR" Length="200" />
<Column Name="remoteItem.sharepointIds.webId" Label="remoteItem_sharepointIds_webId" DataType="DT_WSTR" Length="200" />
<Column Name="id" Label="id_path" DataType="DT_WSTR" Length="200" ValueTemplate="/items/{$value$}" />
</OutputColumns>
</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" Template="T_SiteIdParam" Label="Site Id" />
</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="owner.user.displayName" Label="OwnerDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="owner.user.email" Label="OwnerEmail" DataType="DT_WSTR" Length="200" />
<Column Name="owner.user.id" Label="OwnerId" DataType="DT_WSTR" Length="200" />
<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" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
</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="owner.user.displayName" Label="OwnerDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="owner.user.email" Label="OwnerEmail" DataType="DT_WSTR" Length="200" />
<Column Name="owner.user.id" Label="OwnerId" DataType="DT_WSTR" Length="200" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- ===========================
FOLDERS LIST
============================ -->
<EndPoint Name="list_folders" Template="ItemOutput" Label="List Folders" Url="/sites/[$SiteId$]/drives/[$DriveId$][$SearchPath$]/[$SearchType$]"
Filter="$.value[?(@.folder.childCount != null)]" Method="GET">
<Params>
<Param Name="Filter" Label ="Record Filter (Client Side)"
Options="All Folders=$.value[?(@.folder.childCount !=null)];
All Folders Under a Specific Folder=$.value[?(@.folder.childCount !=null && @parentReference.id=='SOME-FOLDER-ID-GOES-HERE')];
All Folders=$.value[?(@.folder.childCount != null)];
Folder Name Equals=$.value[?(@.folder.childCount !=null && @name=='SOME-FOLDER-NAME-HERE')];
Folder Id Equals=$.value[?(@.folder.childCount !=null && @id=='SOME-FOLDER-ID-GOES-HERE')];
Folder Name Contains=$.value[?(@.folder.childCount !=null && @name=~/somename$/)];
Folder Name With Regex Pattern=$.value[?(@.folder.childCount !=null && @name=~/^somefolder_\d\d\d.txt$/)];
Folder Name With Regex Pattern (Multiple)=$.value[?(@.folder.childCount !=null && @name=~/somefolderAAA|somefolderBBB/)]"
/>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" Value="search(q='.xls')" />
<Param Name="SearchPath" Label="Search Path (Default=Root Folder) - Max 200 items listed"
RequiredIf="SearchType==children"
OptionsExtra="Example#1 (Recursive Scan - Under Root)=/root;
Example#2 (Recursive Scan - Under Subfolder Path)=/root:/somefolder/subfolder:;
Example#3 (Non-Recursive Scan - Under Folder Id)=/items/ABCD123-SOME-FOLDER-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];OrderBy=[$OrderBy$]"
Value="/root"
MultiSelectLimit="200"
/>
<Param Name="OrderBy" Label="Order By Field(s) - Only for Recursive SearchType" Type="Query" Key="$orderBy"
MultiSelect="True" MultiSelectSeparator=","
Desc="Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc"
Options="name;name desc;size;size desc;createdDateTime;createdDateTime desc;lastModifiedDateTime;lastModifiedDateTime desc"
VisibleIf="SearchType!=children"
/>
</Params>
</EndPoint>
<!-- ===========================
FILES LIST
============================ -->
<EndPoint Name="list_files" Label="List Files" Template="ItemOutput" Url="/sites/[$SiteId$]/drives/[$DriveId$][$SearchPath$]/[$SearchType$]"
Filter="$.value[?(@.file.mimeType != null)]"
Method="GET" HelpLink="https://learn.microsoft.com/en-us/graph/MyFolder/driveitem-search?view=graph-rest-1.0&tabs=http"
>
<Params>
<Param Name="Filter" Template="T_FilterParam" Value="$.value[?(@.file.mimeType != null)]" />
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam"/>
<Param Name="SearchType" Template="T_SearchTypeParam" Value="search(q='')" />
<Param Name="SearchPath" Label="Search Path (Default=Root Folder) - Max 200 items listed" VisibleIf="SearchType!=sharedWithMe"
RequiredIf="SearchType==children"
OptionsExtra="Example#1 (Recursive Scan - Under Root)=/root;
Example#2 (Recursive Scan - Under Subfolder Path)=/root:/somefolder/subfolder:;
Example#3 (Non-Recursive Scan - Under Folder Id)=/items/ABCD123-SOME-FOLDER-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];OrderBy=[$OrderBy$]"
Value="/root"
MultiSelectLimit="200"
/>
<Param Name="OrderBy" Label="Order By Field(s) - Only for Recursive SearchType" Type="Query" Key="$orderBy"
MultiSelect="True" MultiSelectSeparator=","
Desc="Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc"
Options="name;name desc;size;size desc;createdDateTime;createdDateTime desc;lastModifiedDateTime;lastModifiedDateTime desc"
VisibleIf="SearchType!=children"
/>
</Params>
</EndPoint>
<!-- ===========================
EXCEL FILES
============================ -->
<EndPoint Name="list_excel_files" Label="List Excel Files" Template="ItemOutput" Url="/sites/[$SiteId$]/drives/[$DriveId$][$SearchPath$]/[$SearchType$]"
Filter="$.value[?(@file.mimeType != null && @name=~/(\.xlsx|\.xls)$/)]"
Method="GET" HelpLink="https://learn.microsoft.com/en-us/graph/MyFolder/driveitem-search?view=graph-rest-1.0&tabs=http"
>
<Params>
<Param Name="Filter" Template="T_FilterParam" Value="$.value[?(@file.mimeType != null && @name=~/(\.xlsx|\.xls)$/)]" />
<Param Name="SiteId" Template="T_SiteIdParam"/>
<Param Name="DriveId" Template="T_DriveIdParam"/>
<Param Name="SearchType" Label="Search Type (Default=Recursive)" Value="search(q='.xls')"
Options="Recursive (not supported for Application Credentials with Sites.Selected Scope)=search(q='.xls');Non-Recursive (Only for Non-Shared Items)=children"
Desc="NOTE: Not Supported with Application Credentials which uses Sites.Selected Scope - Use Non-Recursive search option (children) instead. If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property)."/>
<Param Name="SearchPath" Label="Search Path (Default=Root Folder)"
RequiredIf="SearchType==children"
OptionsExtra="Example#1 (Recursive Scan - Under Root)=/root;
Example#2 (Recursive Scan - Under Subfolder Path)=/root:/folder/subfolder:;
Example#3 (Non-Recursive Scan - Under Folder Id)=/items/ABCD123-SOME-FOLDER-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];OrderBy=[$OrderBy$]"
Value="/root"
/>
<Param Name="OrderBy" Label="Order By Field(s) - Only for Recursive SearchType" Type="Query" Key="$orderBy"
MultiSelect="True" MultiSelectSeparator=","
Desc="Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc"
Options="name;name desc;size;size desc;createdDateTime;createdDateTime desc;lastModifiedDateTime;lastModifiedDateTime desc"
VisibleIf="SearchType!=children"
/>
</Params>
</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" Template="T_SiteIdParam" />
</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" Template="T_SiteIdParam" />
</Params>
</EndPoint>
<!-- =======================
LIST FIELDS
============================ -->
<EndPoint Name="get_list_fields" UseRawCache="True" 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" Template="T_SiteIdParam" Label="SiteId (Re-Select ListId after you change this)"/>
<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" Template="T_SiteIdParam" Label="SiteId (Re-Select ListId after you change this)"/>
<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" Label="SiteId (Re-Select ListId after you change this)"
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" Label="SiteId (Re-Select ListId after you change this)"
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" Label="SiteId (Re-Select ListId after you change this)"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True" Label="ListId (Re-Enter ListItemId after you change this)"
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="ContinueOn404Error" Key="ContineOn404Error" Value="True" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;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" Label="SiteId (Re-Select ListId after you change this)"
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=|raw=choice.displayAs(equals:checkBoxes)|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;DT_RAW=raw"
/>
<Column Name="fields.[$parent.name$]@odata\u002Etype" Label="[$parent.name$]OdataType"
Desc="[$parent.name$]@odata\u002Etype must be set as Collection(Edm.String)"
DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.choice.displayAs=='checkBoxes')]"
/>
<!-- LOOKUP FIELDS
TODO: Raw=True can be conditional based on following
ColumnInfoMap add ...|raw=lookup.allowMultipleValues(equals:True),personOrGroup.allowMultipleSelection(equals:True)
DataTypeMap add ;DT_RAW=raw
-->
<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;"
Raw="True">
</Column>
<Column Name="fields.[$parent.name$]LookupId@odata\u002Etype" Label="[$parent.name$]LookupIdOdataType"
Desc="[$parent.name$]@odata\u002Etype must be set as Collection(Edm.Int32)"
DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.lookup.allowMultipleValues==true || @.personOrGroup.allowMultipleSelection==true)]"
/>
</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" Label="SiteId (Re-Select ListId after you change this)"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True" Label="ListId (Re-Enter ListItemId after you change this)"
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=|raw=choice.displayAs(equals:checkBoxes)|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;DT_RAW=raw"
/>
<Column Name="fields.[$parent.name$]@odata\u002Etype" Label="[$parent.name$]OdataType"
Desc="[$parent.name$]@odata\u002Etype must be set as Collection(Edm.String)"
DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.choice.displayAs=='checkBoxes')]"
/>
<!-- LOOKUP FIELDS
TODO: Raw=True can be conditional based on following
ColumnInfoMap add ...|raw=lookup.allowMultipleValues(equals:True),personOrGroup.allowMultipleSelection(equals:True)
DataTypeMap add ;DT_RAW=raw
-->
<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;"
Raw="True">
</Column>
<Column Name="fields.[$parent.name$]LookupId@odata\u002Etype" Label="[$parent.name$]LookupIdOdataType"
Desc="[$parent.name$]@odata\u002Etype must be set as Collection(Edm.Int32)"
DataType="DT_WSTR" Length="100"
Expand="True"
DataEndPoint="get_list_fields"
DataEndPointParameters="SiteId=[$SiteId$];ListId=[$ListId$];Filter=$.columns[?(@.readOnly == false && @.lookup.allowMultipleValues==true || @.personOrGroup.allowMultipleSelection==true)]"
/>
</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" Label="SiteId (Re-Select ListId after you change this)"
OptionsEndPoint="list_sites"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a site"
/>
<Param Name="ListId"
Required="True" Label="ListId (Re-Enter ListItemId after you change this)"
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" DataType="DT_WSTR" Length="2000" />
</OutputColumns>
</EndPoint>
<!-- ======================
EXCEL WORKSHEETS
======================= -->
<EndPoint Name="list_excel_worksheets"
Label="List Excel Worksheets of a File (i.e. List Tabs)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/workbook/worksheets"
Method="GET" Filter="$.value[*]">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" Value="search(q='.xls')" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Label="FileId -OR- Path"
OptionsExtra="Example#1 (File Path)=root:/myfile.xlsx:;
Example#2 (File Path)=root:/somefolder/myfile.xlsx:;
Example#2 (File Id)=items/01R65-xxx-SOME-FILE-ID"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id_path"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
Required="True"
OptionsEndPointParameters="SiteId=[$SiteId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.file.mimeType != null)];SearchPath=[$SearchPath$]"
Desc="Specify a file Id or Path. Syntax: [items/{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/myfile.xlsx: )"
Order="2" />
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="50" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="120" />
<Column Name="position" Label="Position" DataType="DT_I4" />
<Column Name="visibility" Label="visibility" DataType="DT_WSTR" Length="50" />
<Column Name="@odata.id" Label="OdataId" DataType="DT_WSTR" Length="300" />
</OutputColumns>
</EndPoint>
<EndPoint Name="get_excel_worksheet_autodetect"
Label="Read Excel Worksheet (Auto Detect Range)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$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/MyFolder/worksheet-usedrange?view=graph-rest-1.0">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" Value="search(q='.xls')"/>
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdExcelParam" />
<Param Name="SheetId" Template="T_SheetIdParam" />
<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 (From Exact Range)" Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/workbook/worksheets('[$SheetId$]')/range(address='[$Range$]')" Method="GET" Filter="$.values[*]">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" Value="search(q='.xls')"/>
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdExcelParam" />
<Param Name="SheetId" Template="T_SheetIdParam" />
<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>
<!-- ===========================
READ CSV FILE
=========================== -->
<EndPoint Name="get_csv_file" Label="Read CSV File (Generic)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="GET" ResponseFormat="Csv"
>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<Param Name="DataFormat" Type="Property" Value="Notset" Hidden="True"/>
<Param Name="ContinueOn404Error" Value="True" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" Desc="Set True by default to allow Write scenario without failing on Meta detect" />
<Param Name="ColumnDelimiter" Type="Property" Value="," Options="Comma=,;Tab={TAB};Pipe=|;New Line={NEWLINE};Carriage Return={CR};Line Feed={CR};Space={SPACE};Custom Hex Value(2-Bytes e.g. Tab Char)=\x0009;Custom Hex Value Example (Tab+Tab Char) (4-Bytes)=\x00090009" Required="True"/>
<Param Name="HasColumnHeaderRow" Type="Property" Value="True" Required="True"/>
<Param Name="Encoding" Type="Property" />
<Param Name="CharacterSet" Type="Property" />
<Param Name="EnableCustomReplace" Type="Property" />
<Param Name="SearchFor" Type="Property" />
<Param Name="ReplaceWith" Type="Property" />
<Param Name="ThrowErrorOnColumnCountMismatch" Type="Property" />
<Param Name="ThrowErrorOnNoRecordFound" Type="Property" />
<Param Name="AllowComment" Type="Property" />
<Param Name="SkipRows" Type="Property" />
<Param Name="SkipHeaderCommentRows" Type="Property" />
<Param Name="TreatBlankAsNull" Type="Property" />
<Param Name="IgnoreBlankLines" Type="Property" />
<Param Name="SkipEmptyRecords" Type="Property" />
<Param Name="TrimHeaders" Type="Property" />
<Param Name="TrimFields" Type="Property" />
<Param Name="FileCompressionType" Type="Property" Required="True" Value="None"/>
<Param Name="DateFormatString" Type="Property" Options="yyyy-MM-dd;yyyyMMdd;MM/dd/yyyy;dd/MM/yyyy;yyyy-MM-dd HH:mm:ss;yyyy-MM-dd HH:mm:ss.fff"/>
</Params>
</EndPoint>
<!-- ===========================
WRITE CSV FILE
=========================== -->
<EndPoint Name="create_csv_file" Label="Create / Update CSV File (Generic)"
Template="ItemOutput"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="PUT" RequestFormat="Csv" BatchSize="10000000"
MetaDetectionOrder="StaticDynamicVirtual"
ContentType="text/plain"
Body="{$rows$}"
>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<Param Name="DataFormat" Type="Property" Value="Notset" Hidden="True"/>
<Param Name="ContinueOn404Error" Value="True" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" Desc="Set True by default to allow Write scenario without failing on Meta detect" />
<Param Name="ColumnDelimiter" Type="Property" Value="," Options="Comma=,;Tab={TAB};Pipe=|;New Line={NEWLINE};Carriage Return (\r)={CR};Line Feed (\n)={LF};Space={SPACE};Custom Hex Value(2-Bytes e.g. Tab Char)=\x0009;Custom Hex Value Example (Tab+Tab Char) (4-Bytes)=\x00090009" Required="True"/>
<Param Name="RowDelimiter" Type="Property" Value="{NEWLINE}" Options="New Line={NEWLINE};Carriage Return (\r)={CR};Line Feed (\n)={LF};Space={SPACE};Tab={TAB};Vertical Tab={VTAB};Custom Hex Value(2-Bytes e.g. Tab Char)=\x0009;Custom Hex Value Example (Tab+Tab Char) (4-Bytes)=\x00090009" Required="True"/>
<Param Name="HasColumnHeaderRow" Type="Property" Value="True" Required="True"/>
<Param Name="QuotesAroundValue" Type="Property" Value="True" Required="True"/>
<Param Name="AlwaysQuoteRegardlessDataType" Type="Property" />
<Param Name="Encoding" Type="Property" />
<Param Name="CharacterSet" Type="Property" />
<Param Name="WriterDateTimeFormat" Type="Property" Options="yyyy-MM-dd;yyyyMMdd;MM/dd/yyyy;dd/MM/yyyy;yyyy-MM-dd HH:mm:ss;yyyy-MM-dd HH:mm:ss.fff"/>
</Params>
</EndPoint>
<!-- ===========================
READ JSON FILE
=========================== -->
<EndPoint Name="get_json_file" Label="Read JSON File (Generic)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="GET" ResponseFormat="Json"
>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<Param Name="DataFormat" Type="Property" Value="Notset" Hidden="True"/>
<Param Name="ContinueOn404Error" Value="True" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" Desc="Set True by default to allow Write scenario without failing on Meta detect" />
<Param Name="Filter" Type="Property" Hidden="False"
Options="No filter=;Example1=$.store.books[*];
Example2 (Sections Under Books)=$.store.books[*].sections[*];
Example3 (Equals)=$.store.books[?(@author=='sam')];
Example4 (Equals - Any Section)=$..[?(@author=='sam')];
Example5 (Not Equals - Any Section)=$..[?(@author!='sam')];
Example6 (Number less than)=$.store.books[?(@.price<10)]
Example7 (Regular Expression - Contains Pattern)=$.store.books[?(@author=~ /sam|bob/ )];
Example8 (Regular Expression - Does Not Contain Pattern)=$.store.books[?(@author=~ /^((?!sam|bob).)*$/ )];
Example9 (Regular Expression - Exact Pattern Match)=$.store.books[?(@author=~ /^sam|bob$/ )];
Example10 (Regular Expression - Starts With)=$.store.books[?(@author=~ /^sam/ )];
Example11 (Regular Expression - Ends With)=$.store.books[?(@author=~ /sam$/ )];
Example12 (Between)=$.store.employees[?( @.hiredate>'2015-01-01' && @.hiredate<'2015-01-04' )]"
/>
<Param Name="ExcludedProperties" Type="Property" />
<Param Name="Encoding" Type="Property" />
<Param Name="CharacterSet" Type="Property" />
<Param Name="EnableCustomReplace" Type="Property" />
<Param Name="SearchFor" Type="Property" />
<Param Name="ReplaceWith" Type="Property" />
<Param Name="EnableArrayFlattening" Type="Property" />
<Param Name="MaxArrayItemsToFlatten" Type="Property" />
<Param Name="ArrayTransformType" Type="Property" />
<Param Name="ArrayTransColumnNameFilter" Type="Property" />
<Param Name="ArrayTransRowValueFilter" Type="Property" />
<Param Name="ArrayTransEnableCustomColumns" Type="Property" />
<Param Name="ArrayTransCustomColumns" Type="Property" />
<Param Name="EnablePivot" Type="Property" />
<Param Name="FileCompressionType" Type="Property" Required="True" Value="None"/>
<Param Name="DateFormatString" Type="Property" Options="yyyy-MM-dd;yyyyMMdd;MM/dd/yyyy;dd/MM/yyyy;yyyy-MM-dd HH:mm:ss;yyyy-MM-dd HH:mm:ss.fff"/>
</Params>
</EndPoint>
<!-- ===========================
WRITE JSON FILE
=========================== -->
<EndPoint Name="create_json_file" Label="Create / Update JSON File (Generic)"
Template="ItemOutput"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="PUT" RequestFormat="Json" BatchSize="10000000"
MetaDetectionOrder="StaticDynamicVirtual"
ContentType="application/json"
Body="{$rows$}"
>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<Param Name="DataFormat" Type="Property" Value="Notset" Hidden="True"/>
<Param Name="ContinueOn404Error" Value="True" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" Desc="Set True by default to allow Write scenario without failing on Meta detect" />
<Param Name="JsonOutputFormat" Type="Property" Value="Default" Required="True" />
<Param Name="DoNotOutputNullProperty" Type="Property" />
<Param Name="LayoutMap" Type="Property"
Hidden="False"
PropertyScope="EndPoint" Editor="MultiLine"
Value="<?xml version="1.0" encoding="utf-8"?>
<!-- Example#1: Output all columns -->
<settings>
<dataset id="root" main="True" readfrominput="True" />
<map src="*" />
</settings>
<!-- Example#2: Records under array
<?xml version="1.0" encoding="utf-8"?>
<settings singledataset="True">
<dataset id="root" main="True" readfrominput="True" />
<map name="MyArray" dataset="root" maptype="DocArray">
<map src="OrderID" name="OrderID" />
<map src="OrderDate" name="OrderDate" />
</map>
</settings>
-->
<!-- Example#3: Records under nested section
<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="dsRoot" main="True" readfrominput="True" />
<map name="NestedSection">
<map src="OrderID" name="OrderID_MyLabel" />
<map src="OrderDate" name="OrderDate_MyLabel" />
</map>
</settings>
-->"
/>
<Param Name="Encoding" Type="Property" />
<Param Name="CharacterSet" Type="Property" />
<Param Name="WriterDateTimeFormat" Type="Property" Options="yyyy-MM-dd;yyyyMMdd;MM/dd/yyyy;dd/MM/yyyy;yyyy-MM-dd HH:mm:ss;yyyy-MM-dd HH:mm:ss.fff"/>
</Params>
</EndPoint>
<!-- ======================
DOWNLOAD FILE CONTENTS
=========================== -->
<EndPoint Name="download_file"
Label="Read File Data (As Byte Array)"
Desc="Get file data as a column field (Binary data)"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="GET">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<Param Name="SaveContentAsBinary"
Required="True"
Type="Property"
Hidden="True"
Value="True"
/>
</Params>
<OutputColumns>
<Column Name="data" Label="Data" Desc="This field returns file content as binary data (as is). You can save binary data to database or save to disk. Some app like Microsoft SSIS (ETL tool) can export byte array (binary data) to file using transformation like [Export Column]. Refer to specific ETL tool help file see if they offer such feature." DataType="DT_IMAGE" />
</OutputColumns>
</EndPoint>
<!-- ===========================
DOWNLOAD A File
=========================== -->
<EndPoint Name="download_file_to_disk" Label="Download File"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$FileId$]/content" Method="GET">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FileId" Template="T_FileIdParam" Required="True" />
<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"
Label="Target File Path (e.g. c:\somefolder\file.xlsx)"
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>
<!-- ===========================
UPLOAD A FILE
=========================== -->
<EndPoint Name="upload_file" Template="ItemOutput" Label="Upload File"
Url="/sites/[$SiteId$]/drives/[$DriveId$]/items/root:/[$SharePointFilePath$]:/createUploadSession"
Method="POST"
ContentType="application/json"
Desc="Upload a file to the specified path"
HelpLink="https://learn.microsoft.com/en-us/graph/MyFolder/driveitem-createuploadsession?view=graph-rest-1.0#create-an-upload-session"
>
<Body>
<![CDATA[{
"item": {
"@microsoft.graph.conflictBehavior": "[$ConflictBehavior$]"
[$UseSourceFileTimeStamp$]
}
}]]>
</Body>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam"/>
<Param Name="DiskFilePath"
Required="True"
Label="Disk File Path (e.g. c:\somefolder\file.xlsx)"
Type="Placeholder"
Desc="Specify a disk file path"
Example="C:\MyParentFolder\Subfolder\file.txt"
Editor="FileOpen"
/>
<Param Name="SharePointFilePath"
Required="True"
Label="Target File Path OR Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234)"
Type="Placeholder"
Options="Example #1 (Save On Root)=file.xlsx;
Example #2 (Save Under Folder)=myfolder/myfile.xlsx;
Example #3 (Save Under Child Folder)=myfolder/subfolder/myfile.xlsx;
Example #4 (Overwrite Exising File by ID)=0Zxxxx1234"
Desc="Specify target file path"
Example="MyParentFolder/Subfolder/file.txt"
/>
<Param Name="ConflictBehavior"
Required="True"
Label="Conflict Behavior (What to do if file exists)"
Value="fail"
Options="fail;replace;rename"
/>
<!-- For now we dont send UTC file time see TFS (Name, Description not working well soe skip for now)... -->
<Param Name="UseSourceFileTimeStamp"
Label="Use Source File Created/Modified DateTime rather than Uploaded Time"
Options="Use Uploaded Time (Blank)=;Use Source File Time=true"
ValueTemplate=","fileSystemInfo" : { "createdDateTime" : "<<[$DiskFilePath,FUN_FILE_CREATE_DATE$],FUN_TO_UTC_DATETIME>>", "lastModifiedDateTime": "<<[$DiskFilePath,FUN_FILE_LASTEDIT_DATE$],FUN_TO_UTC_DATETIME>>" }"
Desc="Set this property if you like to set file creation/modified date as original file. By default its set to uploaded date/time. This may not be desired if file was actually created on different date than uploaded. If you do not set then current date/time used (Upload time)."
/>
</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="16384000" Hidden="True" />
<Param Name="RequestTimeoutMs"
Type="Property"
Desc="Timeout in milliseconds after which upload stops"
Value="7200000"
/>
</Params>
</EndPoint>
</EndPoint>
<!-- ===========================
GET AN ITEM
=========================== -->
<EndPoint Name="get_item" Label="Get Item (File or Folder)" Template="ItemOutput" Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$ItemId$]" Method="GET">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="ItemId" Template="T_ItemIdParam"
Desc="Specify a File/Folder Id or Path (e.g. root:/myfolder/file.xyz: )"
ReadAs="Id" IsKey="True" />
<Param Name="ContinueOn404Error" Value="False" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" />
</Params>
<InputColumns>
<Column Name="ItemId" Label="Id" Key="True" MapToParam="True" />
</InputColumns>
</EndPoint>
<!-- ===========================
DELETE AN ITEM
=========================== -->
<EndPoint Name="delete_item" Label="Delete Item (File or Folder)" Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$ItemId$]" Method="DELETE">
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="ItemId" Required="True" Template="T_ItemIdParam" Desc="Specify an item Id you like to delete" ReadAs="Id" IsKey="True" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Deleted'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
<Param Name="ContinueOn404Error" Value="False" Key="ContineOn404Error" Hidden="False" Label="Continue On 404 Error (When item not found)" Type="Property" Options="False;True" />
</Params>
<InputColumns>
<Column Name="ItemId" Label="Id" Key="True" MapToParam="True" />
</InputColumns>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
<EndPoint Name="move_rename_item" Label="Move or rename item (File or Folder)" Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$ItemId$]"
Method="PATCH" ContentType="application/json"
Desc="Move or rename an item (File or Folder) to a different location in the same drive or another drive (e.g. different Document Library)"
AllowParamBindForLayoutMap="True"
JsonRowFormat="Multicontent"
Template="ItemOutput"
>
<Body>{$rows$}</Body>
<LayoutMap>
<![CDATA[<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="root" readfrominput="True" />
<map name="parentReference">
<map name="siteId" src="TargetSiteId" />
<map name="driveId" src="TargetDriveId" />
<map name="id" src="TargetFolderId" />
</map>
<map name="name" src="TargetItemName" />
</settings>]]>
</LayoutMap>
<Params>
<Param Name="Operation" Value="Move" Options="Move;Rename" Required="True"/>
<Param Name="SiteId" Template="T_SiteIdParam" Label="Source Site Id (Optional - Select if different than default site)" />
<Param Name="DriveId" Template="T_DriveIdParam" Label="Source Drive Id" />
<Param Name="ItemId" Template="T_ItemIdParam" Label="Source File or Folder Id you like to move or rename" Required="True" Desc="Specify an item Id you like to move or rename" />
<Param Name="TargetSiteId" Template="T_SiteIdParam" Label="Target Site Id (Optional - Select if different than source SiteId)" />
<Param Name="TargetDriveId" Template="T_TargetDriveIdParam" Label="Target DriveId (Optional - Select if different than source DriveId)" Required="False" />
<Param Name="TargetFolderId" Template="T_TargetFolderIdParam" RequiredIf="Operation==Move" Desc="Specify a target folder where item should move after move or rename operation. If you are doing inplace rename and not doing move after rename then keep it blank" />
<Param Name="TargetItemName" Label="Target Item Name (Optional for Move - e.g. Myfile.pdf or MyFolder)" RequiredIf="Operation==Rename" Desc="Specify target item name you like to give after move or rename operation. If its move operation and you like to keep same item name as source then keep this blank." />
<Param Name="SearchType" Template="T_SearchTypeParam" Label="Source Search Type (For UI Only - Default=Recursive - i.e. Blank)" />
<Param Name="SearchPath" Template="T_SearchPathParam" Label="Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed" />
<Param Name="TargetSearchType" Template="T_TargetSearchTypeParam" />
<Param Name="TargetSearchPath" Template="T_TargetSearchPathParam" />
<Param Name="Prefer" Label="Prefer Async Operation (Enable if you get error)" Key="prefer" AllowDuplicateHeader="True" Type="Header" Options="None=;Async (Needed for Large files, Cross Site)=respond-async" />
</Params>
</EndPoint>
<!-- ===========================
COPY AN ITEM
=========================== -->
<EndPoint Name="copy_item" Label="Copy item (File or Folder)" Url="/sites/[$SiteId$]/drives/[$DriveId$]/[$ItemId$]/copy"
Method="POST" ContentType="application/json"
Desc="Copy an item (File or Folder) to a different location in the same drive or another drive (e.g. different User's drive)"
AllowParamBindForLayoutMap="True"
JsonRowFormat="Multicontent"
>
<Body>{$rows$}</Body>
<LayoutMap>
<![CDATA[<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="root" readfrominput="True" />
<map name="name" src="TargetItemName" />
<map name="parentReference">
<map name="siteId" src="TargetSiteId" />
<map name="driveId" src="TargetDriveId" />
<map name="id" src="TargetFolderId" />
</map>
<map name="@microsoft.graph.conflictBehavior" src="ConflictBehavior" />
</settings>]]>
</LayoutMap>
<Params>
<Param Name="SiteId" Template="T_SiteIdParam" Label="Source Site Id (Optional - Select if different than default site)" />
<Param Name="DriveId" Template="T_DriveIdParam" Label="Source Drive Id" />
<Param Name="ItemId" Label="Source File or Folder Id you like to copy" Required="True" Template="T_ItemIdParam" Desc="Specify an item Id you like to copy" />
<Param Name="TargetSiteId" Template="T_SiteIdParam" Label="Target Site Id (Optional - Select if different than source SiteId)" />
<Param Name="TargetDriveId" Template="T_TargetDriveIdParam" Label="Target DriveId (Optional - Select if different than source DriveId)" Required="False" />
<Param Name="TargetFolderId" Template="T_TargetFolderIdParam" Label="Target Folder Id (e.g. 01R65QTTRxxxxx -OR- root)" Required="True" Desc="Specify a target folder where item should be copied" />
<Param Name="TargetItemName" Label="Target Item Name (Optional)" Required="False" Desc="Specify target item name you like to give after copy operation. Keep it blank if you like to keep same name as source." />
<Param Name="SearchType" Template="T_SearchTypeParam" Label="Source Search Type (For UI Only - Default=Recursive - i.e. Blank)" />
<Param Name="SearchPath" Template="T_SearchPathParam" Label="Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed" />
<Param Name="TargetSearchType" Template="T_TargetSearchTypeParam" />
<Param Name="TargetSearchPath" Template="T_TargetSearchPathParam" />
<Param Name="Prefer" Label="Prefer Async Operation (Enable if you get error)" Key="prefer" AllowDuplicateHeader="True" Type="Header" Options="None=;Async (Needed for Large files, Cross Site)=respond-async" />
<!-- This only works on file names different else will not throw on fail -->
<Param Name="ConflictBehavior"
Required="False"
Type="Query"
Key="@microsoft.graph.conflictBehavior"
Label="Conflict Behavior (What to do if file exists at target location)"
Options=";fail;replace;rename"
Desc="Default ConflictBehavior is fail if file exists. The conflictBehavior is not supported for OneDrive Consumer. Also it will not fail if TargetItemName same as source file name"
/>
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Done'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Sites" SelectEndPoint="list_sites" />
<Table Name="Lists" SelectEndPoint="list_lists" />
<Table Name="FileReaderWriterCsv" Label="CSV File Read/Write" SelectEndPoint="get_csv_file" InsertEndPoint="create_csv_file" UpdateEndPoint="create_csv_file" />
<Table Name="FileReaderWriterJson" Label="JSON File Read/Write" SelectEndPoint="get_json_file" InsertEndPoint="create_json_file" UpdateEndPoint="create_json_file" />
<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 MyList
--WHERE Id = 1234]]>
</Code>
</Example>
<Example Group="ODBC" Label="Insert data into a named list">
<Code>
<![CDATA[INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)]]>
</Code>
</Example>
<Example Group="ODBC" Label="Insert multiple Lookup or Person/Group IDs into a named list">
<Code>
<![CDATA[
--new version --
INSERT INTO MyList(Title, ManyPersonsLookupId, ManyPersonsLookupIdOdataType)
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Insert multiple Choice Values into a named list">
<Code>
<![CDATA[
--new version --
INSERT INTO MyList(Title, MultiChoiceField, MultiChoiceFieldODataType)
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--old version --
INSERT INTO MyList(Title, MultiChoiceField, "fields.MultiChoiceField@odata\u002Etype")
VALUES ('John Doe', '["Choice1","Choice5"]', 'Collection(Edm.String)')
WITH (Output=1)
--Considering "MultiChoiceField" is the name of the field in SharePoint List
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update data in a named list">
<Code>
<![CDATA[UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123']]>
</Code>
</Example>
<Example Group="ODBC" Label="Update Multi Select Lookup or Person/Group IDs field">
<Code>
<![CDATA[UPDATE MyList
SET Title = 'Elizabeth Johnson',
--Method#1 (Pass type info in the same value)
ManyPersonsLookupId = '[11,22,33]', "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
--Method#2 (Pass type info via ODataField)
--OR--
--ManyPersonsLookupId = '[11,22,33]',
--ManyPersonsLookupIdODataType" = 'Collection(Edm.Int32)
WHERE Id = '123']]>
</Code>
</Example>
<Example Group="ODBC" Label="Update / Insert Sharepoint List Data (Mixed Data Types - e.g. Multi Choice, Lookup, Person, DateTime, Number)">
<Code>
<![CDATA[UPDATE "Travel Requests" --list name
SET
Title='MyTitle @ <<FUN_NOW>>'
--Method#1 Pass Value and Type in 2 fields
, CustomText = 'This is text'
, CustomTextLarge = 'This is really long text ....'
, CustomNumber = 10000.12345
, CustomDateTime ='2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, CustomChoice = 'BB'
--Choice Multi-Select
, CustomChoiceMulti = '["AA", "BB"]'
, CustomChoiceMultiODataType='Collection(Edm.String)' --for multi select must supply OData Type
--OR-- append like below in one field
--, CustomChoiceMulti = '["AA", "BB"], "CustomChoiceMulti@odata.type" : "Collection(Edm.String)"'
--Lookup
--field with Lookup type must be set to [<field-name>LookupId] field (suffix with LookupId)
, CustomLookupSingleLookupId = 1
--Lookup Multi-Select
, CustomLookupMultiLookupId = '[1,3]' --for multi select must supply OData Type
, CustomLookupMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomLookupMultiLookupId = '[1,3], "CustomLookupMultiLookupId@odata.type" : "Collection(Edm.String)"'
--Person
,CustomPersonLookupId = 11
--Person Multi-Select
, CustomPersonMultiLookupId = '[11, 22]'
, CustomPersonMultiLookupIdODataType='Collection(Edm.Int32)'
--OR-- append like below in one field
--, CustomPersonMultiLookupIdODataType = '[11,22], "CustomPersonMultiLookupIdODataType@odata.type" : "Collection(Edm.Int32)"'
Where Id=1 --List Item Id
--- Insert Example ----
INSERT INTO "Travel Requests" --list name
(
Title
, CustomText
, CustomTextLarge
, CustomNumber
, CustomDateTime
, CustomChoice
, CustomChoiceMulti
, CustomChoiceMultiODataType
, CustomLookupSingleLookupId
--Lookup Multi-Select
, CustomLookupMultiLookupId
, CustomLookupMultiLookupIdODataType
--Person
,CustomPersonLookupId
, CustomPersonMultiLookupId
, CustomPersonMultiLookupIdODataType
)
VALUES(
'MyTitle @ <<FUN_NOW>>'
, 'This is text'
, 'This is really long text .. '
, 10000.12345
, '2012-12-31T23:59:59.123Z' --End with Z to treat as UTC time (avoid local-UTC conversion)
, 'BB'
--Choice Multi-Select
, '["AA", "BB"]'
, 'Collection(Edm.String)' --for multi select must supply OData Type
--Lookup
, 1
--Lookup Multi-Select
, '[1,3]' --for multi select must supply OData Type
, 'Collection(Edm.Int32)'
--Person
,11
--Person Multi-Select
, '[11, 22]'
, 'Collection(Edm.Int32)'
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Delete item from a named list">
<Code>
<![CDATA[DELETE FROM MyList WHERE Id=888
--OR--
DELETE FROM MyList WITH (ListItemId='123')
--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.]]>
</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 from SharePoint Document Library">
<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 from SharePoint Document Library">
<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 from SharePoint Document Library">
<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 (SharePoint Document Library)">
<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 (SharePoint Document Library)" 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 (SharePoint Document Library)">
<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 (SharePoint Document Library)">
<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 (SharePoint Document Library)">
<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 (SharePoint Document Library)">
<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 (SharePoint Document Library) - 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 (SharePoint Document Library) 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 Document Library">
<Code>
<![CDATA[SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
--,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Upload a file to SharePoint Document Library (Overwrite file if exists)" Desc="Uploads a file to a SharePoint Document Library and if file already exists then overwrite it rather than throwing error (Default is throw error)">
<Code>
<![CDATA[SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,DiskFilePath='C:\My files\Employees\List.xls'
,SharePointFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,SharePointFilePath='myfolder/List.xlsx' --Upload under some folder
--,SharePointFilePath='01SUOJPKGJXIXXGXACFVDK3QH5JYARFOMB' --Overwrite Exising File Id
,ConflictBehavior = 'replace' -- fail, rename, replace (If File Exists Default=fail)
--,UseSourceFileTimeStamp = 'true' --By default uploaded file set Current Date/Time for Creation/Modified date
)
--DriveId can be retrieved by selecting from 'list_drives' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Download a file to a local disk from a SharePoint document library">
<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 SharePoint 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>
<Example Group="ODBC" Label="List files in a folder path (Recursive)" Desc="Use list_files endpoint to list files recursively (list from child folder). However there are some limitations to list recursively, such as some new files may not appear right away or files not indexed wont show up.">
<Code>
<![CDATA[SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
--,SearchPath='/root' --default
--,SearchPath='/root:/myfolder:' --folder path
,SearchPath='/root:/myfolder/subfolder:' --folder path nested
,OrderBy='lastModifiedDateTime desc'
)
--You can get DriveId by selecting from 'Drives' table.
]]>
</Code>
</Example>
<Example Group="ODBC" Label="List files in a folder Id (Non-Recursive)" Desc="This examplel shles under a specific FolderId (Available in Non-recursive mode only). ">
<Code>
<![CDATA[SELECT * FROM list_files
WITH(
Filter='$.value[*]' --list both files and folders
--Filter='$.value[?(@.file.mimeType != null)]' --list only files
--Filter='$.value[?(@.folder.childCount != null)]' --list only folders
,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
,SearchType='children' --must use this type if you like to list items by FolderId
,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)
--You can get DriveId by selecting from 'Drives' table.
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Create / Update a CSV File from External Data Source (e.g. MSSQL / ODBC)" Desc="This example shows how to create / update a CSV file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to CSV file on SharePoint Document Library. If file does not exists then it creates a new one.">
<Code>
<![CDATA[INSERT INTO FileReaderWriterCsv
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.csv:'
--, FileId='root:/subfolder/test_out.csv:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ...
--, RowDelimiter='{NEWLINE}' --{LF}, {TAB}, | , \x0009 ...
--, HasColumnHeaderRow=0 --set for header less file
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--See Query Builder for more options
)]]>
</Code>
</Example>
<Example Group="ODBC" Label="Create / Update a JSON File in SharePoint Document Library from External Data Source (e.g. MSSQL / ODBC)" Desc="This example shows how to create / update a JSON file directly inside SharePoint Document Library using streaming approach. This example reads records from Microsoft SQL Server database and writes data to JSON file on SharePoint Document Library. If file does not exists then it creates a new one. It uses LayoutMap to generate JSON with custom layout.">
<Code>
<![CDATA[INSERT INTO FileReaderWriterJson
SOURCE( 'MSSQL'--OR 'ODBC'
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
, 'select OrderId,CustomerId,OrderDate FROM Northwind.dbo.Orders'
)
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/test_out.json:'
--, FileId='root:/subfolder/test_out.json:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
, JsonOutputFormat='Multicontent' --Default , Array2D, ArrayLines ...
--, DoNotOutputNullProperty='True'
--, Encoding='UTF8' --UTF8WithoutBOM
--, WriterDateTimeFormat='yyyy-MM-ddTHH:mm:ss.fff'
--Example#1: Output all columns
, LayoutMap='<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="root" main="True" readfrominput="True" />
<map src="*" />
</settings>'
--Example#2: Nested JSON (Records under an array)
/*
, LayoutMap='<?xml version="1.0" encoding="utf-8"?>
<settings singledataset="True">
<dataset id="root" main="True" readfrominput="True" />
<map name="MyArray" dataset="root" maptype="DocArray">
<map src="OrderID" name="OrderID" />
<map src="OrderDate" name="OrderDate" />
</map>
</settings>'
*/
--Example#3: Nested JSON (Records under a sub document section)
/*
, LayoutMap='<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="dsRoot" main="True" readfrominput="True" />
<map name="NestedSection">
<map src="OrderID" name="OrderID_MyLabel" />
<map src="OrderDate" name="OrderDate_MyLabel" />
</map>
</settings>'
*/
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read CSV File from SharePoint Document Library (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a CSV file from SharePoint Document Library without downloading on the Disk (Streaming approach).">
<Code>
<![CDATA[
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/test_out.csv.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Zip/Gzip Compressed CSV File from SharePoint Document Library (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a compressed CSV file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).">
<Code>
<![CDATA[
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.csv.zip:'
--, ColumnDelimiter=',' --{LF}, {TAB}, | , \x0009 ... default is comma (",")
--, HasColumnHeaderRow=0 --set for header less file
--, FileId='root:/MyFolder/dump.csv.gz:'
, FileCompressionType='Zip' --None, GZip, Zip
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read JSON File from SharePoint Document Library (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a JSON file from SharePoint Document Library without downloading on Disk (Streaming approach).">
<Code>
<![CDATA[
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
--to read compressed file use below way
--, FileId='root:/MyFolder/dump.json.gz:'
--, FileCompressionType='GZip' --None, GZip, Zip
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Zip/Gzip Compressed JSON File from SharePoint Document Library (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a compressed JSON file (Zip or Gzip) from SharePoint Document Library without downloading on the Disk (Streaming approach).">
<Code>
<![CDATA[
SELECT * from get_json_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/MyFolder/dump.json.zip:'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
, FileCompressionType='Zip' --None, GZip, Zip
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Generic API Call - Update MultiChoice / Multi Select Lookup field in SharePoint List"
Desc="This example shows how to make generic API call to update fields which allow multiple value selection in either Lookup or MultiChoice datatype. This example shows 2 types of fields. One is Multi Select e.g. MyMultiSelectLookupCol / MyMultiChoiceCol and another is Single select MySingleSelectCol (i.e. Dropdown). You must supply odata.type for Field. If its MultiChoice you can supply string ID/Value but set odata.type as Edm.String rather than Edm.Int32 ">
<Code>
<![CDATA[SELECT * from generic_request
WITH(
Url='https://graph.microsoft.com/v1.0/sites/root/lists/1d3126af-14ca-46c7-a82a-4865873756c6/items/1'
, RequestMethod='PATCH'
, Filter='$.fields'
, Headers='Content-Type: application/json || x-header2: abcd'
, Body='{
"fields": {
"MyMultiSelectLookupColLookupId@odata.type": "Collection(Edm.Int32)"
,"MyMultiSelectLookupColLookupId": [ 1 , 2 ]
,"MyMultiChoiceColumn@odata.type": "Collection(Edm.String)"
,"MyMultiChoiceColumn": [ "AAA" , "BBB" ]
,"MySingleChoiceColumnLookupId":1
}
}'
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Move a File or Folder in SharePoint Document Library"
Desc="This example shows how to move a file or folder to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>">
<Code>
<![CDATA[
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Item Id (File or Folder) you like to move or rename
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder Id
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR (for moving to root)
--, TargetFolderId='root'
--Target Item Name after Move (Optional): If you do not wish to change name after Move then do not supply below
--, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Rename a File or Folder in SharePoint Document Library"
Desc="This example shows how to rename a file or folder and you can also move to a different location in SharePoint Document Library. It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>">
<Code>
<![CDATA[
SELECT * FROM move_rename_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to rename (File or Folder)
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site Id (Optional): Only needed if moving file to a different Site
--, TargetSiteId='xyz.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if moving file to a different Drive
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder (Optional): If you are doing just rename and moving to different location then keep it keep blank
--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--, TargetFolderId='root'
--Target Item Name (Required for rename)
, TargetItemName='new_file_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Copy a File or Folder in SharePoint Document Library"
Desc="This example shows how to copy a file or folder to a different location in SharePoint Document Library (In same Drive or different SharePoint Document Library). It also shows how to use Placeholder Functions to name file daynamically. For example to add timestamp it uses <<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>">
<Code>
<![CDATA[
SELECT * FROM copy_item
WITH(
--Source Site (Leave blank for default)
-- SiteId='xxxx'
--Source Drive
, DriveId='b!0zqxxxxxxxxxxxxxxxxxxxxxxxx'
--Source Item you like to copy
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Site (Optional)
-- SiteId='abc.sharepoint.com,5304daff-xxxxxxxxxx'
--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder where item will be copied
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR--
--, TargetFolderId='root'
--Target item name (Optional) - Only needed if new name needed after copy
, TargetItemName='new_file_or_folder_name_<<yyyy-MM-dd-HH-mm-ss-fff,FUN_NOW>>.pdf'
--,ConflictBehavior='replace' --fail, rename (Ignored if TargetItemName is not supplied)
--Uncomment Below if you get error. Usually needed for large file or cross site operations
--, Prefer='respond-async'
)
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
