Check Gmail from a scheduled job after one-time consent
See more GMail REST API Examples
First run desktop authorization with gmail.readonly and offline access. This applies to personal Gmail and Workspace accounts subject to organization policies. Configure a job to run this code using the same private working directory and user identity. Each run refreshes the token and displays up to ten inbox message headers.
Save the original OAuth client credentials in a private gmail-client.json containing client_id and client_secret. The existing gmail-token.json supplies the refresh token. Use protected credential storage in a deployed application and serialize runs sharing these files. An invalid_grant response requires reconnecting the account. External OAuth apps in Testing normally receive refresh tokens that expire after seven days when Gmail scopes are requested. Google token expiration guidance.
This is a repeatable inbox check. To process each mailbox change across runs, see mailbox synchronization.
Chilkat SQL Server Downloads
--
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 @files int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @files OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Recover the previous complete file if a run stopped during replacement.
DECLARE @fileStatus int
EXEC sp_OAMethod @files, 'FileExists3', @fileStatus OUT, 'gmail-token.json'
IF @fileStatus < 0
BEGIN
PRINT 'Could not inspect gmail-token.json.'
EXEC @hr = sp_OADestroy @files
RETURN
END
IF @fileStatus = 0
BEGIN
DECLARE @backupStatus int
EXEC sp_OAMethod @files, 'FileExists3', @backupStatus OUT, 'gmail-token.json.previous'
IF @backupStatus < 0
BEGIN
PRINT 'Could not inspect the backup file.'
EXEC @hr = sp_OADestroy @files
RETURN
END
IF @backupStatus = 1
BEGIN
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-token.json.previous', 'gmail-token.json'
IF @success <> 1
BEGIN
PRINT 'Could not restore the previous complete file.'
EXEC @hr = sp_OADestroy @files
RETURN
END
SELECT @fileStatus = 1
END
END
DECLARE @token int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT
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 @files
EXEC @hr = sp_OADestroy @token
RETURN
END
EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'refresh_token', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Authorize this account for offline access first.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
RETURN
END
DECLARE @client int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @client OUT
EXEC sp_OAMethod @client, 'LoadFile', @success OUT, 'gmail-client.json'
IF @success <> 1
BEGIN
PRINT 'Could not load the private OAuth client file.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
RETURN
END
EXEC sp_OAMethod @client, 'JsonTypeOf', @iTmp0 OUT, 'client_id'
EXEC sp_OAMethod @client, 'JsonTypeOf', @iTmp1 OUT, 'client_secret'
IF (@iTmp0 <> 1) or (@iTmp1 <> 1)
BEGIN
PRINT 'gmail-client.json must contain client_id and client_secret.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
DECLARE @form int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @form OUT
EXEC sp_OASetProperty @form, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @form, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'grant_type', 'refresh_token'
EXEC sp_OAMethod @client, 'StringOf', @sTmp0 OUT, 'client_id'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'client_id', @sTmp0
EXEC sp_OAMethod @client, 'StringOf', @sTmp0 OUT, 'client_secret'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'client_secret', @sTmp0
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @form, 'AddParam', NULL, 'refresh_token', @sTmp0
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://oauth2.googleapis.com/token', @form, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'The token endpoint did not return an HTTP response.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Token refresh failed. 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, 'StringOfEquals', @iTmp0 OUT, 'error', 'invalid_grant', 1
IF @iTmp0 = 1
BEGIN
PRINT 'Reconnect this account with interactive authorization.'
END
END
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Could not parse the refresh response.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Expected access_token in the response.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
-- Google usually omits the refresh token. Preserve it unless a replacement is returned.
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'refresh_token', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'refresh_token'
EXEC sp_OAMethod @reply, 'UpdateString', @success OUT, 'refresh_token', @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @reply, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
END
EXEC sp_OAMethod @reply, 'HasMember', @iTmp0 OUT, 'scope'
EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp1 OUT, 'scope'
IF (@iTmp0 <> 1) and (@iTmp1 = 1)
BEGIN
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'scope'
EXEC sp_OAMethod @reply, 'UpdateString', @success OUT, 'scope', @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @reply, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
END
-- Prepare the complete replacement before moving the last committed file.
EXEC sp_OAMethod @reply, 'WriteFile', @success OUT, 'gmail-token.json.new'
IF @success <> 1
BEGIN
PRINT 'Could not write the replacement file.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @files, 'FileExists', @iTmp0 OUT, 'gmail-token.json'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @files, 'FileExists', @iTmp0 OUT, 'gmail-token.json.previous'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @files, 'FileDelete', @success OUT, 'gmail-token.json.previous'
IF @success <> 1
BEGIN
PRINT 'Could not rotate the previous backup.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
END
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-token.json', 'gmail-token.json.previous'
IF @success <> 1
BEGIN
PRINT 'Could not retain the previous complete file.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
END
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-token.json.new', 'gmail-token.json'
IF @success <> 1
BEGIN
PRINT 'Could not commit the replacement. The previous complete file is retained.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://gmail.googleapis.com/gmail/v1/users/me/messages?labelIds=INBOX&maxResults=10', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
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 @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
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 @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
DECLARE @ids int
EXEC @hr = sp_OACreate 'Chilkat.StringTable', @ids OUT
DECLARE @i int
SELECT @i = 0
EXEC sp_OAMethod @reply, 'SizeOfArray', @iTmp0 OUT, 'messages'
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @i
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[i].id'
EXEC sp_OAMethod @ids, 'Append', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ids, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @ids
RETURN
END
SELECT @i = @i + 1
END
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
SELECT @i = 0
EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OAMethod @url, 'SetString', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages/'
EXEC sp_OAMethod @ids, 'StringAt', @sTmp1 OUT, @i
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @sTmp1
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
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @url
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 @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @url
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 @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @url
RETURN
END
EXEC sp_OAMethod @ids, 'StringAt', @sTmp0 OUT, @i
PRINT 'Message: ' + @sTmp0
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 @i = @i + 1
END
EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
PRINT 'Inbox messages checked: ' + @iTmp0
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @client
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @form
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @url
END
GO