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

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.

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 @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