OneDrive Connector - Source Code
<!--
Items from folder:
https://graph.microsoft.com/v1.0/sites/root/drive/items/{.....ID.....}/children
Search files
https://graph.microsoft.com/v1.0/sites/root/drives/{.....ID.....}/search(q='xlsx')
Sites
https://graph.microsoft.com/v1.0/sites?search=*
API about METADATA:
https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/list_get?view=odsp-graph-online#example
-->
<ApiConfig Name="OneDrive"
Slug="onedrive-connector"
Id="b81fa083-78c9-461c-ad2a-5490446c9f43"
EngineVersion="5"
Version="4"
Desc="OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAGQAAABkCAYAAABw4pVUAAAJk2lDQ1BERUxMIFVQMzIxNlEgQ29sb3IgUHJvZmlsZSwgRDY1MDAAAHicnZZ3UFTJFof73js5AMMMQ4YhDTnDMIDkOATJUVRwyJkhipgQcQUURUQElAURCQom4ioioBhYFBRQMQyyCCjr4iqigvJgfVXuq31Vr+r9qrrPV6dP1znd/UcfAAiWVrbWdrAQAHHxKTxPB2uGf0AgA/sCoAEdCAAmoIRwkxNdvey9waps7VxcwD/0cRRAa/aB5to6Cd+rNMdIeQdw+2xuhVUe+Gf8f4gQGpbMXbXLqyOHm8hLAQDatcrs9JTENW5fZRpvtahVHljjiO/MX+Mt3/nDXzHenjYAwDgAcKSIv/GWvzE3MiR0NSVtNV7j33nXpLZWN8PHXV9Pl+XBsEmITeAx3HkJ4VGxYRoMW5ahjs7/OMT/q5SwjJQ1a5OQuJUXFRGZwlDhqjL0dHQNGbZhsbEMTjxX67/tW3uj7/Tj7n/4TrMA8FktGdv1w6cVAEDFZQAodT98CvYACEgB0NHPTeWlffeh1iY0IABBQAPiQAYoABWgCfSAETAFlsAOOAE34A0CwCbABZEgDvBAOsgCu0EuyAeHwFFQBipBDagHTeAiaANXwHVwE9wF98EIGAd8MAXegHnwESxBEISFyBAVEodkISVIHdKD2JA5ZAe5QJ5QABQMRUDxUCqUBe2B8qEiqAyqguqhC1AHdB26DQ1Bj6EJaBb6E/oCIzAJpsHSMBPWhtmwFewMe8Mb4Qg4Cc6Ec+CDcClcDZ+FW+Hr8F14BObDb+AFBCBEhI7IIZoIG7FB3JBAJBzhITuQPKQEqUaakE6kH3mA8JE55DMKg6KiGChNlCnKEeWD4qKSUDtQBagyVB2qFdWHeoCaQM2jvqHJaCm0OtoEzUH7oyPQ6ehcdAm6Ft2CvoEeQU+hP2IwGDpGGWOEccQEYKIx2zAFmBOYZkw3ZggziVnAYrHiWHWsGdYNG4JNweZij2PPYq9hh7FT2E84Ik4Wp4ezxwXi4nHZuBJcA64LN4ybxi3hhfBKeBO8Gz4UvxVfiK/Bd+Lv4afwSwQKQZlgRvAmRBN2E0oJTYQbhKeE90QiUZ5oTPQgRhF3EUuJ54m3iBPEzyRhkhrJhhRESiUdJJ0hdZMek96TyWQm2ZIcSE4hHyTXk3vJz8mfBKgCWgIcgVCBnQLlAq0CwwJvBfGCSoJWgpsEMwVLBC8J3hOcE8ILMYVshEKEdgiVC3UIjQktUKgUXYobJY5SQGmg3KbMCGOFmcJ2wqHCOcKnhHuFJ6kIVYFqQ+VS91BrqDeoUzQMTZnGoUXT8mnnaIO0eRFhEQMRX5EMkXKRqyJ8OkJn0jn0WHoh/SJ9lP5FVFrUSjRMdL9ok+iw6KKYpJilWJhYnliz2IjYF3GGuJ14jPhh8TbxZxIoCTUJD4l0iZMSNyTmJGmSppJcyTzJi5JPpGApNSlPqW1Sp6QGpBakZaQdpBOlj0v3Ss/J0GUsZaJlimW6ZGZlqbLmslGyxbLXZF8zRBhWjFhGKaOPMS8nJecolypXJTcotySvLO8jny3fLP9MgaDAVghXKFboUZhXlFV0VcxSbFR8ooRXYitFKh1T6ldaZCoz/Zj7mG3MGWUxZY5ypnKj8lMVsoqFSpJKtcpDVYwqWzVG9YTqfTVYzVAtUq1c7Z46rM5Sj1I/oT6kgdYw1ojXqNYY0yRpWmmmaTZqTmjRtVy0srXatN5qK2oHah/W7tf+pmOoE6tTozOuK6zrpJut26n7p56aHlevXO+hPlnfXn+nfrv+OwN1gzCDkwaPDKmGrob7DHsMv7KMWDxWE2vWSNEo2KjCaIxNY7uzC9i3jNHG1sY7ja8YfzZhmaSYXDT5w1TTNMa0wXRmnfK6sHU16ybN5M1CzKrM+OYM82Dzn835FnIWIRbVFi8tFSxDLWstp61UraKtzlq9tdax5lm3WC/amNhst+m2RWwdbPNsB+2E7Xzsyuye28vbR9g32s87GDpsc+h2RDs6Ox52HONIc7ices68k5HTdqc+Z5Kzl3OZ80sXNReeS6cr7OrkesT16Xql9fHr29yAG8ftiNszd2X3JPdfPDAe7h7lHq88dT2zPPu9qF6bvRq8Pnpbexd6j/uo+KT69PgK+gb51vsu+tn6Ffnx/bX9t/vfDZAIiApoD8QG+gbWBi5ssNtwdMNUkGFQbtDoRuWNGRtvb5LYFLvp6mbBzSGbLwWjg/2CG4KXQ9xCqkMWtnC2VGyZ59pwj3HfhFqGFofOhpmFFYVNh5uFF4XPRJhFHImYjbSILImci7KJKot6F+0YXRm9GOMWcyZmJdYvtjkOFxcc1xEvHB8T35cgk5CRMJSonpibyE8ySTqaNM9z5tUmQ8kbk9tTaKsf40CqSure1Ik087TytE/pvumXMigZ8RkDW9W27t86nWmfeXobaht3W0+WXNburIntVturdkA7tuzo2amwM2fn1C6HXXW7Cbtjdv+arZNdlP1hj9+ezhzpnF05k3sd9jbmCuTycsf2me6r/An1U9RPg/v19x/f/y0vNO9Ovk5+Sf5yAbfgzgHdA6UHVg6GHxwsZBWePIQ5FH9o9LDF4boiSlFm0eQR1yOtxYzivOIPRzcfvV1iUFJ5jHAs9Ri/1KW0/bji8UPHl8siy0bKrcubK6Qq9lcsngg9MXzS8mRTpXRlfuWXn6N+flTlUNVazawuOYU5lXbqVY1vTf9p9un6Wona/NqvZ+LP8Os86/rqjerrG6QaChvhxtTG2bNBZ++fsz3X3qTZVNVMb84/D86nnn99IfjC6EXniz2X2JeaLitdrmihtuS1Qq1bW+fbItv47QHtQx1OHT2dpp0tv2j9cuaK3JXyqyJXC7sIXTldK9cyry10J3bPXY+4PtmzuWe817/3YZ9H3+AN5xu3btrf7O236r92y+zWldsmtzvusO+03WXdbR0wHGj51fDXlkHWYOs9o3vt943vdw6tG+oathi+/sD2wc2HnId3R9aPDI36jD4aCxrjPwp9NPM49vG7J2lPlsZ3PUU/zXsm9KzkudTz6heqL5r5LP7VCduJgZdeL8cnuZNvfkv+bXkq5xX5Vcm07HT9jN7MlVn72fuvN7yeepP4Zmku93fK7xVvVd5e/sPyj4F5//mpd7x3K38WvBd/f+aDwYeeBfeF5x/jPi4t5n0S/1T3mf25/4vfl+ml9GXsculX1a+d35y/PV2JW1lJDtfX+6sVgEi2q63Ai5WV98zVXqIYgK+FKytLVSsrX6sBQMYB6E79F4bKmf4rzD3rAAAACXBIWXMAAAsSAAALEgHS3X78AAAO8ElEQVR42u2dd1DU1xbHz+/3WxRF6YsiQVBEBGZS5sU2UZ+JzzFqEGOSSXEc6cuigC0qAroYe4cHGmt8EmJ7Ro0FpSgowR4dEBCRIkaKSMQYY3mJ9537W3aXXneXXfz98Z2DjpS9n3u+55x71x9AAEAfxPQdDozzeGVkJaeB80X5nELFV8faYhXRm8YEYKcdAMZuOC9dfZ06DYF98zNgx8uUkZt7AUT+qcD5pzQIoFkhQJE0lRcrvQjsyDnA2o8UgDSbDZ+vB3ZSpDJyvglyCN7xbQPRoPBrBaQBN/0wsO5RABOXC0DqgZi8nbcWReT8k4GTnMGPT6sRRN2sSZB/D0kyb4PM6CABCLi4AzthlTJy0rOahdCo8HtSOxsRDOA4+vUEwo4MBnZmujLSndpYkdaa+BqTDIyr2+sDBAZPxhd/UBk538SOhVDPyvDn8T4OzNTDnR8IuEwENiBFGTk/HYNRoyvjJEnA+h7vvEDAFSH4n1FGkS6CqCu/JGC8fgKweadzAQEXN4SQrIx8h6MPQKhoF+Z5uPMAAccJwPgkK6NewaiZKT7HOgcQ1lN+1KGIegdDWVcQyqe79RsIOyKI71oUUW9hKGYV73hsiSfpBxAwNGlYo4OBm3FeblW+dbKEPxKJ16Mswc0lxcbE8RPdBAL9R2DX9JEqLsxG3URlVkdUCP27LIAFGcDMvwHMvGvAzL4MTGAa/+KUra+3noCRxgM7qki3gMCoQICpe1Qx4j5AeL4KQrNCQCFZ8o/n/QLMrIs4l6RWZ48egJEmADNU0vFAwCMWIDRHFWXFAGF5tTOitQqphkMzaO41YGec04viz3qd6jgg0A9tJ+SaKvLZkNV2CE3BQbgUDOd/tslLKF2YT8BtuXaBgIUDwIxkVQy/rbIaDYtZgGCCL9KbQjvGefw/Wcnp6Zg563ExTiCkaxjvoZ7U0GPW51QRxkus96nDnE/CCnbaga8Yu+HvoXqrH0gCsB//qD0g4BYD4HtUFcPuaAUEFfvmZ2J2vMyDm3thq8g/9TLnn/IMF4G0Wr6niUiaWoVKZaUXN7Ij53zM2o/srp6OCzPX4yowDss0DwTGrsRCXaaKobntqxMtzYrP1zuwkyLXYIt8EyE8x86LtAlEg8KvFZD2mJt++CLrHjUPJi63bDeUgBRgPwjSLBBwx4Ity1dFbYCYvN0BLWY155/8iJOcwcU7rUYQdbMmgfDfQ5Jcgja4kBkdZN1mIN5Y3Oeex5b/fc0AAUtPtKdHqhiqYRgu7ibshFWzOenZuxqF0Kjwe0pTr7AjgqeC42hR8wBwBqHyOqkUJ0kFsB2mfiBgagMgPaGK4TmarRMjg4ewM9NP4059xduJ1mHUkDT1OStN/o5xdbOvC0AxsIqwiBvOOgdGCy9Bz/DrYCzLAOOlmWCyLAuMZx0BY79dYCzZBaaBcSCydlIDkMAU+UyhiJqyqsGTWc7n4Fecb2JJh0KoZ2WJhPM+nslMPfyBEoJ/IhjOTgOTiEywWJcHFhvywXJTIVhGFoG4pqLvg3hzmVxbysFiVQaYr8mE7l+uBM7CtvVAwCsJYHGhKmrMoiYasgEpyzm/xBc6BaNGV8ZJkipY3+MeRiFXwHx1Lr/4vCiIliryLlhGFcu1uRS6jZG0EsjsdIBF2aqoCRiuCMP/TJTI59QrnYRRU35JfzJePwWCzTtMq0A0IvHWSrBYeQPMvljRPBD4UIbd1D1V1EhmuHVh/ZO38B2OrsNQSJL8jPU8HKwOILwwW3ptqwRLWXrjQMDhfawXBaqoCRiOE0SMT/JyvYKhypQXjM8xT7VBoYopBTPZBWDN+jQApN97AEuKVFET3ZTn6QD05md6B0NZV5IeMp/u/pc6oYgRiml4SgNApsVhzchRRXXDGBE0FLuXh3oLQzGreMdnM66T7NUHpQCLfTmYhaqgAHQ3x8Hvtiq2F4ChCYsyRPVAmcDoYFtuxvkrvFXR7sW3xuDHH4nE61GWJBJWemYPOH7Cqde+SsB6VVo1kLenyBdSEdui/iN6getHg2Fhtif+ORJnl1MYb6HKICT7GSzMIrAggzDzbxBm3jXCzL5MmMA0+uLQnxNrwNEDKNL4F+yoIvXWE76dzgeDfkMQyOI8+aIqYms0KnAkTN0TARH3z0J4/u/4d6RlQkAhWfKP5/1CmFkXCRuQWp09egBGmnCdGSrpo9Z6sqkYzHZiQ9WmjPCIHQmhOQdBVlyC0zwubCZpOYw6CqmGQzNo7jXCzjhX29Z0VKzXqa/VmiUbsZ7EPm4lkH7Z/SHk2k7Mhj/5Xd5WCE3BQbgUDOd/VnVcrpvzSTm4LbdSa5ZsLkMgIYpr0yZAWDhwMCP5Kwi/XaC0Gg2LWYBggtHKJEm6WV8kCYT9+McFas0SVPNA3GJMwffoWgi781QbIOplzLzrhJ2Zpns25oubxOPqdcZhmal6gSjfpNDAHfnYlSYQUbYXQnPbVyfabWOYMXN/Iaz/Gd2ysYCUp+wHQZ+oGUgjmeEeaw6y/JMdBqIhoY2xaGOcn47YmDcW97nno8H1fUazGWLpaQG+j2IhVIdg1LKxG2hj57XfJuMmYKm8TirFSVLTwXaYheZqiKkNC9ITERCe87fOwag5x/A2hm2yNEWjABSZKMIibjjrHDFaeIn0DL9OjGUZxHhpJjFZlvWH8awjh4z9di01luyabhoY5yKydmLVZ1mBKZNxtnipU1bVVLbw3dgl1cSvLhAUgn8iMZydRkwiMonFujxisSGf4KIRnKyJuKai7xNsWaleireUV1msysg2X5O5tPuXK9/mLGwNW5khWap3C3olWcPiwgKdB9FQm/z1DcLwNpbQ5vrCVn9el5kpxCjkCjFfncsvPi8KoqWKvEsso4qpnltuLo3tNkYypuVAFlW/8Y3G2elRsCib6CMQJZg51TbWGij4b0V+CaTbnDS0ohvEfG2efFFbC6IRibdWPrZYeSPG7IsVDs0DsR0sB/KhbBjI7pXrMwyVjWUQZtYl+VDZnC1JEonRgovEdEUOb0niKPWBqCXMmF7bKjMtZenjmgbS04reFIogrCBa72HU1fwMwgb+jPUlQVmgaWdEs6FrYArpEXaNmK+/o6wLlpGF6gdRVzGlf5jJLniwZn0abJUVN4ausKSosNMBqc4YZs5VwvifxWxIIt3mphPjiAx5gaa2tKlQ6xLHlD41DU+RNA5kWpwPLMrpfDAWyGWwOIdYYia8EZ1PekUVacaSWqUCYrm5vMosNOXT+pbV3dwIQm8f7VwQcKJflEUMl94ivWPuEsfvy8igfQ+I84EKMvCHcmK7/Ve+XbXcWNixYGJKSqxXpb1VG8jbU2zxhVTqP4hMXjQbjFffIbbb7pFBexHC/gdkENW+2nLYU0psthTL60ZHgeEzNT/RoN8QQ8UdO70pdNf7jMAh1jDiFu76QmK/q4QH4Ly/oh6EunJCYPbflZDe0XeJRQdBEW8qJmY7C6S4Kfh3odCWd62+1gYm5Cbpvvw26fNtMRkQW8ZDaAmImqIZNDCunPTdVm1j2oayEetJ7OOb3XyWWVttKeeBnNSfNlZ+nGMQnkNM1ubz2eBUbUvO+x+0CkRDGhBbSvpsltuYNjNGvLnsr55TNwXZr0nlDxdz9aI+0GxYlkuscBdT/+czQQ0Q6tnYPmpj90kvamPatK5tv53s8tY4YwqkUqdtadFNvkjbbL1HBmC35HKgQi3Z0JyNOcaVkb47tGdj4i0VVV1cPxhO70Ge6VrLSrNBhLZkvqGA9N9dSpywVXXWAoha2q+wsTLSh3ZjmzRsY3RI/eaqH60hL3UCxHz57MDbUnT17KCm2tB+GytXdmOabJHFkfd20Ayp7Oj6IArPJqbr8skbODvIi3RFh0NouBtDG6NDJU77mqgv+DXP0RqS3RG1garLkhz+xfXbXcK/aFofdA1EY92YdbWNqVdFxdSyjmpzkuZCs4kRzg42397jd5x89+kHiFr1BTOZbqRe0Wot+k/oTeFSbYCg2WCGtmSLncug/RU6URvUIcXZmBW1sfbXl5f0pnCs5o40bpJuWKRpT093Ez/AHajoFCDqnY2hjdl8W9zOY5TCp/TG0BoX7lf1ZcNNwoZmEeNVefxxBN1BbTnS0DfRzea0t1xuY/9us42V0RvDrhCWv6dd7zKpPtLoGnGLmK/PVxZpZx1pW7XeJuMm7NsGG8MMuSy/oJq07ksIzX3VFhAMzg49VuTxPTrtPnhLeg0hNJQxrbUxBBKnuMLtD7LizNa0rRzaEi3S9HjBMa5cK0ca+mpj9LShdwu6MauNRbPlQOyGMzAvYzmEZL9qapKm2dBVdov/ZIdYVcsqgGi+TW6uGxNvefC867uTVY8TgnEhg2DprwUNgeDCskmPlXn8mQ5fpA8Ii9z2obKMv78Rb6p5U1lArHY8PtflrXFWtf+PdFByGITeUoIwWJxNLDYUEDs6O+ztvC1rhwyV9GwsRn42Jo4pfWU8c++S+s86MT3Wg1lYfIFeh+I/fkXvHfTpSEPf6gu6zau+O+4Tqy3lhUbjgp0afDyTaeCRYc5HnpYpPklYPA2D+fF3Yr8mNazJJ8rZhR/ycTlU9URYMG2o/KrpOC+zJoGI/uHRte8+stZFKN6a1cHffu+/M29Mix6CyfpuM7A/+vcGl30PXgmLpwGr+m/VX86x94Na9ZhYxm+ryO4EWYn29ecgHHCEhVQbjL8H/qdohZHrCFGrH6QsGhdgYBd6yNflyNPSQfuFbqvdMA5U/uX0/f0l3V3fM2zXs99tv4kfhl/wZ+eDjwQLa9P88RA7qieVTnGl0m6O73JqeRh/F2uHrgO2Z810PfHyjvOBh8Iit1AuR5/9z+mHkrP9114YrpFfV9HLd83AAbsLl7gee3HV+dBj2roJC1+/nSXoJsT1+MvzfYK2Sk3HTu+p8V/o0nPIRHvrGZunYCquxx8iV4CgUEUVDnx7bcMPfY5r1Efrv/LIwKovZ+g01Grgvt9G4c74GnUQf7AbOOnfxdiZB0xaT6tQd5z2VaRh3Oh86MmUnsPc7XBNunW6X3D/OkpYBAGIIAGIAESQAEQAIkgAIgARJAARgAgSgAgSgAhABAlABCCCBCACEEFa1f8By05S239vussAAAAASUVORK5CYII="
>
<VersionHistory>
<Change Date="2023-08-23" Type="New">Added new endpoint get_excel_worksheet_autodetect so user doesnt need Address Range (Dynamically detect max rows and columns for data read.</Change>
<Change Date="2023-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="2021-11-11">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="Version 1" Url="https://graph.microsoft.com/v1.0" />
</ServiceUrls>
<Auths>
<Auth Type="OAuth" Name="UserCreds" Label="User Credentials" Desc="OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-register-app-v2."
HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=Default;Scope=[$Scope$];ScopeSeparator={space};"
TestEndpoint="get_drives">
<Notes>
<![CDATA[
<p>Firstly, login into Azure Portal and there create an OAuth application:
<p />
<ol>
<li>Go to <a target="_blank" href="https://portal.azure.com/#home"><b>Azure Portal</b></a> and login there.</li>
<li>Then go to <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview"><b>Azure Active Directory</b></a>.</li>
<li>On the left side click menu item <b><a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a></b></li>
<li>Then proceed with clicking <b>New registration</b>.</li>
<li>Enter a name for your application.</li>
<li>Select one of <b> Accounts in this organizational directory only</b> as supported account type to access data in your company.</li>
<li>In <b>Redirect URI</b> section leave option selected at <i>Web</i>.</li>
<li>In nearby textbox enter <b>https://login.microsoftonline.com/common/oauth2/nativeclient</b> as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth</li>
<li>Use this Redirect URI in <b>Redirect URL</b> grid row.</li>
<li>Copy <b>Application (client) ID</b> and paste it into API Connection Manager configuration grid in <b>Client ID</b> row.</li>
<li>Then click on <b>Endpoints</b> link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
<ul>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize</li>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token</li>
<li>https://login.microsoftonline.com/common/oauth2/v2.0/authorize</li>
<li>https://login.microsoftonline.com/common/oauth2/v2.0/token</li>
</ul>
</li>
<li>Close "Endpoints" and create a </i>Client Secret</i> by clicking <b>Certificates & secrets</b>.</li>
<li>Proceed by clicking <b>New client secret</b> and setting expiration period. Copy the client secret and paste it into configuration grid in <b>Client Secret</b> row.</li>
<li>Use these scopes in Scopes field: <b>offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile User.Read email</b></li>
<li>Click <b>Generate Token</b> to generate tokens.
<div style="background-color: #f5f2c4; border-style: solid; border-color: #000000; border-width: 1px; margin-top: 3px; margin-bottom: 6px; padding: 6px;">
<b>NOTE:</b> If you are planning to use your current data connection/token for automated processes, we recommend that you use a generic account for token generation when the login box appears (e.g. sales_automation@mycompany.com instead of bob_smith@mycompany.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will fail. Another potentially unwanted effect of using a personal token is incorrect logging; the API calls (e.g. Read, Edit, Delete, Upload) made with that token will record the specific user as performing the calls instead of an automated process.
</div>
</li>
<li>That's it!</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="AuthUrl" Label="Authorization URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/authorize;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/authorize" />
<Param Name="TokenUrl" Label="Token URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/token;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/token" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True"/>
<Param Name="RefreshTokenFilePath" Label="Refresh Token File Path" Hidden="True" Desc="If you cant fit long refresh token in ConnectionString from your program then use this. Supply three properies in json format (i.e. save this in file { "access_token": "YOUR_ACCESS_TOKEN", "refresh_token": "YOUR_REFRESH_TOKEN", "expires_in": 3600 } )"/>
<Param Name="Scope"
Required="True"
Options="offline_access;Files.Read;Files.Read.All;Files.ReadWrite;Files.ReadWrite.All;openid;profile;Sites.Read.All;Sites.ReadWrite.All;User.Read"
MultiSelectSeparator=" "
MultiSelect="True"
Value="offline_access~Files.Read~Files.Read.All~Files.ReadWrite~Files.ReadWrite.All~openid~profile~Sites.Read.All~Sites.ReadWrite.All~User.Read~"
Desc="Permissions you want to use."
Example="offline_access~Files.Read~Files.Read.All~openid~profile~Sites.Read.All~User.Read~"
HelpLink=""/>
<Param Name="ReturnUrl"/>
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
<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"/>
</Params>
</Auth>
<Auth Type="OAuth" Name="AppCreds" Label="Application Credentials" Desc="OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-v2-service"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-v2-service"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=ClientCredentials;Scope=[$Scope$];ScopeSeparator={space};"
TestEndpoint="get_main_site">
<Notes>
<![CDATA[<p>Firstly, login into Azure Portal and there create an OAuth application:
<p />
<ol>
<li>Go to <a target="_blank" href="https://portal.azure.com/#home"><b>Azure Portal</b></a> and login there.</li>
<li>Then go to <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview"><b>Azure Active Directory</b></a>.</li>
<li>On the left side click menu item <b><a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a></b></li>
<li>Then proceed with clicking <b>New registration</b>.</li>
<li>Enter a name for your application.</li>
<li>Select one of <b> Accounts in this organizational directory only</b> as supported account type to access data in your company.</li>
<li>In <b>Redirect URI</b> section leave option selected at <i>Web</i>.</li>
<li>Copy <b>Application (client) ID</b> and paste it into API Connection Manager configuration grid in <b>Client ID</b> row.</li>
<li>Then click on <b>Endpoints</b> link and copy second, Token, URL to Token URL field in configuration grid. Usually they look similar to these:
<ul>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token</li>
<li>https://login.microsoftonline.com/common/oauth2/v2.0/token</li>
</ul>
</li>
<li>Close "Endpoints" and create a </i>Client Secret</i> by clicking <b>Certificates & secrets</b>.</li>
<li>Proceed by clicking <b>New client secret</b> and setting expiration period. Copy the client secret and paste it into configuration grid in <b>Client Secret</b> row.</li>
<li>Go to <b>API Permissions</b> section and add Microsoft Graph permissions. Then and add Drive permissions, e.g. Files.Read.All, Files.ReadWrite.All.</li>
<li>Use these scopes in Scopes field: <b>offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile User.Read email</b></li>
<li>Finally, <b>Grant admin consent</b> for your domain for your permissions.</li>
<li>That's it!</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="TokenUrl" Label="Token URL" Required="True" Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/token;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/token" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True" Required="True"/>
<Param Name="Scope"
Required="False"
Hidden="True"
Value="https://graph.microsoft.com/.default"
Desc="Permissions you want to use."
Example="https://graph.microsoft.com/.default"
HelpLink="https://docs.microsoft.com/en-us/graph/auth-v2-service"/>
<!--below not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch" Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="5" Hidden="True" />
<Param Name="RetryMultiplyWaitTime" Value="True" Hidden="True" />
</Params>
</Auth>
</Auths>
<Template>
<EndPoint>
<Params>
<Param Name="DataFormat" Type="Property" Value="OData" />
</Params>
</EndPoint>
</Template>
<EndPoints>
<!-- =======================
DRIVES
============================ -->
<EndPoint Name="list_drives" Label="List Drives" Url="/me/drives" Filter="$.value[*]" Method="GET">
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="driveType" Label="DriveType" DataType="DT_WSTR" Length="60" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="quota.deleted" Label="QuotaDeleted" DataType="DT_I8" />
<Column Name="quota.remaining" Label="QuotaRemaining" DataType="DT_I8" />
<Column Name="quota.total" Label="QuotaTotal" DataType="DT_I8" />
<Column Name="quota.used" Label="QuotaUsed" DataType="DT_I8" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- =======================
DRIVE DETAILS
============================ -->
<EndPoint Name="get_drive" Label="Read Drive" Url="/drives/[$DriveId$]" Method="GET">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="500" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="500" />
<Column Name="driveType" Label="DriveType" DataType="DT_WSTR" Length="60" />
<Column Name="createdDateTime" Label="CreatedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="createdBy.user.displayName" Label="CreatedByUser" DataType="DT_WSTR" Length="56" />
<Column Name="lastModifiedDateTime" Label="LastModifiedAt" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedBy.user.displayName" Label="LastModifiedByUser" DataType="DT_WSTR" Length="48" />
<Column Name="lastModifiedBy.user.email" Label="LastModifiedByUserEmail" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.id" Label="LastModifiedByUserId" DataType="DT_WSTR" Length="144" />
<Column Name="quota.deleted" Label="QuotaDeleted" DataType="DT_I8" />
<Column Name="quota.remaining" Label="QuotaRemaining" DataType="DT_I8" />
<Column Name="quota.total" Label="QuotaTotal" DataType="DT_I8" />
<Column Name="quota.used" Label="QuotaUsed" DataType="DT_I8" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="300" />
<Column Name="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- ===========================
LIST ROOT
============================ -->
<EndPoint Name="list_root" Label="List Root" Url="/drives/[$DriveId$]/root/children" Method="GET">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a drive"
/>
<Param Name="Filter"
Type="Property"
Value="$.value[*]"
Options="All items=$.value[*];
Folders=$.value[?(@.folder)];
Files=$.value[?(@.file)]"
Required="true" />
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- ===========================
LIST FOLDER
============================ -->
<EndPoint Name="list_folder" Label="Lists Folder" Url="/drives/[$DriveId$]/items/[$FolderId$]/children" Method="GET">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a drive"
/>
<Param Name="FolderId"
Required="True"
Type="Placeholder"
Desc="Specify a folder"
/>
<Param Name="Filter"
Type="Property"
Value="$.value[*]"
Options="All items=$.value[*];
Folders=$.value[?(@.folder.childCount != null)];
Files=$.value[?(@.file.mimeType != null)]"
Required="true" />
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- ===========================
EXCEL FILES
============================ -->
<EndPoint Name="list_excel_files" Label="List Excel Files" Url="/drives/[$DriveId$]/search(q='.xlsx')" Filter="$.value[?(@.file)]" Method="GET">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Type="Placeholder"
Desc="Specify a drive"
/>
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="500" />
<Column Name="name" DataType="DT_WSTR" Length="150" />
<Column Name="size" DataType="DT_I8" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="webUrl" DataType="DT_WSTR" Length="1500" />
<Column Name="createdBy.user.email" Label="createdBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="createdBy.user.displayName" Label="createdBy_user_displayName" DataType="DT_WSTR" Length="100" />
<Column Name="lastModifiedBy.user.email" Label="lastModifiedBy_user_email" DataType="DT_WSTR" Length="150" />
<Column Name="lastModifiedBy.user.displayName" Label="lastModifiedBy_user_displayName" DataType="DT_WSTR" Length="150" />
<Column Name="parentReference.driveId" Label="parentReference_driveId" DataType="DT_WSTR" Length="400" />
<Column Name="parentReference.driveType" Label="parentReference_driveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="parentReference_id" DataType="DT_WSTR" Length="200" />
<Column Name="fileSystemInfo.createdDateTime" Label="fileSystemInfo_createdDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="fileSystemInfo.lastModifiedDateTime" Label="fileSystemInfo_lastModifiedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="folder.childCount" Label="folder_childCount" DataType="DT_I8" />
<Column Name="file.mimeType" Label="file_mimeType" DataType="DT_WSTR" Length="300" />
<Column Name="shared.scope" Label="shared_scope" DataType="DT_WSTR" Length="100" />
</OutputColumns>
</EndPoint>
<!-- ======================
EXCEL WORKSHEETS
======================= -->
<EndPoint Name="list_excel_worksheets" Label="List Excel Worksheets (Tabs)" Url="/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets" Method="GET" Filter="$.value[*].name">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id to search items in"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a drive Id to search items in"
/>
</Params>
<OutputColumns>
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="120" />
</OutputColumns>
</EndPoint>
<EndPoint Name="get_excel_worksheet_autodetect" Label="Read Excel Worksheet (Auto Detect Range)"
Url="/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets('[$SheetId$]')/usedRange(valuesOnly=[$AutoDetectByValue$])" Method="GET" Filter="$.values[*]"
Desc="Use this endpoint to read data from a Sheet without supplying address range (e.g. A1:D100). This endpoint auto detects address range based on used cells in a sheet."
HelpLink="https://learn.microsoft.com/en-us/graph/api/worksheet-usedrange?view=graph-rest-1.0"
>
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id to search items in"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="SiteId=[$SiteId$];DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id"
/>
<Param Name="SheetId"
Required="True"
OptionsEndPoint="list_excel_worksheets"
OptionsEndPointValueColumn="Name"
OptionEndPointParameters="DriveId=[$DriveId$];FileId=[$FileId$]"
Type="Placeholder"
Desc="Specify a sheet id or name"
/>
<Param Name="AutoDetectByValue"
Desc="Auto detect cell range based on values only and do not consider cell formatting (for empty values) to detect the range."
Value="true"
Options="true;false"
/>
<Param Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" />
<Param Name="ArrayTransEnableCustomColumns" Label="First Row Has Column Names" Required="True" Type="Property" Value="True" Desc="Detect column names based on first row in supplied range." />
</Params>
</EndPoint>
<EndPoint Name="get_excel_worksheet" Label="Read Excel Worksheet" Url="/drives/[$DriveId$]/items/[$FileId$]/workbook/worksheets('[$SheetId$]')/range(address='[$Range$]')" Method="GET" Filter="$.values[*]">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
OptionsEndPoint="list_excel_files"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
OptionEndPointParameters="DriveId=[$DriveId$];"
Type="Placeholder"
Desc="Specify a file id or name"
/>
<Param Name="SheetId"
Required="True"
OptionsEndPoint="list_excel_worksheets"
OptionsEndPointValueColumn="Name"
OptionEndPointParameters="DriveId=[$DriveId$];FileId=[$FileId$]"
Type="Placeholder"
Desc="Specify a sheet id or name"
/>
<Param Name="Range" Type="Placeholder" Required="True" Value="a1:j100" />
<Param Name="ArrayTransformType" Type="Property" Value="TransformColumnslessArray" />
<Param Name="ArrayTransEnableCustomColumns" Label="First Row Has Column Names" Required="True" Type="Property" Value="True" Desc="Detect column names based on first row in supplied range." />
</Params>
</EndPoint>
<!-- ===========================
DOWNLOAD A File
=========================== -->
<EndPoint Name="download_file" Label="Download File" Url="/drives/[$DriveId$]/items/[$FileId$]/content" Method="GET">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="FileId"
Required="True"
Type="Placeholder"
Desc="Specify a file id"
MultiSelectLimit="1000"
/>
<Param Name="SaveContentAsBinary"
Required="True"
Type="Property"
Hidden="True"
Value="True"
/>
<Param Name="RequestTimeoutMs"
Type="Property"
Desc="Timeout in milliseconds after which download stops"
Value="7200000"
/>
<Param Name="FileOverwriteMode"
Type="Property"
Value="AlwaysOverwrite"
Required="True"
/>
<Param Key="ResponseDataFile"
Name="TargetFilePath"
Desc="Specify a disk file path to save file as"
Required="True"
Type="Property"
Value=""
Editor="FileSave"
/>
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Downloaded'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
<!-- ===========================
UPLOAD A FILE
=========================== -->
<EndPoint Name="upload_file" Label="Upload File"
Url="/drives/[$DriveId$]/items/root:/[$TargetFilePath$]:/createUploadSession"
Method="POST"
Desc="Don't forget to set up">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="SourceFilePath"
Required="True"
Type="Placeholder"
Desc="Specify a disk file path"
Example="C:\MyParentFolder\Subfolder\file.txt"
Value=""
Editor="FileOpen"
/>
<Param Name="TargetFilePath"
Required="True"
Type="Placeholder"
Desc="Specify Target file path"
Example="MyParentFolder/Subfolder/file.txt"
Value=""
/>
</Params>
<EndPoint Name="upload_part"
Url="[$parent.[0].uploadUrl$]"
Method="PUT" Body="[$SourceFilePath$]">
<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>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="150" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="300" />
<Column Name="size" Label="Size" DataType="DT_I8" />
<Column Name="parentReference.path" Label="Path" DataType="DT_WSTR" Length="1000" />
<Column Name="webUrl" Label="WebUrl" DataType="DT_WSTR" Length="500" />
<Column Name="parentReference.driveId" Label="DriveId" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.driveType" Label="DriveType" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.id" Label="ParentRefId" DataType="DT_WSTR" Length="100" />
<Column Name="parentReference.name" Label="ParentRefName" DataType="DT_WSTR" Length="100" />
<Column Name="createdBy.user.id" Label="CreatedBy" DataType="DT_WSTR" Length="100" />
<Column Name="createdDateTime" Label="CreatedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="file.mimeType" Label="MimeType" DataType="DT_WSTR" Length="100" />
<Column Name="file.hashes.quickXorHash" Label="HashQuickXor" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha1Hash" Label="HashSha1" DataType="DT_WSTR" Length="200" />
<Column Name="file.hashes.sha256Hash" Label="HashSha256" DataType="DT_WSTR" Length="200" />
</OutputColumns>
</EndPoint>
<!-- ===========================
DELETE AN ITEM
=========================== -->
<EndPoint Name="delete_item" Label="Delete Item" Url="/drives/[$DriveId$]/items/[$ItemId$]" Method="DELETE">
<Params>
<Param Name="DriveId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="ItemId"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
Type="Placeholder"
Desc="Specify a drive Id"
/>
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Deleted'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Drives" SelectEndPoint="list_drives" />
</Tables>
<Examples>
<Example Group="ODBC"
Label="List drives"
Code="SELECT * FROM Drives"
Desc="Lists all drives">
</Example>
<Example Group="ODBC" Label="Get drive" Desc="Gets info about drive">
<Code>
<![CDATA[SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726XXXXY5F2BBNi14')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List root" Desc="Lists items at a root level">
<Code>
<![CDATA[SELECT * FROM list_root
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa')
--You can get DriveId by selecting from 'Drives' table.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List items in a folder" Desc="Lists items in a folder">
<Code>
<![CDATA[SELECT * FROM list_folder
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa',
FolderId='atLNa26LE0eYFa2BeNi1awa')
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List folders in a folder" Desc="Lists folders in a folder">
<Code>
<![CDATA[SELECT * FROM list_folder
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa',
FolderId='atLNa26LE0eYFa2BeNi1awa',
Filter='$.value[?(@.folder)]')
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List files in a folder" Desc="Lists files in a folder">
<Code>
<![CDATA[SELECT * FROM list_folder
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wa',
FolderId='atLNa26LE0eYFa2BeNi1awa',
Filter='$.value[?(@.file)]')
--You can get DriveId by selecting from 'Drives' table.
--You can get FolderId by selecting from 'list_root' table OR selection from 'list_folder'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List Excel files" Desc="Lists Excel files in a specified drive">
<Code>
<![CDATA[SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14')
--You can get DriveId by selecting from 'list_drives' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List Excel file Worksheets" Desc="Enumerates Worksheets that are part of Excel file">
<Code>
<![CDATA[SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_excel_files' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Excel Worksheet data - Auto Detect Range" Desc="Reads an Excel file without supplying Cell Address Range (It auto detects based on first and last cell)">
<Code>
<![CDATA[SELECT * FROM get_excel_worksheet_autodetect
WITH (
DriveId='b!GtLN726LE-XXXXXXXXXXX'
,FileId='01SUOJPFXXXXXXXXXXXXXXXXXX'
,SheetId='Sheet1' --Id or Name
,AutoDetectByValue='true'
,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Excel Worksheet data for a specified Cell Address Range" Desc="Reads an Excel file from a specified Cell Range. To automatically read without cell range use other endpoint get_excel_worksheet_autodetect">
<Code>
<![CDATA[SELECT * FROM get_excel_worksheet
WITH (
DriveId='b!GtLN726LE-XXXXXXXXXXX'
,FileId='01SUOJPFXXXXXXXXXXXXXXXXXX'
,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" Desc="Uploads a file to a OneDrive">
<Code>
<![CDATA[SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,SourceFilePath='C:\My files\Employees\List.xls'
,TargetFilePath='Uploads/Employees/List.xls')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Download a file" Desc="Downloads a file from OneDrive">
<Code>
<![CDATA[SELECT * FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX'
,TargetFilePath='C:\My files\Employees\List.xlsx')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_files' endpoint.]]>
</Code>
</Example>
<Example Group="ODBC" Label="Delete a file/folder" Desc="Deletes a file or folder in OneDrive">
<Code>
<![CDATA[SELECT * FROM delete_item
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,ItemId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'list_drives' endpoint.
--FileId can be retrieved by selecting from 'list_folder' or 'list_root' endpoints.]]>
</Code>
</Example>
</Examples>
</ApiConfig>
