Endpoint List Files

Name

list_files

Description

No description available [API reference]

Related Tables

Files , FilesFolders , Folders , SharedWithMe (Not valid for Application Creds or other user)

Parameters

Output Columns

Input Columns

Examples

SSIS

Use OneDrive Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the Files table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - OneDrive

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!

OneDrive

Files

Required Parameters
Record Filter (Client Side)	Fill-in the parameter...
Drive Id / Shared Folder	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (Default=Recursive)	search(q='')
Search Path (Default=Root Folder) - Max 200 listed	/root
Order By Field(s) - Only for Recursive SearchType

API Destination

This Endpoint belongs to the Files table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to list files:

API Destination - OneDrive

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!

OneDrive

Files

Select

Required Parameters
Record Filter (Client Side)	Fill-in the parameter...
Drive Id / Shared Folder	Fill-in the parameter...
Optional Parameters
Default Group or User Id (additional Scopes needed to list - If fails enter manually)
Search Type (Default=Recursive)	search(q='')
Search Path (Default=Root Folder) - Max 200 listed	/root
Order By Field(s) - Only for Recursive SearchType

ODBC application

Use these SQL queries in your ODBC application data source:

List files in a folder path (Recursive)

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.

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'.

List files from Group / other User's drive

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).

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
)

List All Files (Recursive)

Lists all files (recursive)

SELECT * FROM Files

List All Files and Folders (Recursive)

Lists all files and folders recursively

SELECT * FROM FilesFolders

List All Folders (Recursive)

Lists all folders recursively (exclude files)

SELECT * FROM Folders

List All Files and Folders Shared with Me

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.

SELECT * FROM SharedWithMe

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List files in a folder path (Recursive)

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.

DECLARE @MyQuery NVARCHAR(MAX) = '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''.';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List files from Group / other User's drive

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).

DECLARE @MyQuery NVARCHAR(MAX) = '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
)';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files (Recursive)

Lists all files (recursive)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files and Folders (Recursive)

Lists all files and folders recursively

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM FilesFolders';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Folders (Recursive)

Lists all folders recursively (exclude files)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Folders';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];

List All Files and Folders Shared with Me

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.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM SharedWithMe';

EXEC (@MyQuery) AT [LS_TO_ONEDRIVE_IN_GATEWAY];