EndPoint List files / folders from a parent folder (Recursive)

Name

list_parent_items

Description

List files or folders under a specified parent folder

Parameters

Parameter

Label

Required

Options

Description

DriveType

Drive Type

YES

Option	Value
My Drive	user
Shared Drive	drive
All Drives	allDrives

Default search context is User's drive. Bodies of items (files/documents) to which the query applies. Supported bodies are 'user', 'domain', 'drive', and 'allDrives'. Prefer 'user' or 'drive' to 'allDrives' for efficiency. By default, corpora is set to 'user'. However, this can change depending on the filter set through the 'Query' parameter.

Query

Extra Query (must start with ' and ' --OR-- ' or ')

Option	Value
None
By name (exact name match)	and name='abc'
By name (contains sub string)	and name contains 'abc'
By name (does not contain)	and not name contains 'abc'
By text (search inside file)	and fullText contains 'abc'
By created time	and createdTime > '2012-06-04T12:00:00'
By modified time	and modifiedTime > '2012-06-04T12:00:00'
Allow only shared files and folders	and sharedWithMe=true
Exclude App Script	and mimeType!='application/vnd.google-apps.script'
Search for spreadsheet	and mimeType = 'application/vnd.google-apps.spreadsheet'
Search for multiple files type	and mimeType contains 'application/vnd.google-apps.spreadsheet' OR mimeType contains 'application/vnd.google-apps.document' OR mimeType contains 'application/vnd.google-apps.presentation' OR mimeType contains 'application/vnd.google-apps.drawing'

Data filter (e.g. somecolumn -eq 'somevalue' ) , User can select multiple filter criteria using 'AND','OR' operator , Note*:- please refer the link for more filter criteria : https://zappysys.com/links?url=https://developers.google.com/drive/api/guides/search-files [API reference]

FolderId

Search under Folder Id (Keep Blank for all folders)

Folder Id for which you like to list all files

IncludeFromTrash

Include files from trash

Option	Value
false	false
true	true

ItemType

Search Item type (i.e. files or folders)

Option	Value
all	all
files	files
folders	folders
files_native	files_native
files_exclude_native	files_exclude_native
sheets	sheets
documents	documents

DriveId

Shared DriveId

By default file lists from MyDrive but if you like to search other Shared drive (formally known as Team drive) then set this parameter.

pageSize

PageSize

SupportsAllDrives

Supports all drives (e.g. My and Shared)

Option	Value
true	true
false	false

Whether the requesting application supports both My Drives and shared drives.

IncludeItemsFromAllDrives

Include items from all drives (e.g. My and Shared)

Option	Value
true	true
false	false

Spaces

Spaces to include in corpora (comma separed)

IncludePermissionsForView

Include Permissions For View

Option	Value
true	true
false	false

IncludeLabels

Include Labels

Option	Value
true	true
false	false

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw
Id	`DT_WSTR`	`nvarchar(440)`	440	`False`
Kind	`DT_WSTR`	`nvarchar(200)`	200	`False`
Name	`DT_WSTR`	`nvarchar(780)`	780	`False`
MimeType	`DT_WSTR`	`nvarchar(650)`	650	`False`
CreatedTime	`DT_DBTIMESTAMP`	`datetime`		`False`
ModifiedTime	`DT_DBTIMESTAMP`	`datetime`		`False`
Size	`DT_WSTR`	`nvarchar(200)`	200	`False`
Version	`DT_WSTR`	`nvarchar(200)`	200	`False`
Shared	`DT_BOOL`	`bit`		`False`
Starred	`DT_BOOL`	`bit`		`False`
Trashed	`DT_BOOL`	`bit`		`False`
FileExtension	`DT_WSTR`	`nvarchar(200)`	200	`False`
DriveId	`DT_WSTR`	`nvarchar(500)`	500	`False`
CopyRequiresWriterPermission	`DT_BOOL`	`bit`		`False`
Md5Checksum	`DT_WSTR`	`nvarchar(500)`	500	`False`
WritersCanShare	`DT_BOOL`	`bit`		`False`
ViewedByMe	`DT_BOOL`	`bit`		`False`
Parents	`DT_WSTR`	`nvarchar(500)`	500	`False`
ThumbnailLink	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
IconLink	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
LastModifyingUserDisplayName	`DT_WSTR`	`nvarchar(500)`	500	`False`
LastModifyingUserKind	`DT_WSTR`	`nvarchar(100)`	100	`False`
LastModifyingUserMe	`DT_BOOL`	`bit`		`False`
LastModifyingUserPermissionId	`DT_WSTR`	`nvarchar(500)`	500	`False`
LastModifyingUserEmailAddress	`DT_WSTR`	`nvarchar(500)`	500	`False`
LastModifyingUserPhotoLink	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Owners	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
HeadRevisionId	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
WebViewLink	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
WebContentLink	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
ViewersCanCopyContent	`DT_BOOL`	`bit`		`False`
Permissions	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
HasThumbnail	`DT_BOOL`	`bit`		`False`
Spaces	`DT_WSTR`	`nvarchar(500)`	500	`False`
ExplicitlyTrashed	`DT_BOOL`	`bit`		`False`
ModifiedByMeTime	`DT_DBTIMESTAMP`	`datetime`		`False`
ViewedByMeTime	`DT_DBTIMESTAMP`	`datetime`		`False`
QuotaBytesUsed	`DT_WSTR`	`nvarchar(50)`	50	`False`
OriginalFilename	`DT_WSTR`	`nvarchar(500)`	500	`False`
OwnedByMe	`DT_BOOL`	`bit`		`False`
FullFileExtension	`DT_WSTR`	`nvarchar(50)`	50	`False`
IsAppAuthorized	`DT_BOOL`	`bit`		`False`
CanChangeViewersCanCopyContent	`DT_BOOL`	`bit`		`False`
CanEdit	`DT_BOOL`	`bit`		`False`
CanCopy	`DT_BOOL`	`bit`		`False`
CanComment	`DT_BOOL`	`bit`		`False`
CanAddChildren	`DT_BOOL`	`bit`		`False`
CanDelete	`DT_BOOL`	`bit`		`False`
CanDownload	`DT_BOOL`	`bit`		`False`
CanListChildren	`DT_BOOL`	`bit`		`False`
CanRemoveChildren	`DT_BOOL`	`bit`		`False`
CanRename	`DT_BOOL`	`bit`		`False`
CanTrash	`DT_BOOL`	`bit`		`False`
CanReadRevisions	`DT_BOOL`	`bit`		`False`
CanChangeCopyRequiresWriterPermission	`DT_BOOL`	`bit`		`False`
CanMoveItemIntoTeamDrive	`DT_BOOL`	`bit`		`False`
CanUntrash	`DT_BOOL`	`bit`		`False`
CanModifyContent	`DT_BOOL`	`bit`		`False`
CanMoveItemOutOfDrive	`DT_BOOL`	`bit`		`False`
CanAddMyDriveParent	`DT_BOOL`	`bit`		`False`
CanRemoveMyDriveParent	`DT_BOOL`	`bit`		`False`
CanMoveItemWithinDrive	`DT_BOOL`	`bit`		`False`
CanShare	`DT_BOOL`	`bit`		`False`
CanMoveChildrenWithinDrive	`DT_BOOL`	`bit`		`False`
CanModifyContentRestriction	`DT_BOOL`	`bit`		`False`
CanChangeSecurityUpdateEnabled	`DT_BOOL`	`bit`		`False`
CanAcceptOwnership	`DT_BOOL`	`bit`		`False`
CanReadLabels	`DT_BOOL`	`bit`		`False`
CanModifyLabels	`DT_BOOL`	`bit`		`False`
CanModifyEditorContentRestriction	`DT_BOOL`	`bit`		`False`
CanModifyOwnerContentRestriction	`DT_BOOL`	`bit`		`False`
CanRemoveContentRestriction	`DT_BOOL`	`bit`		`False`
CanMoveChildrenOutOfDrive	`DT_BOOL`	`bit`		`False`
CanReadDrive	`DT_BOOL`	`bit`		`False`
CanReadTeamDrive	`DT_BOOL`	`bit`		`False`
CanMoveTeamDriveItem	`DT_BOOL`	`bit`		`False`
CanMoveItemWithinTeamDrive	`DT_BOOL`	`bit`		`False`
CanMoveItemOutOfTeamDrive	`DT_BOOL`	`bit`		`False`
CanDeleteChildren	`DT_BOOL`	`bit`		`False`
CanMoveChildrenOutOfTeamDrive	`DT_BOOL`	`bit`		`False`
CanMoveChildrenWithinTeamDrive	`DT_BOOL`	`bit`		`False`
CanTrashChildren	`DT_BOOL`	`bit`		`False`
CanAddFolderFromAnotherDrive	`DT_BOOL`	`bit`		`False`
ThumbnailVersion	`DT_WSTR`	`nvarchar(50)`	50	`False`
ModifiedByMe	`DT_BOOL`	`bit`		`False`
PermissionIds	`DT_WSTR`	`nvarchar(500)`	500	`False`
ImageMediaMetadataWidth	`DT_I4`	`int`		`False`
ImageMediaMetadataHeight	`DT_I4`	`int`		`False`
ImageMediaMetadataRotation	`DT_I4`	`int`		`False`
LinkShareMetadataSecurityUpdateEligible	`DT_BOOL`	`bit`		`False`
LinkShareMetadataSecurityUpdateEnabled	`DT_BOOL`	`bit`		`False`
Sha1Checksum	`DT_WSTR`	`nvarchar(500)`	500	`False`
Sha256Checksum	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
HasAugmentedPermissions	`DT_BOOL`	`bit`		`False`

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Google Drive Connector in API Source component to read data or in API Destination component to read/write data:

List files / folders from a parent folder (Recursive) using API Source

Google Drive

List files / folders from a parent folder (Recursive)

SSIS API Source - Read from table or endpoint

List files / folders from a parent folder (Recursive) using API Destination

Google Drive

List files / folders from a parent folder (Recursive)

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

List / search items (files/folders) from a parent folder in recursive or non-recursive mode

This example shows how to list files from a parent folder, optionally you can specifiy item types to list (e.g. files, folders, sheets, documents). If you do not specify FolderId then recursive list is returned.

SELECT * FROM list_parent_items
WITH(
	--Parent folder id (Keep it blank to scan all files in all folders - recursive
	  FolderId='1VK5NBhuOTaLEy1pBMsQtG672vrJNgxai'
	
	--List only from Trash (deleted files)
	, IncludeFromTrash='false'
	
	--Extra search criteria
	--, Query='or (name contains ''blog'') or (name contains ''test'') ' --extra query
	
	--Item type to return (default is all)
	--, ItemType='files'	-- (default=all) all, files, folders, sheets, documents, files_native, files_exclude_native
	--, ItemType='folders'
	--, ItemType='sheets'
	--, ItemType='files_native'
)

SQL Server

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

List / search items (files/folders) from a parent folder in recursive or non-recursive mode

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM list_parent_items
WITH(
	--Parent folder id (Keep it blank to scan all files in all folders - recursive
	  FolderId=''1VK5NBhuOTaLEy1pBMsQtG672vrJNgxai''
	
	--List only from Trash (deleted files)
	, IncludeFromTrash=''false''
	
	--Extra search criteria
	--, Query=''or (name contains ''''blog'''') or (name contains ''''test'''') '' --extra query
	
	--Item type to return (default is all)
	--, ItemType=''files''	-- (default=all) all, files, folders, sheets, documents, files_native, files_exclude_native
	--, ItemType=''folders''
	--, ItemType=''sheets''
	--, ItemType=''files_native''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];