Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.0.0+

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.

Google documentation

Chilkat SQL Server Downloads

SQL Server
--
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