Google Drive Connectors

ZappySys Google Drive Connector provide read / write capability inside your app (see list below), using these drag and drop , high performance connectors you can perform many Google Drive operations without any coding. You can use this connector to integrate Google Drive data inside apps like SSIS, SQL Server or popular ETL Platforms / BI Tools/ Reporting Apps / Programming languages (i.e. Informatica, Power BI, SSRS, Excel, C#, JAVA, Python)

Download for SSIS Download for Other Apps Documentation

Video Tutorial - Google Drive - Connectors REST API Integration

Click on your App below to get started with Google Drive Integration

Choose your App / Scenario for which you like to integrate Google Drive

SSIS

SQL Server

Power BI

SSRS

Informatica

MS Access

MS Excel

SSAS

Python

JAVA

Tableau

SAP Crystal Reports

Azure Data Factory (ADF)

ODBC

Actions supported by Google Drive Connector

Google Drive Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.

Get my drive info

Gets my drive information [Read more...]

Lists shared drives

Lists the user's shared drives (i.e. formally known as team drive) with optional search criteria [Read more...]

List files

List files / folders with search criteria [Read more...]

Parameter

Description

Search Criteria

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

Option	Value
None
By name (exact name match)	name='abc'
By name (contains sub string)	name contains 'abc'
By name (does not contain)	not name contains 'abc'
By text (search inside file)	fullText contains 'abc'
By created time	createdTime > '2012-06-04T12:00:00'
By modified time	modifiedTime > '2012-06-04T12:00:00'
Allow only shared files and folders	sharedWithMe=true
Exclude trashed files	trashed=false
Include files from trash	mimeType!='application/vnd.google-apps.folder'
Exclude files from trash	mimeType!='application/vnd.google-apps.folder' and trashed!=true
Exclude Folders	mimeType!='application/vnd.google-apps.folder'
Exclude App Script	mimeType!='application/vnd.google-apps.script'
Search for spreadsheet	mimeType = 'application/vnd.google-apps.spreadsheet'
Search for multiple files type	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'

Search items (i.e. files / folders)

Lists items (i.e. files / folders) with search criteria [Read more...]

List folders

Lists folders [Read more...]

Parameter

Description

Search Criteria

Option	Value
None
By name (exact name match)	name='abc'
By name (contains sub string)	name contains 'abc'
By name (does not contain)	not name contains 'abc'
By text (search inside file)	fullText contains 'abc'
By created time	createdTime > '2012-06-04T12:00:00'
Include folders from trash	mimeType='application/vnd.google-apps.folder'
Exclude folders from trash	mimeType='application/vnd.google-apps.folder' and trashed!=true
By modified time	modifiedTime > '2012-06-04T12:00:00'
Allow only shared files and folders	sharedWithMe=true
Exclude trashed files	trashed=false
Exclude Folders	mimeType!='application/vnd.google-apps.folder'
Exclude App Script	mimeType!='application/vnd.google-apps.script'
Search for spreadsheet	mimeType = 'application/vnd.google-apps.spreadsheet'
Search for multiple files type	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'

List deleted files / folders

Lists only deleted files / folders from trash [Read more...]

Parameter	Description
Search Criteria

Get file info

Gets information of a file [Read more...]

Parameter	Description
Id	Id of a file you want to see information for

Download a file

Downloads a file [Read more...]

Parameter	Description
File Id	Id of a file you want to download

Export a file

Export a document editor files such document, Spreadsheets, Drawings, Presentations, Apps Scripts to common file formats as defined in this link https://developers.google.com/drive/api/guides/ref-export-formats [Read more...]

Parameter

Description

File Id

Id of a file you want to export and file must be editor type (e.g. document, Spreadsheets, Drawings, Presentations, Apps Scripts)

Export As (Mime Type)

The MIME type of the format requested for this export.

Option	Value
None
Export to PDF	application/pdf
Export to HTML	text/html
Export to HTML (zipped)	application/zip
Export to Plain text	text/plain
Export to Rich text	application/rtf
Export to Open Office doc	application/vnd.oasis.opendocument.text
Export to MS Word document	application/vnd.openxmlformats-officedocument.wordprocessingml.document
Export to EPUB	application/epub+zip
Export to MS Excel	application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Export to Open Office sheet	application/x-vnd.oasis.opendocument.spreadsheet
Export to (sheet only)	text/tab-separated-values
Export to JPEG	image/jpeg
Export to PNG	image/png
Export to SVG	image/svg+xml
Export to MS PowerPoint	application/vnd.openxmlformats-officedocument.presentationml.presentation
Export to Open Office presentation	application/vnd.oasis.opendocument.presentation
Export to JSON	application/vnd.google-apps.script+json

fields

Upload a file

Uploads a file (resumable) [Read more...]

Parameter

Description

Keep Revision Forever

Defines whether uploaded file has revisions

Option	Value
true	true
false	false

Parent FolderId

Id of a parent folder you want to upload the file in. Use value 'root' to place the file in the topmost level.

Shared DriveId

By default file lists from MyDrive but if you like to search other Shared drive then set this parameter.

Drive Type

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.

Option	Value
My Drive	user
Shared Drive	drive

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

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

Option	Value
true	true
false	false

Target FileName

A filename the file will have in Google Drive

Local FilePath

Specify a disk file path

AddParents

A comma-separated list of parent IDs to add

OcrLanguage

A language hint for OCR processing during image import (ISO 639-1 code).

UseContentAsIndexableText

Whether to use the uploaded content as indexable text.

Create a folder

Creates a folder [Read more...]

Parameter	Description
Name	A folder name the folder will have in Google Drive
Parent FolderId	Id of a parent folder you want to create the folder in. Use value 'root' to create the folder in the topmost level.

Delete an item

Deletes an item [Read more...]

Duplicate a file

Duplicates a file [Read more...]

Parameter	Description
File Id	Id of a file you want to duplicate

Replace file data (keep same file id)

Update file with new content / metadata (keep same file Id) [Read more...]

Parameter	Description
File Id	Id of a file you want to update
DiskFilePath	A disk file path you want to update file contents with
KeepRevisionForever	Defines whether uploaded file has revisions
AddParents	A comma-separated list of parent IDs to add
OcrLanguage	A language hint for OCR processing during image import (ISO 639-1 code).
UseContentAsIndexableText	Whether to use the uploaded content as indexable text.

Update metadata in a file

Updates meta-data in a file [Read more...]

Parameter

Description

File Id

Id of a file you want to update meta-data to

Keep Revision Forever

Defines whether uploaded file has revisions

Option	Value
True	True
False	False

AddParents

A comma-separated list of parent IDs to add

OcrLanguage

A language hint for OCR processing during image import (ISO 639-1 code).

UseContentAsIndexableText

Whether to use the uploaded content as indexable text.

Empty trash

Empties trash [Read more...]

Delete all items

Deletes all items from the drive [Read more...]

Generic Request

This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL. [Read more...]

Parameter	Description
Url	API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body	Request Body content goes here
IsMultiPart	Check this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter	Enter filter to extract array from response. Example: $.rows[] --OR-- $.customers[].orders[*]. Check your response document and find out hierarchy you like to extract
Headers	Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair

Google Drive Connector Examples (For ODBC PowerPack)

This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

Get my drive information [Read more...]

Gets my drive information

SELECT * FROM Drive

List all shared drives [Read more...]

List all shared drives

SELECT * FROM SharedDrives --WITH(Query='name = "HR"')

Get information about shared drive [Read more...]

Get information about shared drive

SELECT * FROM SharedDrives WITH(Query='name = "HR"')

List / search files from my drive [Read more...]

List or search files from my drive (use search query expression). Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
)

List / search files from a single shared drive [Read more...]

List or search files from a specific shared drive by driveId and search query expression. Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
	DriveId='0AFWIggplk2z6Uk9PVA', 
	DriveType='drive',
	SupportsAllDrives='true',
	IncludeItemsFromAllDrives='true'
)

List / search files from all shared drives [Read more...]

List or search files from all shared drives by driveId and search query expression. Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
	DriveType='allDrives',
	SupportsAllDrives='true',
	IncludeItemsFromAllDrives='true'
)

Dowload a file (my drive) [Read more...]

Downloads a file (from user my drive)

SELECT * FROM download_file
  WITH(Id='1PpE2eSi5faAnMwiSolwr34zA5MwOdR3D'	
      ,ResponseDataFile='C:\MyFiles\myfile.ext'
  )
  --You can get file Id by selecting from 'list_files' endpoint

Dowload a file from google shared drive [Read more...]

Dowload a file from google shared drive for which user has access

SELECT * FROM download_file
WITH(
	  DriveType='drive' --or set 'user' for mydrive 
	, DriveId='0AFWIggplk2z6Uk9PVA'
	, Id='1hk7odj2Y2--yxN9DY0zW88hSnxqiyHq0' --file id
	, ResponseDataFile='c:\temp\dump.zip'
	--You can get file Id by selecting from 'list_files' endpoint
)

Upload local file to google shared drive folder [Read more...]

Upload a local file to google shared drive folder for which user has access. Donot set DriveId to upload to MyDrive rather than sahred drive.

SELECT *
FROM upload_file
WITH(
	  DriveId='0AC00HWQH144cUk9PVA' --team drive id (keep blank for uploading to my drive)
	, DiskFilePath='C:\temp\dump.csv' --source file path
	, FileName='dump.csv' --target file name
	, ParentFolderId='1PRySfNNjWIp9ZAnhPh-u9tKRj3QOETSq'
	, KeepRevisionForever='false'	
)

Get file list [Read more...]

Downloads a file

SELECT * FROM Files

Update a file [Read more...]

Updates a file

UPDATE Files
  WITH (Id='1EVzXFVBQIh3LcJvfcLhI1UWhW3qUxFkM'
  ,DiskFilePath='C:\MyFiles\myfile.ext')
  --You can get file Id by selecting from 'list_files' endpoint

Delete a file [Read more...]

Deletes a file

DELETE FROM Files
  WITH (Id='1gcu0hV34OTfgvcu9CFhr7Go4b35j5Mop')
  --You can get file Id by selecting from 'list_files' endpoint

Delete a file by name from a shared drive [Read more...]

Delete a file by name from a shared drive

DELETE FROM Files where Name='dump.csv' and Trashed=0 WITH(DriveId='0AC00HWQH144cUk9PVA', DriveType='drive')
  --You can get file Id by selecting from 'list_files' endpoint

Upload a file with INSERT keyword [Read more...]

Upload a file with INSERT keyword. Advantage of INSERT is you can use Bulk option using SOURCE clause. This way you can upload many files

INSERT INTO Files
  WITH (FileName='My filename'
      ,DiskFilePath='C:\MyFolder\Mymyfile.ext'
      ,ParentFolderId='root'
	  )
  --You can get ParentFolderId by selecting from 'list_folders' endpoint or just use 'root'

Delete a folder [Read more...]

Deletes a folder

DELETE FROM Folders
  WITH (Id='1yDO11prsLH9DUrBTCadV6GGMJrITjF9I')
  --You can get folder Id by selecting from 'list_folders' endpoint

Create a folder [Read more...]

Creates a folder

INSERT INTO Folders
  WITH (Name='My folder name'
      ,ParentFolderId='root')
  --You can get ParentFolderId by selecting from 'list_folders' endpoint or just use 'root'

Get file information [Read more...]

Gets file information

SELECT * FROM FileInfo
  WITH (Id='1EVzXFVBQIh3LcJvfcLhI1UWhW3qUxFkM')
  --You can get file Id by selecting from 'list_files' endpoint

Get items list [Read more...]

Gets items list

SELECT * FROM ItemsList

Get items list using query [Read more...]

Gets items list using query

SELECT Id, Kind, Name, MimeType
  FROM list_items
  WITH (Query = 'name contains ''.txt''')

Get files list [Read more...]

Gets files list

SELECT * FROM FilesList

Get folders list [Read more...]

Gets folders list

SELECT * FROM FoldersList

Select from trash [Read more...]

Shows deleted items in Trash

SELECT * FROM Trash

List files from trash for a shared drive [Read more...]

Shows deleted items in Trash

SELECT * FROM Trash
				WITH(
				    DriveType='drive'
				   ,DriveId='1j0HFOP4gsoExxxxxx' 	--get id from select * from SharedDrives			   
				 )

Empty trash bin [Read more...]

Empties trash bin

DELETE FROM Trash

Empty trash bin for a shared drive [Read more...]

Empties trash bin for a specified shared drive

DELETE FROM Trash
				WITH(
				    DriveType='drive'
				   ,DriveId='1j0HFOP4gsoExxxxxx' --get id from select * from SharedDrives
				 )

Export Google Sheet to Excel (xlsx) or PDF [Read more...]

Export a Google files to other format (e.g. Google Sheets to MS Excel / PDF)

SELECT "Status" FROM export_file
				WITH(
                      Id='1j0HFOP4gsoE-Zbf2xN3IsPSj8wgwNtIfyyvM1_BWkW4' --//google sheet id or document ID
					  
				   --,DriveId='xx your shared drive id'  --get id from select * from SharedDrives
				   --,DriveType='drive'
					   
					 , ExportAs='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
				   --, ExportAs='application/pdf'
					 , ResponseDataFile='c:\temp\sheet1.xlsx'		
					 , FileOverwriteMode='AlwaysOverwrite' -- or try 'FailIfExists' or 'SkipIfExists'					 
				))
			 --You can export files from 'export_file' endpoint