Search Gmail by sender, subject, date, or attachment
See more GMail REST API Examples
Choose a Gmail search query, follow every result page, and fetch From and Subject headers for each match. Edit the query to use operators such as from:, subject:, after:, before:, has:attachment, and filename:. Quoted phrases and special characters are URL-encoded once.
Gmail search dates use midnight Pacific Standard Time. Use Unix timestamps in seconds for exact date boundaries in another timezone. Spam and Trash are excluded unless includeSpamTrash=true is added. Search requires gmail.readonly or a broader mail scope. Query searches are unavailable with gmail.metadata.
Authorize the application with https://www.googleapis.com/auth/gmail.readonly. The example reads the access token from gmail-token.json in a private working directory. Authorize a desktop application.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @iTmp1 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @token int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @token, 'LoadFile', @success OUT, 'gmail-token.json'
IF @success <> 1
BEGIN
PRINT 'Could not read gmail-token.json from the private working directory.'
EXEC @hr = sp_OADestroy @token
RETURN
END
EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'An OAuth access token is required.'
EXEC @hr = sp_OADestroy @token
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
DECLARE @searchQuery nvarchar(4000)
SELECT @searchQuery = 'from:sender@example.com has:attachment filename:pdf'
DECLARE @baseUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @baseUrl OUT
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages?maxResults=100&q='
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @searchQuery
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, @sTmp0
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
DECLARE @pageToken nvarchar(4000)
SELECT @pageToken = ''
DECLARE @page int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @page OUT
DECLARE @total int
SELECT @total = 0
DECLARE @more int
SELECT @more = 1
WHILE (@more = 1)
BEGIN
EXEC sp_OAMethod @baseUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @url, 'SetString', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'Append', @success OUT, '&pageToken='
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @pageToken
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
-- Keep this page while individual message requests reuse reply.
EXEC sp_OAMethod @reply, 'MakeCopy', @success OUT, @page
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @reply, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
DECLARE @i int
SELECT @i = 0
EXEC sp_OAMethod @page, 'SizeOfArray', @iTmp0 OUT, 'messages'
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OASetProperty @page, 'I', @i
DECLARE @messageId nvarchar(4000)
EXEC sp_OAMethod @page, 'StringOf', @messageId OUT, 'messages[i].id'
EXEC sp_OAMethod @url, 'SetString', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages/'
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @messageId
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'Append', @success OUT, '?format=metadata&metadataHeaders=From&metadataHeaders=Subject'
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @resp
-- A message can disappear between the search and the metadata request.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 404
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
RETURN
END
PRINT 'Message: ' + @messageId
DECLARE @h int
SELECT @h = 0
EXEC sp_OAMethod @reply, 'SizeOfArray', @iTmp0 OUT, 'payload.headers'
WHILE (@h < @iTmp0)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @h
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'payload.headers[i].name'
EXEC sp_OAMethod @reply, 'StringOf', @sTmp1 OUT, 'payload.headers[i].value'
PRINT @sTmp0 + ': ' + @sTmp1
SELECT @h = @h + 1
END
SELECT @total = @total + 1
END
SELECT @i = @i + 1
END
SELECT @pageToken = ''
EXEC sp_OAMethod @page, 'JsonTypeOf', @iTmp0 OUT, 'nextPageToken'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @page, 'StringOf', @pageToken OUT, 'nextPageToken'
END
SELECT @more = @pageToken <> ''
END
PRINT 'Matching messages read: ' + @total
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @page
END
GO