OneDrive Connector - Source Code
<?xml version="1.0" encoding="utf-8"?>
<ApiConfig Name="OneDrive"
Slug="onedrive-connector"
Id="b81fa083-78c9-461c-ad2a-5490446c9f43"
EngineVersion="17"
Version="8"
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 Ver="8" Date="2025-01-30" Type="New">Sorted Files and Folders in Dropdown selection on UI for many endpoints, also included full path rather than name.</Change>
<Change Ver="8" 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="8" Date="2025-01-30" Type="New">Reduced Folder selection dropdown limit to 200 items to load UI faster.</Change>
<Change Ver="8" Date="2025-01-30" Type="New">Added new endpoint copy_item to copy file or folder.</Change>
<Change Ver="8" Date="2025-01-30" Type="New">Added new endpoint move_rename_item to move or rename file or folder.</Change>
<Change Ver="8" Date="2025-01-30" Type="New">Added ConvertTo option to download_file endpoint to convert supported files to PDF or HTML format.</Change>
<Change Ver="7" Date="2024-12-17" Type="New">Added support for Test connection (Previously it was not doing correct way).</Change>
<Change Ver="7" Date="2024-12-17" Type="New">Added support for "App Credentials" authentication (You can now List/Read/Write files from other User's or Group's Drive (Shared Drive)).</Change>
<Change Ver="7" Date="2024-12-17" 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="7" Date="2024-12-17" Type="Modified">Updated authentication instructions with explanatory images.</Change>
<Change Ver="6" 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="6" Date="2024-10-22" Type="New">Show additional columns for list_excel_worksheets (new columns are Id, Position, Visible).</Change>
<Change Ver="6" 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 rename of tab with not getting error.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added a new parameter ContinueOn404Error in many endpoints to allow 404 file not found error.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added a new tables FileReaderWriterCsv / FileReaderWriterJson to support CSV / JSON data Read/Write scenarios.</Change>
<Change Ver="6" 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="6" 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="6" Date="2024-10-22" Type="New">Added retry on error code 423 (File is locked).</Change>
<Change Ver="6" 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="6" 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="6" Date="2024-10-22" Type="New">Added a new connection level options (ExtraHeaders and SearchOptionForNonIndexedFields).</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added a new SearchPath option for list_files, list_folders, list_excel_files endpoints.</Change>
<Change Ver="6" 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="6" Date="2024-10-22" Type="New">Added a new OrderBy parameter for list_files, list_folders, list_excel_files endpoints.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added a new endpoint list_files for recursive / non-recursive scan.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Allow Lookup Operation (By ID Param) in Files Table (e.g. SELECT * FROM Files WHERE Id='Abxxx12').</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Allow File/Folder ID or Path in most endpoints.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added an option to select See Shared DriveId (Dropdown on UI) along with User DriverId (me) in list_files / list_folder and many other endpoints.</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added an option to list files / folders Shared With Me (new SearchType option).</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added new tables Files and Folders (default DriveId=me).</Change>
<Change Ver="6" Date="2024-10-22" Type="New">Added a new Default DriveId (default=me) parameter at connection level.</Change>
<Change Date="2023-08-23" Type="New">Added new endpoint get_excel_worksheet_autodetect so user doesnt need Address Range (Dynamically detect max rows and columns for data read.</Change>
<Change Date="2023-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="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};"
TestEndPoint="list_drives" TestParameters="GroupOrUserId=/me">
<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="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 properties 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="User.Read.All;Group.Read.All;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="https://learn.microsoft.com/en-us/graph/permissions-reference"
/>
<Param Name="ReturnUrl" Label="Return URL" Value="https://zappysys.com/oauth" Options="https://zappysys.com/oauth"/>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="DriveId" Label="Default Drive Id (Select after clicking **Generate Token**)"
Value="me"
OptionsExtra="My Drive=me"
OptionsEndPoint="list_drives"
OptionsEndPointParameters="GroupOrUserId=[$GroupOrUserId$]"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
/>
<!--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="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"/>
<Param Name="SearchOptionForNonIndexedFields" Type="Header" Label ="Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)" Key="Prefer" AllowDuplicateHeader="True" 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>:
To automate your company's processes, 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>
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>
When configuration window opens, configure these fields:
<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>
After registering the app, copy the <strong>Application (client) ID</strong> for later:
<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>
Then copy <strong>OAuth authorization endpoint (v2)</strong> & <strong>OAuth token endpoint (v2) URLs</strong> to use later in the configuration:
<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>
Now go to SSIS package or ODBC data source and use the copied values in <strong>User Credentials</strong> authentication configuration:
<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>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="list_drives" TestParameters="GroupOrUserId=/"
>
<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="GroupOrUserId" Template="T_GroupOrUserIdParam" Required="True" />
<Param Name="DriveId" Label="Default Drive Id"
Required="True"
OptionsEndPoint="list_drives"
OptionsEndPointParameters="GroupOrUserId=[$GroupOrUserId$]"
OptionsEndPointValueColumn="Id"
OptionsEndPointLabelColumn="Name"
/>
<!--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" Label ="Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)" Key="Prefer" AllowDuplicateHeader="True" 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 below to create Microsoft Entra ID application with application access permissions. Using following steps, you can grant very granular app permissions to access Full site(s), List(s), List Item(s) and File(s). Choose permission based on your need.
<p/>
<h3>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>
When configuration window opens, configure these fields:
<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>
After registering the app, copy the <strong>Application (client) ID</strong> for later:
<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>
Then copy <strong>OAuth authorization endpoint (v2)</strong> & <strong>OAuth token endpoint (v2) URLs</strong>:
<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>
Continue and create <strong>Client secret</strong>:
<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>
Then copy the <strong>Client secret</strong> for later steps:
<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>
Continue by adding permissions for the app by going to the <strong>API permissions</strong> section, and clicking on <strong>Add a permission</strong>:
<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>
Select <strong>Microsoft Graph</strong>:
<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>
Then choose <strong>Application permissions</strong> option:
<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>
Continue by adding these <strong>Files</strong> (OneDrive) permissions (Just search for "Site" and then select desired permissions):
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/User-Credentials/select-onedrive-scopes.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select OneDrive scopes"
title="Selecting OneDrive scopes"
width="1000"
height="300" />
</li>
<li>
Move on by addding these <strong>Sites</strong> (SharePoint) permissions (You can search for "Site" and then select desired permissions):
<img src="https://cdn.zappysys.com/api/Images/authentication/microsoft/App-Credentials/select-sharepoint-online-application-scopes.png"
loading="lazy"
decoding="async"
class="img-thumbnail block"
alt="Select SharePoint Online application scopes"
title="Selecting SharePoint Online application scopes"
width="1000"
height="300" />
</li>
<li>
Finish by clicking <strong>Add permissions</strong> button:
<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>
Now it's time to <strong>Grant admin consent</strong> for your application:
<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>
Confirm all the permissions are granted:
<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>
Now go to SSIS package or ODBC data source and use the copied values in <strong>Application Credentials</strong> authentication configuration:
<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! Let's move on to the next step.
</li>
</ol>
<h3>
Step-2 (Method#1 - New Approach): Grant permission to app using permission API (UI is not available yet)
</h3>
<p>
In year 2024 microsoft launched <pre>*.Selected</pre> permission scopes and new APIs to grant granular permissions to the resource for app access scenario. Unfortunatly, there is no User interface available yet to control this access so you have to call Admin API or use PowerShell script to control this.
<a target="_blank" href="https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=http" class="font10px-force">[Click here to learn more]</a>.
Follow these simple steps to configure permissions for app we created earlier (steps assume you are the owner of the resource and like to grant some permissions e.g. read, write, full, owner):
</p>
<ol>
<li>Open PowerShell (Run as Admin user)</li>
<li>Install Graph API module by running this command <pre><code>Import-Module Microsoft.Graph.Sites</code></pre> </li>
<li>Now you can call following PowerShell code to set "read" and "write" permission for the app we created earlier (assuming app id is 89ea5c94-7736-4e25-95ad-3fa95f62b66e).
<pre><code>
#More info https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
#Step-1: Install module if not found
#Install-Module Microsoft.Graph.Sites
#Step-2: set permission for app 89xxx6e and site zappysys.xx.efcdd21xxxxe2
Import-Module Microsoft.Graph.Sites
#Step-3: Login
Connect-MgGraph
#Step-4: Set Parameters for API Call (set Permissions, SiteId and AppId - Replace under #TODO)
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">)
$siteId="yourcompany.sharepoint.com,efcddxxxxxxx104d8b5e3,8c9c6xxxxxxxx84e2"
#--OR-- simple id
#$siteId="efcddxxxxxxx104d8b5e3"
$params = @{
roles = @(
#possible options are read, write, fullcontrol, owner (write includes read too)
#TODO: Change here
"write"
)
grantedTo = @{
application = @{
#find this Client Id (Application Id) from the Azure Portal - Application page (https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade)
#TODO: Change here
id = "c3e9xxxxxx-xxxxx-xxxxx-xxxx-xxxxxb30c1"
}
}
}
#Step-5: Grant Permission
New-MgSitePermission -SiteId $siteId -BodyParameter $params
</code>
</pre>
</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_GroupOrUserIdParam"
Label="Default Group or User Id (additional Scopes needed to list - If fails enter manually)"
Desc="To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}"
Options ="My self (Not Valid for Application Credentials)=;For any group=/groups/ENTER-GROUP-EMAIL-OR-ID;For any user=/users/ENTER-USER-EMAIL-OR-ID"
OptionsExtra="My self (Not Valid for Application Credentials)=;For any group=/groups/ENTER-GROUP-EMAIL-OR-ID;For any user=/users/ENTER-USER-EMAIL-OR-ID"
OptionsEndPoint="get_groups;get_users"
OptionsEndPointValueColumn="IdPath"
OptionsEndPointLabelColumn="DisplayName"
OptionsEndPointContinueOnError="True"
/>
<EndPoint Name="ODataGeneralOptions">
<Params>
<Param Name="OrderBy" Key="$orderby" Label="Order By" Type="Query"
Options="None=;AscendingOrder=SomeColumn asc;DescendingOrder=SomeColumn desc;"
DataType="DT_WSTR" Length="200"
Desc="Order the specified column by ascending or descending order"
HelpLink="https://zappysys.com/links?url=https://learn.microsoft.com/en-us/graph/query-parameters" />
<Param Name="SearchCriteria" Key="$filter" Label="Search Criteria" Type="Query"
Options="None=;Equal Search=SomeColumn eq 'SomeValue';Substring Search=substringof(SomeField,'abc');Starts With=startswith(SomeField, 'abc')"
Desc="Data filter (e.g. somecolumn -eq 'somevalue')"
HelpLink="https://zappysys.com/links?url=https://learn.microsoft.com/en-us/graph/query-parameters" />
<Param Name="PageSize" Key="$top" Label="Page Size" Type="Query" Value="100"
DataType="DT_I4" Desc="The number of records to include in each page of results"
HelpLink="https://zappysys.com/links?url=https://learn.microsoft.com/en-us/graph/query-parameters" />
</Params>
</EndPoint>
<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_DriveIdParam"
Label="Drive Id / Shared Folder"
Required="True"
OptionsEndPoint="list_drives;list_files"
OptionsEndPointValueColumn="id;remoteItem_parentReference_driveId"
OptionsEndPointLabelColumn="name,OwnerEmail;remoteItem_sharepointIds_siteUrl"
OptionsEndPointParameters="GroupOrUserId=[$GroupOrUserId$];SearchType=sharedWithMe;Filter=$.value[*]"
OptionsEndPointDistinct="True"
OptionsEndPointContinueOnError="True"
Type="Placeholder"
Desc="Specify a drive"
OptionsExtra="My Drive (Only Valid for User Credentials)=me"
Order="1"
/>
<Param Name="T_SearchTypeParam" Label="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;
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="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_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="GroupOrUserId=[$GroupOrUserId$];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"
InvokeOrder="2"
/>
<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="GroupOrUserId=[$GroupOrUserId$];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="GroupOrUserId=[$GroupOrUserId$];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="GroupOrUserId=[$GroupOrUserId$];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="GroupOrUserId=[$GroupOrUserId$];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_FolderIdParam"
Label="Folder Id or Path (e.g. root: -OR- root:/myfolder: ) - Max 200 items listed"
OptionsExtra="Example#1 (Folder Id)=01R65Qxxxxxxx123;
Example#2 (Root)=root;
Example#3 (Sub Folder Path)=root:/myfolder/subfolder:"
OptionsEndPoint="list_files"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="webUrlPath,name"
OptionsEndPointSortBy="webUrlPath"
OptionsEndPointParameters="GroupOrUserId=[$GroupOrUserId$];SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];SearchPath=[$SearchPath$]"
Desc="Specify a Folder Id -OR- Path. Syntax: [{folder-id}] -OR- [root:/{folder-path}:] (Example: root:/myfolder/subfolder: )"
Type="Placeholder"
Value="root"
MultiSelectLimit="200"
ValueTemplate="<<{$value$}|~|STARTSWITH|~|root:|~|{$value$}|~|items/{$value$},FUN_IF>>"
Order="4"
/>
<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="GroupOrUserId=[$GroupOrUserId$];SearchType=[$TargetSearchType$];DriveId=[$TargetDriveId$];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="GroupOrUserId=[$GroupOrUserId$];SearchType=[$TargetSearchType$];SearchPath=[$TargetSearchPath$];DriveId=[$TargetDriveId$];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>
</Template>
<EndPoints>
<!-- =======================
DRIVES
============================ -->
<EndPoint Name="list_drives" Label="List Drives" Url="[$GroupOrUserId$]/drives" Filter="$.value[*]" Method="GET">
<Params>
<Param
Name="GroupOrUserId"
Template="T_GroupOrUserIdParam"
Value="/me"
Options ="Myself (only use for User Credentials)=/me;For any group=/groups/ENTER-GROUP-EMAIL-OR-ID;For any user=/users/ENTER-USER-EMAIL-OR-ID"
OptionsExtra="Myself (only use for User Credentials)=/me;For any group=/groups/ENTER-GROUP-EMAIL-OR-ID;For any user=/users/ENTER-USER-EMAIL-OR-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="[$GroupOrUserId$]/drives/[$DriveId$]" Method="GET">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="@odata.context" Label="OdataContext" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- ===========================
LIST ROOT
============================ -->
<EndPoint Name="list_root" Label="List Root (Non Recursive)" Template="ItemOutput" Url="[$GroupOrUserId$]/drives/[$DriveId$]/root/children" Method="GET" Filter="$.value[*]" >
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="Filter" Template="T_FilterParam" Required="true" />
</Params>
</EndPoint>
<!-- ===========================
LIST FOLDER
============================ -->
<EndPoint Name="list_folder" Label="Lists Folder Items (Non-Recursive Children)" Filter="$.value[*]"
Template="ItemOutput" Url="[$GroupOrUserId$]/drives/[$DriveId$]/[$FolderId$]/children" Method="GET">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="Filter" Template="T_FilterParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="FolderId" Template="T_FolderIdParam" Required="True" />
</Params>
</EndPoint>
<!-- ===========================
FILES LIST
============================ -->
<EndPoint Name="list_files" Label="List Files" Template="ItemOutput" Url="[$GroupOrUserId$]/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="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam" />
<Param Name="SearchType"
Label="Search Type (Default=Recursive)"
Value="search(q='')"
VisibleIf="GroupOrUserId=="
Options="Recursive - All Files (Blank) - For User Creds Only=search(q='');
Non-Recursive / Children - All Files - For Non-Shared Items Only=children;
Shared With Me (For DriveId 'me') - For User Creds Only=sharedWithMe;
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="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>>"
InvokeOrder="1"
/>
<Param Name="SearchPath" Label="Search Path (Default=Root Folder) - Max 200 listed"
VisibleIf="SearchType!=sharedWithMe"
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="GroupOrUserId=[$GroupOrUserId$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];OrderBy=[$OrderBy$]"
ValueTemplate="<<[$SearchType$]|~|Eq|~|sharedWithMe|~||~|{$value$},FUN_IF>>"
Value="/root"
MultiSelectLimit="200"
InvokeOrder="2"
/>
<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="[$GroupOrUserId$]/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="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam"/>
<Param Name="SearchType" Label="Search Type (Default=Recursive)" Value="search(q='.xls')"
Options="Recursive=search(q='.xls');Non-Recursive (Only for Non-Shared Items)=children;Shared With Me (For DriveId 'me')=sharedWithMe"
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)."/>
<Param Name="SearchPath" Label="Search Path (Default=Root Folder)" VisibleIf="SearchType!=sharedWithMe"
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="SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.folder.childCount != null)];OrderBy=[$OrderBy$]"
ValueTemplate="<<[$SearchType$]|~|Eq|~|sharedWithMe|~||~|{$value$},FUN_IF>>"
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>
<!-- GET USERS -->
<EndPoint Name="get_users"
Label="Get Users"
Url="/users"
Method="GET"
Filter="$.value[*]"
Desc="Get the list of users."
Template="ODataGeneralOptions"
HelpLink="https://learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-1.0">
<Params>
<Param Name="DataFormat" Type="Property" Value="OData" />
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Label="Id" Length="255" />
<Column Name="displayName" DataType="DT_WSTR" Label="DisplayName" Length="500" />
<Column Name="description" DataType="DT_WSTR" Label="Description" Length="4000" />
<Column Name="businessPhones" DataType="DT_WSTR" Label="BusinessPhones" Length="4000" />
<Column Name="givenName" DataType="DT_WSTR" Label="GivenName" Length="80" />
<Column Name="jobTitle" DataType="DT_WSTR" Label="JobTitle" Length="200" />
<Column Name="mail" DataType="DT_WSTR" Label="Mail" Length="200" />
<Column Name="mobilePhone" DataType="DT_WSTR" Label="MobilePhone" Length="500" />
<Column Name="officeLocation" DataType="DT_WSTR" Label="OfficeLocation" Length="500" />
<Column Name="preferredLanguage" DataType="DT_WSTR" Label="PreferredLanguage" Length="100" />
<Column Name="surname" DataType="DT_WSTR" Label="SurName" Length="80" />
<Column Name="userPrincipalName" DataType="DT_WSTR" Label="UserPrincipalName" Length="200" />
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" Label="CreatedDateTime" />
<Column Name="deletedDateTime" DataType="DT_DBTIMESTAMP" Label="DeletedDateTime" />
<Column Name="id" DataType="DT_WSTR" Label="IdPath" Length="255" ValueTemplate="/users/{$value$}"/>
</OutputColumns>
</EndPoint>
<EndPoint Name="get_groups"
Label="Get Groups"
Url="/groups"
Method="GET"
Filter="$.value[*]"
Desc="Get the list of groups."
Template="ODataGeneralOptions"
HelpLink="https://learn.microsoft.com/en-us/graph/api/group-list?view=graph-rest-1.0">
<Params>
<Param Name="DataFormat" Type="Property" Value="OData" />
</Params>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Label="Id" Length="255" />
<Column Name="displayName" DataType="DT_WSTR" Label="DisplayName" Length="500" />
<Column Name="description" DataType="DT_WSTR" Label="Description" Length="4000" />
<Column Name="mail" DataType="DT_WSTR" Label="Mail" Length="200" />
<Column Name="id" DataType="DT_WSTR" Label="IdPath" Length="255" ValueTemplate="/groups/{$value$}"/>
<Column Name="createdDateTime" DataType="DT_DBTIMESTAMP" Label="CreatedDateTime" />
<Column Name="deletedDateTime" DataType="DT_DBTIMESTAMP" Label="DeletedDateTime" />
</OutputColumns>
</EndPoint>
<!-- ======================
EXCEL WORKSHEETS
======================= -->
<EndPoint Name="list_excel_worksheets"
Label="List Excel Worksheets of a File (i.e. List Tabs)"
Url="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/workbook/worksheets"
Method="GET" Filter="$.value[*]">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="SearchType=[$SearchType$];DriveId=[$DriveId$];Filter=$.value[?(@.file.mimeType != null)];SearchType=search(q='.xls');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="[$GroupOrUserId$]/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="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="/drives/[$DriveId$]/[$FileId$]/workbook/worksheets('[$SheetId$]')/range(address='[$Range$]')" Method="GET" Filter="$.values[*]">
<Params>
<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="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="GET" ResponseFormat="Csv"
>
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="PUT" RequestFormat="Csv" BatchSize="10000000"
MetaDetectionOrder="StaticDynamicVirtual"
ContentType="text/plain"
Body="{$rows$}"
>
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="GET" ResponseFormat="Json"
>
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/content"
Method="PUT" RequestFormat="Json" BatchSize="10000000"
MetaDetectionOrder="StaticDynamicVirtual"
ContentType="application/json"
Body="{$rows$}"
>
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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 A File
=========================== -->
<EndPoint Name="download_file" Label="Download File"
Url="[$GroupOrUserId$]/drives/[$DriveId$]/[$FileId$]/content" Method="GET">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="ConvertTo" Type="Query" Key="format" Label="Convert to new format (e.g. pdf or html)"
Desc="Use this option to download file and convert to another format (e.g. download excel file as pdf). See https://learn.microsoft.com/en-us/graph/api/driveitem-get-content-format?view=graph-rest-1.0 for more information. Supported Options are Convert to PDF from [csv, doc, docx, odp, ods, odt, pot, potm, potx, pps, ppsx, ppsxm, ppt, pptm, pptx, rtf, xls, xlsx]. Convert to HTML from supported formats are [loop, fluid, wbtx]"
Options="Do not convert=;Convert To PDF=pdf;Convert To HTML (source file must be *.loop, *.fluid, *.wbtx)=html"
/>
<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="[$GroupOrUserId$]/drives/[$DriveId$]/items/root:/[$TargetFilePath$]:/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="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<Param Name="DriveId" Template="T_DriveIdParam"/>
<Param Name="SourceFilePath"
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="TargetFilePath"
Required="True"
Label="Target File Path / 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 or exising file Id (for Overwrite scenario)"
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" : "<<[$SourceFilePath,FUN_FILE_CREATE_DATE$],FUN_TO_UTC_DATETIME>>", "lastModifiedDateTime": "<<[$SourceFilePath,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="[$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>
</EndPoint>
<!-- ===========================
GET AN ITEM
=========================== -->
<EndPoint Name="get_item" Label="Get Item (File or Folder)" Template="ItemOutput" Url="[$GroupOrUserId$]/drives/[$DriveId$]/[$ItemId$]" Method="GET">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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" />
</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="[$GroupOrUserId$]/drives/[$DriveId$]/[$ItemId$]" Method="DELETE">
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="SearchType" Template="T_SearchTypeParam" />
<Param Name="SearchPath" Template="T_SearchPathParam" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{Status:'Deleted'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<InputColumns>
<Column Name="ItemId" Label="Id" Key="True" MapToParam="True" />
</InputColumns>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" />
</OutputColumns>
</EndPoint>
<!-- ===========================
MOVE AN ITEM
=========================== -->
<EndPoint Name="move_rename_item" Label="Move or rename item (File or Folder)" Url="[$GroupOrUserId$]/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"
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="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="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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="TargetDriveId" Template="T_DriveIdParam" Label="Target Drive Id (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="[$GroupOrUserId$]/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="driveId" src="TargetDriveId" />
<map name="id" src="TargetFolderId" />
</map>
<map name="@microsoft.graph.conflictBehavior" src="ConflictBehavior" />
</settings>]]>
</LayoutMap>
<Params>
<Param Name="GroupOrUserId" Template="T_GroupOrUserIdParam" />
<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 copy" Required="True" Desc="Specify an item Id you like to copy" />
<Param Name="TargetDriveId" Template="T_DriveIdParam" 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="Drives" SelectEndPoint="list_drives" Order="0" />
<Table Name="Files" SelectEndPoint="list_files" DeleteEndPoint="delete_item" LookupEndPoint="get_item" Order="1"/>
<Table Name="Folders" SelectEndPoint="list_files" DeleteEndPoint="delete_item" Order="2">
<Params>
<Param Name="Filter" Value="$.value[?(@.folder.childCount != null)]" />
</Params>
</Table>
<Table Name="FilesFolders" SelectEndPoint="list_files" DeleteEndPoint="delete_item" Order="3">
<Params>
<Param Name="Filter" Value="$.value[*]" />
</Params>
</Table>
<Table Name="SharedWithMe" Label="SharedWithMe (Not valid for Application Creds or other user)" SelectEndPoint="list_files" DeleteEndPoint="delete_item" Order="4">
<Params>
<Param Name="Filter" Value="$.value[*]" />
<Param Name="SearchType" Value="sharedWithMe" />
</Params>
</Table>
<Table Name="FileReaderWriterCsv" Label="CSV File Read/Create/Update" Order="5" SelectEndPoint="get_csv_file" InsertEndPoint="create_csv_file" UpdateEndPoint="create_csv_file" />
<Table Name="FileReaderWriterJson" Label="JSON File Read/Create/Update" Order="6" SelectEndPoint="get_json_file" InsertEndPoint="create_json_file" UpdateEndPoint="create_json_file" />
</Tables>
<Examples>
<Example Group="ODBC"
Default="True"
Label="List All Files (Recursive)"
Code="SELECT * FROM Files"
Desc="Lists all files (recursive)">
</Example>
<Example Group="ODBC"
Label="List All Folders (Recursive)"
Code="SELECT * FROM Folders"
Desc="Lists all folders recursively (exclude files)">
</Example>
<Example Group="ODBC"
Label="List All Files and Folders (Recursive)"
Code="SELECT * FROM FilesFolders"
Desc="Lists all files and folders recursively">
</Example>
<Example Group="ODBC"
Label="List All Files and Folders Shared with Me"
Code="SELECT * FROM SharedWithMe"
Desc="Lists all files / folders shared with me. To List shared file inside a specific folder use FilesFolders table (e.g. select * from FilesFolders WITh(DriverId='zzzzz', SearchPath='zzzz') ). Provide DriveId (Found in SharedWithMe Table - see remoteItem_parentReference_driveId column) and optionally supply SearchPath (e.g. /root:/folder/subfolder: ) from which you like to list files/folders.">
</Example>
<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 (Non-Recursive)" Desc="Lists items in a folder">
<Code>
<![CDATA[SELECT *
FROM list_folder
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!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
--,FolderId='01R65QTTUIVFJ6HVJ2WBC37KCAOQRKX7PP' --exact Id of Folder
--,FolderId='root' --default is root if FolderId is not passed
--,FolderId='root:/Documents/My Projects:'
--,FolderId='root:/Documents/My Projects/Sample Project:'
)
--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 path (Recursive)" Desc="Use list_files endpoint to list files recursivly (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' --folder path
--,SearchPath='/root:/myfolder:' --folder path
,SearchPath='/root:/myfolder/subfolder:' --folder path nested
,OrderBy='lastModifiedDateTime desc'
)
--You can get DriveId by selecting from 'Drives' table.
--You can get SearchPath by selecting from 'list_root' table OR selection from 'list_folder'.]]>
</Code>
</Example>
<Example Group="ODBC" Label="List files from Group / other User's drive"
Desc="This example shows how to read files from Group or other User's drive. You must use SearchType='children' for this purpose (known issue: recursive option may not work correctly in some cases).">
<Code>
<![CDATA[
SELECT * FROM list_files
WITH(
GroupOrUserId='/groups/138eb458-84f0-4928-bbe6-df663bd1fcea'
, DriveId='b!VxnFPrt3C0GQnQ70Skx-p9_Eqvvac5pGhvqzoRdUpnZb-G5HVC0WTbLU3a89b4LJ'
, SearchType='children'
, SearchPath='/root:/folder/subfolder:'
--, Filter='$.value[*]' --list both files and folders
--, Filter='$.value[?(@.folder.childCount != null)]' --list only folders
)
]]>
</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
--,SheetId='{00000000-0001-0000-0000-000000000000}' --Using ID is good idea if Sheet ever renamed (Id doesnt change)
,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 to OneDrive" 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='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,TargetFilePath='myfolder/List.xlsx' --Upload under some folder
--,TargetFilePath='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="Upload a file to OneDrive (Overwrite file if exists)" Desc="Uploads a file to a OneDrive 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'
,SourceFilePath='C:\My files\Employees\List.xls'
,SourceFilePath='C:\My files\Employees\List.xls'
,TargetFilePath='List.xlsx' --Upload on Root (Create -or- Overwrite if ConflictBehavior = 'replace')
--,TargetFilePath='myfolder/List.xlsx' --Upload under some folder
--,TargetFilePath='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="Upload a file with original timestamp (For Created/Modified Date)" Desc="Uploads a file to a OneDrive and use original creation/modified on date. By default it uses Upload time to set creation/modified on date time. Set UseSourceFileTimeStamp if you like to use original timestamp. This is useful if file was created few days back but Upoloaded later on.">
<Code>
<![CDATA[SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14'
,SourceFilePath='C:\My files\Employees\List.xls'
,TargetFilePath='Uploads/Employees/List.xls'
,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" 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'
)
--Using Table Name instead of endpoint (with Id / Path in WHERE clause for simple Syntax)
--************
--Delete File by ID or Path
--************
--DELETE From Files Where Id='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ'
--DELETE From Files Where Id='root:/test_out.csv:'
--DELETE From Files Where Id='root:/somefolder/test_out.csv:'
--************
--Delete Folder by ID or Path
--************
--DELETE From Folders Where Id='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ'
--DELETE From Folders Where Id='root:/somefolder:'
--DELETE From Folders Where Id='root:/somefolder/childfolder:'
--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>
<Example Group="ODBC" Label="Create / Update a CSV File in OneDrive from External Data Source (e.g. MSSQL / ODBC)" Desc="This example shows how to create / update a CSV file directly on OneDrive using streaming approach. This example reads records from Microsoft SQL Server database and writes data to CSV file on OneDrive. 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!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD'
, FileId='root:/test_out.csv:'
--, FileId='root:/subfolder/test_out.csv:'
--, FileId='01N3NxxxxxxxWZYSDJ' --exising File ID
--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)
--, FileId='01N3NI7YU6DYBSLCEDKBB23CR4FSWZYSDJ' --exising File ID for overwrite action
--, 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 OneDrive from External Data Source (e.g. MSSQL / ODBC)" Desc="This example shows how to create / update a JSON file directly on OneDrive using streaming approach. This example reads records from Microsoft SQL Server database and writes data to JSON file on OneDrive. 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!XpzQciaV_k6my5II5L22J0C4iRhyz21Js89PUyZ6-w0lH0AYv_I8RJHpXZQ81efD'
, FileId='root:/test_out.json:'
--, FileId='root:/subfolder/test_out.json:'
--, FileId='01N3NxxxxxxxxxxWZYSDJ' --exising File ID
--, ContinueOn404Error=0 --Fail if file not found (Useful for overwrite mode for exising file)
, 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 OneDrive (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a CSV file from OneDrive without downloading on the Disk (Streaming approach).">
<Code>
<![CDATA[
SELECT * from get_csv_file
WITH(
DriveId='b!0zqXLXXJh0uUMzl-JXAd9Ztngc-5utVDqRyD2lKpD2535-11HLQTR5z4hOzmA7Q0'
, FileId='root:/dump.csv:'
--, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'
--, 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
)
--SELECT * FROM get_csv_file WITH(FileId='01N3NI7YQA6I2F7YAXBBCZCLLPTRFLANAX')
--SELECT * FROM get_csv_file WITH(FileId='root:/dump.csv:')
--SELECT * FROM get_csv_file WITH(FileId='root:/Documents/dump.csv:')
--SELECT * FROM get_csv_file WITH(FileId='root:/Documents/SubFolder/dump.csv:')
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Zip/Gzip Compressed CSV File from OneDrive (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a compressed CSV file (Zip or Gzip) from OneDrive 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:'
--, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'
--, 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 OneDrive (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a JSON file from OneDrive 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:'
--, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'
, 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
)
--SELECT * FROM get_json_file WITH(FileId='01N3NI7YQJMKXUWUAQGJEJJJNSGVT7QSJ3')
--SELECT * FROM get_json_file WITH(FileId='root:/dump.json:')
--SELECT * FROM get_json_file WITH(FileId='root:/Documents/dump.json:')
--SELECT * FROM get_json_file WITH(FileId='root:/Documents/SubFolder/dump.json:')
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Read Zip/Gzip Compressed JSON File from OneDrive (Parse Rows and Columns without downloading)"
Desc="This example shows how to read a compressed JSON file (Zip or Gzip) from OneDrive 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:'
--, FileId='01N3NI7YRUO2UHV2TUMBGJ4H4QQMWCG6DA'
, Filter='$.store.books[*]' --or just blank (see help file for more filter examples)
, FileCompressionType='Zip' --None, GZip, Zip
)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Move a File or Folder in OneDrive"
Desc="This example shows how to move a file or folder to a different location in OneDrive. 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 Drive
DriveId='me'
--OR--
--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'
--Source Item
--Item Id (File or Folder) you like to move or rename
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Drive Id (Optional): Only needed if moving file to a different Drive (e.g. another User's drive)
--, TargetDriveId='me'
--OR
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder
, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--OR (for moving to root)
--, TargetFolderId='root'
--Target Item Name (New 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 OneDrive"
Desc="This example shows how to rename a file or folder and you can also move to a different location in OneDrive.. 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 Drive
DriveId='me'
--OR--
--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'
--Source Item you like to rename or move
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Drive Id (Optional): Only needed if moving file to a different Drive (e.g. another User's drive)
--, TargetDriveId='me'
--OR
--, TargetDriveId='b!0zqXLXXJh0uxxxxxxxxxxxxxxxxxxxxx'
--Target Folder (Optional): If you are doing Rename and Move both in one action then supply new Folder Id too else keep it blank for just rename
--, TargetFolderId='01R65QTTTBPH6V2AP36VD33CYYDXJSNHLN'
--, TargetFolderId='root'
--Target Item Name (New name)
, 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 OneDrive"
Desc="This example shows how to copy a file or folder to a different location in OneDrive (In same Drive or different User's drive). 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 Drive
DriveId='me'
--OR--
--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'
--Source Item you like to copy
, ItemId='01R65QTTRARZ42C4BN6FF2WOH3AONX4GUW'
--Target Drive Id (Optional): Only needed if copying file to a different Drive (e.g. another User's drive)
--, TargetDriveId='me'
--OR--
--, 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>
<Example Group="ODBC" Label="Download File and convert to PDF or HTML file format (convert xlsx, docx, pptx and many more) in OneDrive"
Desc="This example shows how to download a file with different format (PDF or HTML). If conversion not supported you will get an error.">
<Code>
<![CDATA[
SELECT "Status"
FROM download_file
WITH(
DriveId='me'
--OR--
--DriveId='b!7HBaTtrbekqMYJQ-OqV5Q3hrcOIQoyhGiAoWjqWFenIlIJ-Us7DMQ6jvyrsWMJPx'
, FileId='01R65QTTTF7H7WMCHCKRFJGEJTXAEC7RGX'
--Supported Source Formats: csv, doc, docx, odp, ods, odt, pot, potm, potx, pps, ppsx, ppsxm, ppt, pptm, pptx, rtf, xls, xlsx
, ConvertTo='pdf'
, TargetFilePath='C:\temp\converted.pdf'
--OR--
--Supported Source Formats: loop, fluid, wbtx
--, ConvertTo='html'
--, TargetFilePath='C:\temp\converted.html'
)
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
