Access a Google Workspace mailbox with a service account
See more GMail REST API Examples
A Google Workspace super administrator must authorize the service account client ID for https://www.googleapis.com/auth/gmail.metadata in Admin console domain-wide delegation. Enable the Gmail API in the Cloud project. Set SubEmailAddress to a Gmail-enabled user in that organization. The profile response identifies the mailbox represented by me.
Store the downloaded JSON service-account key in gmail-service-account.json in a private working directory. AuthGoogle signs a JWT with the key and exchanges it for an access token. Repeat that exchange when the token expires. For other operations, change Scope and grant that scope in the Admin console. This workflow is for organization-controlled applications. Personal Gmail accounts can use offline authorization with a refresh token.
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @iTmp1 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @key int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @key OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @key, 'LoadFile', @success OUT, 'gmail-service-account.json'
IF @success <> 1
BEGIN
PRINT 'Could not load the service-account key from the private directory.'
EXEC @hr = sp_OADestroy @key
RETURN
END
DECLARE @auth int
EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @auth OUT
EXEC sp_OAMethod @key, 'Emit', @sTmp0 OUT
EXEC sp_OASetProperty @auth, 'JsonKey', @sTmp0
EXEC sp_OASetProperty @auth, 'Scope', 'https://www.googleapis.com/auth/gmail.metadata'
EXEC sp_OASetProperty @auth, 'SubEmailAddress', 'user@example.com'
EXEC sp_OASetProperty @auth, 'ExpireNumSeconds', 3600
-- Obtain a token for the named Workspace user. Keep the computer clock accurate.
DECLARE @connection int
EXEC @hr = sp_OACreate 'Chilkat.Socket', @connection OUT
EXEC sp_OASetProperty @connection, 'RequireSslCertVerify', 1
EXEC sp_OAMethod @connection, 'Connect', @success OUT, 'www.googleapis.com', 443, 1, 10000
IF @success <> 1
BEGIN
PRINT 'Could not establish the Google token connection.'
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
RETURN
END
EXEC sp_OAMethod @auth, 'ObtainAccessToken', @success OUT, @connection
EXEC sp_OAMethod @connection, 'Close', @success OUT, 1000
IF @success <> 1
BEGIN
PRINT 'Token exchange failed. Check the key, delegated scopes, user, and computer clock.'
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
EXEC sp_OAGetProperty @auth, 'AccessToken', @sTmp0 OUT
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
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://gmail.googleapis.com/gmail/v1/users/me/profile', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
EXEC @hr = sp_OADestroy @http
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 @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
EXEC @hr = sp_OADestroy @http
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 @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'emailAddress'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'emailAddress', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Expected emailAddress in the response.'
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'emailAddress'
PRINT 'Authorized mailbox: ' + @sTmp0
EXEC sp_OAMethod @reply, 'IntOf', @iTmp0 OUT, 'messagesTotal'
PRINT 'Messages: ' + @iTmp0
EXEC @hr = sp_OADestroy @key
EXEC @hr = sp_OADestroy @auth
EXEC @hr = sp_OADestroy @connection
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
END
GO