Changes - Fetch
Retrieve Google Drive changes using SQL Server
- See SQLHTTP easy setup for Google
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
CREATE PROCEDURE usp_Google_Changes_Fetch( @Profile nvarchar(100), @includeCorpusRemovals bit = 0, @includeDeleted bit = 1, @includeSubscribed bit =1, @includeTeamDriveItems bit = 0, @maxResults int = 100, @pageToken nvarchar(MAX) = NULL, @spaces varchar(30) = NULL, @startChangeId bigint = NULL, @supportsTeamDrives bit = 0, @teamDriveId nvarchar(MAX) = NULL, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @Text_includeCorpusRemovals varchar(5) DECLARE @Text_includeDeleted varchar(5) DECLARE @Text_includeSubscribed varchar(5) DECLARE @Text_includeTeamDriveItems varchar(5) DECLARE @Text_supportsTeamDrives varchar(5) SET @Text_includeCorpusRemovals = CASE WHEN @includeCorpusRemovals = 0 THEN 'false' ELSE 'true' END SET @Text_includeDeleted = CASE WHEN @includeDeleted = 0 THEN 'false' ELSE 'true' END SET @Text_includeSubscribed = CASE WHEN @includeSubscribed = 0 THEN 'false' ELSE 'true' END SET @Text_includeTeamDriveItems = CASE WHEN @includeTeamDriveItems = 0 THEN 'false' ELSE 'true' END SET @Text_supportsTeamDrives = CASE WHEN @supportsTeamDrives = 0 THEN 'false' ELSE 'true' END DECLARE @URL nvarchar(MAX) DECLARE @QueryString nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = 'https://www.googleapis.com/drive/v2/changes' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'includeCorpusRemovals', @Text_includeCorpusRemovals, 'includeDeleted', @Text_includeDeleted, 'includeSubscribed', @Text_includeSubscribed, 'includeTeamDriveItems', @Text_includeTeamDriveItems, 'maxResults', @maxResults, 'pageToken', @pageToken, 'spaces', @spaces, 'startChangeId', @startChangeId, 'supportsTeamDrives', @Text_supportsTeamDrives, 'teamDriveId', @teamDriveId SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Google_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @ContentType = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Profile nvarchar(100) SET @Profile = 'My google' --Google API Bearer Token expires after an hour EXEC usp_Google_Auth_Refresh @Profile EXEC usp_Google_Changes_Fetch @Profile = @Profile, @maxResults = 5, @spaces = 'drive', @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/items/JsonObject' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' -- SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'@type', N'nvarchar(MAX)') AS [type] ,T.C.value(N'@fileId', N'nvarchar(MAX)') AS [fileId] ,T.C.value(N'file[1]/@title', N'nvarchar(MAX)') AS title ,T.C.value(N'file[1]/@fileSize', N'nvarchar(MAX)') AS [fileSize] ,T.C.value(N'file[1]/@mimeType', N'nvarchar(MAX)') AS [mimeType] FROM @X.nodes(N'/JsonObject/items/JsonObject') T(C) --Set @pageToken to one of the below values (normally @nextPageToken) --and continue calling the above stored procedure to load additional pages DECLARE @nextPageToken nvarchar(MAX) DECLARE @newStartPageToken nvarchar(MAX) SELECT @nextPageToken = T.C.value(N'@nextPageToken', N'nvarchar(MAX)') ,@newStartPageToken = T.C.value(N'@newStartPageToken', N'nvarchar(MAX)') FROM @X.nodes(N'/JsonObject') T(C) END |
1 2 3 4 5 6 7 8 |
id type fileId title fileSize mimeType ----- ------ ------------------------- ----------------------------- --------- --------------- 89 file 0B899kwotB4sTSGppU0VF... CO2 and Acytlene Cooling.PDF 84696 application/pdf 106 file 0B899kwotB4sTSEY2c1pT... Archive8March2016.pdf 250302 application/pdf 120 file 0B899kwotB4sTOVFQd3A4... Heat Treatment.pdf 3524409 application/pdf 136 file 0B899kwotB4sTWlRxcGo5... Wordware.Microsoft.Excel.rar 3646389 application/rar |
- About – Fetch
- App – Fetch
- Apps – Fetch
- Change – Fetch
- Changes – Fetch
- Child File – Create
- Child File – Fetch
- Child File – Remove
- Children Files – Fetch
- Comment – Create
- Comment – Delete
- Comment – Fetch
- Comment – Update
- Comments – Fetch
- File – Copy
- File – Delete
- File – Fetch
- File – Patch
- File – Touch
- File – Trash
- File – Untrash
- Files – Fetch
- Parent Folder – Create
- Parent Folder – Fetch
- Parent Folder – Remove
- Parent Folders – Fetch
- Permission – Create
- Permission – Delete
- Permission – Fetch
- Permission – Patch
- Permission – Update
- Permission ID – Fetch
- Permissions – Fetch
- Properties – Fetch
- Property – Create
- Property – Delete
- Property – Fetch
- Property – Update
- Replies – Fetch
- Reply – Create
- Reply – Delete
- Reply – Fetch
- Reply – Update
- Revision – Delete
- Revision – Fetch
- Revision – Update
- Revisions – Fetch
- Start Page Token – Fetch
- Trashed Files – Delete
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.