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

Apply a Gmail label to search results in batches

See more GMail REST API Examples

Choose a Gmail search query and an existing label name. Resolve the label ID, collect all matching message IDs before changing any labels, then submit at most 1,000 IDs per batchModify call. Empty searches send no updates. For large mailboxes, spool IDs to persistent storage. Completed batches stay applied if a later request fails; adding the same label again is safe.

Authorize the application with https://www.googleapis.com/auth/gmail.modify. The example reads the access token from gmail-token.json in a private working directory. Authorize a desktop application.

Google documentation

Chilkat SQL Server Downloads

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

    EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://gmail.googleapis.com/gmail/v1/users/me/labels', @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
        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
        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
        RETURN
      END
    EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'labels'
    IF @iTmp0 <> 4
      BEGIN

        PRINT 'Expected a list of labels.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        RETURN
      END
    DECLARE @label int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @label OUT

    EXEC sp_OAMethod @reply, 'FindRecord2', @success OUT, 'labels', 'name', 'To review', 1, @label
    IF @success <> 1
      BEGIN

        PRINT 'No label with that exact name. Check the name or create the label first.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        EXEC @hr = sp_OADestroy @label
        RETURN
      END
    DECLARE @labelId int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @labelId OUT

    EXEC sp_OAMethod @label, 'StringOfSb', @success OUT, 'id', @labelId
    IF @success <> 1
      BEGIN

        PRINT 'Could not read the label ID.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @reply
        EXEC @hr = sp_OADestroy @label
        EXEC @hr = sp_OADestroy @labelId
        RETURN
      END
    DECLARE @ids int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @ids OUT

    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=500'
    DECLARE @searchQuery nvarchar(4000)
    SELECT @searchQuery = 'has:attachment'
    EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, '&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 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @pageToken OUT

    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_OAGetProperty @pageToken, 'Length', @iTmp0 OUT
        IF @iTmp0 > 0
          BEGIN
            EXEC sp_OAMethod @url, 'Append', @success OUT, '&pageToken='
            EXEC sp_OAMethod @pageToken, 'GetAsString', @sTmp1 OUT
            EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @sTmp1
            EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
          END
        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 @label
            EXEC @hr = sp_OADestroy @labelId
            EXEC @hr = sp_OADestroy @ids
            EXEC @hr = sp_OADestroy @baseUrl
            EXEC @hr = sp_OADestroy @url
            EXEC @hr = sp_OADestroy @pageToken
            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 @label
            EXEC @hr = sp_OADestroy @labelId
            EXEC @hr = sp_OADestroy @ids
            EXEC @hr = sp_OADestroy @baseUrl
            EXEC @hr = sp_OADestroy @url
            EXEC @hr = sp_OADestroy @pageToken
            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 @label
            EXEC @hr = sp_OADestroy @labelId
            EXEC @hr = sp_OADestroy @ids
            EXEC @hr = sp_OADestroy @baseUrl
            EXEC @hr = sp_OADestroy @url
            EXEC @hr = sp_OADestroy @pageToken
            RETURN
          END
        --  Gmail can omit messages when the search or mailbox is empty.
        DECLARE @count int
        EXEC sp_OAMethod @reply, 'SizeOfArray', @count OUT, 'messages'
        DECLARE @i int
        SELECT @i = 0
        WHILE (@i < @count)
          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

                PRINT 'Could not retain a message ID.'
                EXEC @hr = sp_OADestroy @token
                EXEC @hr = sp_OADestroy @http
                EXEC @hr = sp_OADestroy @resp
                EXEC @hr = sp_OADestroy @reply
                EXEC @hr = sp_OADestroy @label
                EXEC @hr = sp_OADestroy @labelId
                EXEC @hr = sp_OADestroy @ids
                EXEC @hr = sp_OADestroy @baseUrl
                EXEC @hr = sp_OADestroy @url
                EXEC @hr = sp_OADestroy @pageToken
                RETURN
              END
            SELECT @i = @i + 1
          END
        SELECT @more = 0
        EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'nextPageToken'
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'nextPageToken'
            EXEC sp_OAMethod @pageToken, 'SetString', @success OUT, @sTmp0
            EXEC sp_OAGetProperty @pageToken, 'Length', @iTmp0 OUT
            SELECT @more = @iTmp0 > 0
          END
      END
    --  Search is complete. Apply the label to each group and track confirmed progress.
    DECLARE @request int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @request OUT

    DECLARE @done int
    SELECT @done = 0
    EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
    WHILE (@done < @iTmp0)
      BEGIN
        EXEC sp_OAMethod @request, 'Clear', NULL
        EXEC sp_OAMethod @labelId, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @request, 'UpdateString', @success OUT, 'addLabelIds[0]', @sTmp0
        DECLARE @n int
        SELECT @n = 0
        EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
        WHILE ((@n < 1000) and ((@done + @n) < @iTmp0))
          BEGIN
            EXEC sp_OASetProperty @request, 'I', @n
            EXEC sp_OAMethod @ids, 'StringAt', @sTmp0 OUT, @done + @n
            EXEC sp_OAMethod @request, 'UpdateString', @success OUT, 'ids[i]', @sTmp0
            SELECT @n = @n + 1
          END
        EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://gmail.googleapis.com/gmail/v1/users/me/messages/batchModify', @request, 'application/json', @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 @label
            EXEC @hr = sp_OADestroy @labelId
            EXEC @hr = sp_OADestroy @ids
            EXEC @hr = sp_OADestroy @baseUrl
            EXEC @hr = sp_OADestroy @url
            EXEC @hr = sp_OADestroy @pageToken
            EXEC @hr = sp_OADestroy @request
            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 @label
            EXEC @hr = sp_OADestroy @labelId
            EXEC @hr = sp_OADestroy @ids
            EXEC @hr = sp_OADestroy @baseUrl
            EXEC @hr = sp_OADestroy @url
            EXEC @hr = sp_OADestroy @pageToken
            EXEC @hr = sp_OADestroy @request
            RETURN
          END
        SELECT @done = @done + @n

        PRINT 'Messages labeled so far: ' + @done
      END

    PRINT 'Total messages labeled: ' + @done

    EXEC @hr = sp_OADestroy @token
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @reply
    EXEC @hr = sp_OADestroy @label
    EXEC @hr = sp_OADestroy @labelId
    EXEC @hr = sp_OADestroy @ids
    EXEC @hr = sp_OADestroy @baseUrl
    EXEC @hr = sp_OADestroy @url
    EXEC @hr = sp_OADestroy @pageToken
    EXEC @hr = sp_OADestroy @request


END
GO