Table Messages

Description

No description available

Supported Operations

Examples

SSIS

Use Gmail Connector in API Source component to read data or in API Destination component to read/write data:

Read from Messages table using API Source

API Source - Gmail

Read and write Gmail data effortlessly. Send, manage, and sync messages, attachments, labels, and threads — almost no coding required.

Gmail

Messages

There are no parameters to configure.

Read/write to Messages table using API Destination

API Destination - Gmail

Read and write Gmail data effortlessly. Send, manage, and sync messages, attachments, labels, and threads — almost no coding required.

Gmail

Messages

Select

There are no parameters to configure.

ODBC application

Use these SQL queries in your ODBC application data source:

Get all messages

SELECT * FROM Messages

Get a specific message by ID

SELECT * FROM Messages WHERE Id='1910abb2f0a52aee'

Get all messages for a specified user

SELECT * FROM Messages
LIMIT 10 -- fetches first 10 messages (fetching all can be very slow)
WITH (UserID='me')
--WITH (UserID='firstname.lastname@domainname.com')

Get all messages with search condition(s)

SELECT * 
FROM Messages
--Use WITH clause --OR-- Key column(s) in WHERE clause. If you use Id in WHERE clause then Search parameter is ignored
--WHERE [Id] = '18fb7747b0136726' 
WITH(
	Search='after:<<yearstart+1d|~|yyyy/MM/dd,FUN_TO_DATE>> before:<<today-1d|~|yyyy/MM/dd,FUN_TO_DATE>>'  --search for mail sent between some dates (after year start plus one day and before yesterday (i.e. today-1d))
--	Search='after:2024/01/31 before:2024/05/25'  --use of static date

--for more examples refer https://support.google.com/mail/answer/7190

--  Search='FREE PRODUCT' --search example for=> has words anywhere in the subject or body
--  Search='"FREE PRODUCT"' --search example for=> has exact phrase anywhere in the subject or body
--  Search='subject:FREE PRODUCT' --search example for=> has subject (contains any word)
--  Search='subject:(FREE PRODUCT)' --search example for => has subject (contains exact phrase)
--  Search='Rfc822msgid:<008601dae391$fda78bf0$f8f6a3d0$@zappysys.com>' --search example for => Search by Rfc822MessageID column (Alternate Message ID for searching - you can find from get_messages or  get_message endpoint output
--  Search='to:david' --search example for => to (name)
--  Search='to:david.smith@gmail.com' --search example for => to (email)
--  Search='from:david' --search example for => from (name)
--  Search='from:david.smith@gmail.com' --search example for => from (email)
--  Search='from:(Platform Notifications) OR from:(no-reply@accounts.google.com) ' --search example for => from (name or email)
--  Search='cc:david' --search example for => cc (name)
--  Search='bcc:david' --search example for => bcc (name)
--  Search='is:unread AND subject:(Share request for)' --search example for => multiple conditions (AND)
--  Search='is:unread OR subject:(Share request for)' --search example for => multiple conditions (OR)
--  Search='from:someuser@example.com' --search example for => from sender=from:someuser@example.com
--  Search='from:a@company.com OR from:b@company.com' --search example for => from sender (OR)
--  Search='from:amy' --search example for => from sender by name=from:amy
--  Search='from:amy OR from:bob ' --search example for => from sender by name (OR)
--  Search='has:attachment' --search example for => has attachment=has:attachment
--  Search='larger:10M' --search example for => has attachment (larger than 10MB)
--  Search='smaller:10M' --search example for => has attachment (smaller than 10MB)
--  Search='smaller:10M' --search example for => has attachment (smaller than 10MB)
--  Search='1024' --search example for => size (exact bytes)
--  Search='unread=is:unread' --search example for => is unread=is:unread
--  Search='is:read' --search example for => is read=is:read
--  Search='after:2004/04/16' --search example for => date after=after:2004/04/16
--  Search='before:2004/04/16' --search example for => date before=before:2004/04/16
--  Search='older_than:2d' --search example for => age older than (2days)
--  Search='older_than:4y' --search example for => age older than (4years)
--  Search='newer_than:2d' --search example for => age newer than (2days)
--  Search='newer_than:4m' --search example for => age newer than (4months)
--  Search='from:someuser@example.com rfc822msgid:<008601dae391zzzzzzz> is:unread' --search example for => from specific sender with specific message id(RFC822) and unread emails only=from:someuser@example.com rfc822msgid:<somemsgid@example.com> is:unread
--  Search='in:sent after:2014/01/01 before:2014/02/01' --search example for => sent date range (static dates)
--  Search='in:sent after:<<yearstart-1day+22hour|~|yyyy/MM/dd,FUN_TO_DATE>> before:<<today-1day|~|yyyy/MM/dd,FUN_TO_DATE>>' --search example for => sent date range (dynamic dates). You can use keywords like now,today,yesterday,monthstart,monthend,yearstart,yearend,weekstart,weekend along with operator + / - [hour,minute,second,day,month,year]
	  
)

Send a message

INSERT INTO Messages(From,To,Cc,Bcc,ReplyTo,Subject,Body,MimeType)
VALUES('me'
,'bob@zappysys.com,sam@zappysys.com'
,'some-cc1@zappysys.com,some-cc2@zappysys.com'
,'some-bcc1@zappysys.com,some-bcc1@zappysys.com'
,'reply-to@gmail.com'
,'This is sample HTML email'
,'This is <u>Bob''s</u> HTML email. Sent on <b>*** <<FUN_NOW>> ** </b>'
,'text/html' --or use text/plain for Plain format
)

/*

OR call using SELECT statement 

SELECT * FROM send_message
WITH(From='me'
,To='bob@zappysys.com,sam@zappysys.com'
,Cc='some-cc1@zappysys.com,some-cc2@zappysys.com'
,Bcc='some-bcc1@zappysys.com,some-bcc1@zappysys.com'
,ReplyTo='reply-to@gmail.com'
,Subject='This is sample HTML email'
,Body='This is <u>Bob''s</u> HTML email. Sent on <b>*** <<FUN_NOW>> ** </b>'
,MimeType='text/html' --or use text/plain for Plain format
)

*/

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get all messages

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Messages';

EXEC (@MyQuery) AT [LS_TO_GMAIL_IN_GATEWAY];

Get a specific message by ID

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Messages WHERE Id=''1910abb2f0a52aee''';

EXEC (@MyQuery) AT [LS_TO_GMAIL_IN_GATEWAY];

Get all messages for a specified user

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Messages
LIMIT 10 -- fetches first 10 messages (fetching all can be very slow)
WITH (UserID=''me'')
--WITH (UserID=''firstname.lastname@domainname.com'')';

EXEC (@MyQuery) AT [LS_TO_GMAIL_IN_GATEWAY];

Get all messages with search condition(s)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM Messages
--Use WITH clause --OR-- Key column(s) in WHERE clause. If you use Id in WHERE clause then Search parameter is ignored
--WHERE [Id] = ''18fb7747b0136726'' 
WITH(
	Search=''after:<<yearstart+1d|~|yyyy/MM/dd,FUN_TO_DATE>> before:<<today-1d|~|yyyy/MM/dd,FUN_TO_DATE>>''  --search for mail sent between some dates (after year start plus one day and before yesterday (i.e. today-1d))
--	Search=''after:2024/01/31 before:2024/05/25''  --use of static date

--for more examples refer https://support.google.com/mail/answer/7190

--  Search=''FREE PRODUCT'' --search example for=> has words anywhere in the subject or body
--  Search=''"FREE PRODUCT"'' --search example for=> has exact phrase anywhere in the subject or body
--  Search=''subject:FREE PRODUCT'' --search example for=> has subject (contains any word)
--  Search=''subject:(FREE PRODUCT)'' --search example for => has subject (contains exact phrase)
--  Search=''Rfc822msgid:<008601dae391$fda78bf0$f8f6a3d0$@zappysys.com>'' --search example for => Search by Rfc822MessageID column (Alternate Message ID for searching - you can find from get_messages or  get_message endpoint output
--  Search=''to:david'' --search example for => to (name)
--  Search=''to:david.smith@gmail.com'' --search example for => to (email)
--  Search=''from:david'' --search example for => from (name)
--  Search=''from:david.smith@gmail.com'' --search example for => from (email)
--  Search=''from:(Platform Notifications) OR from:(no-reply@accounts.google.com) '' --search example for => from (name or email)
--  Search=''cc:david'' --search example for => cc (name)
--  Search=''bcc:david'' --search example for => bcc (name)
--  Search=''is:unread AND subject:(Share request for)'' --search example for => multiple conditions (AND)
--  Search=''is:unread OR subject:(Share request for)'' --search example for => multiple conditions (OR)
--  Search=''from:someuser@example.com'' --search example for => from sender=from:someuser@example.com
--  Search=''from:a@company.com OR from:b@company.com'' --search example for => from sender (OR)
--  Search=''from:amy'' --search example for => from sender by name=from:amy
--  Search=''from:amy OR from:bob '' --search example for => from sender by name (OR)
--  Search=''has:attachment'' --search example for => has attachment=has:attachment
--  Search=''larger:10M'' --search example for => has attachment (larger than 10MB)
--  Search=''smaller:10M'' --search example for => has attachment (smaller than 10MB)
--  Search=''smaller:10M'' --search example for => has attachment (smaller than 10MB)
--  Search=''1024'' --search example for => size (exact bytes)
--  Search=''unread=is:unread'' --search example for => is unread=is:unread
--  Search=''is:read'' --search example for => is read=is:read
--  Search=''after:2004/04/16'' --search example for => date after=after:2004/04/16
--  Search=''before:2004/04/16'' --search example for => date before=before:2004/04/16
--  Search=''older_than:2d'' --search example for => age older than (2days)
--  Search=''older_than:4y'' --search example for => age older than (4years)
--  Search=''newer_than:2d'' --search example for => age newer than (2days)
--  Search=''newer_than:4m'' --search example for => age newer than (4months)
--  Search=''from:someuser@example.com rfc822msgid:<008601dae391zzzzzzz> is:unread'' --search example for => from specific sender with specific message id(RFC822) and unread emails only=from:someuser@example.com rfc822msgid:<somemsgid@example.com> is:unread
--  Search=''in:sent after:2014/01/01 before:2014/02/01'' --search example for => sent date range (static dates)
--  Search=''in:sent after:<<yearstart-1day+22hour|~|yyyy/MM/dd,FUN_TO_DATE>> before:<<today-1day|~|yyyy/MM/dd,FUN_TO_DATE>>'' --search example for => sent date range (dynamic dates). You can use keywords like now,today,yesterday,monthstart,monthend,yearstart,yearend,weekstart,weekend along with operator + / - [hour,minute,second,day,month,year]
	  
)';

EXEC (@MyQuery) AT [LS_TO_GMAIL_IN_GATEWAY];

Send a message

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Messages(From,To,Cc,Bcc,ReplyTo,Subject,Body,MimeType)
VALUES(''me''
,''bob@zappysys.com,sam@zappysys.com''
,''some-cc1@zappysys.com,some-cc2@zappysys.com''
,''some-bcc1@zappysys.com,some-bcc1@zappysys.com''
,''reply-to@gmail.com''
,''This is sample HTML email''
,''This is <u>Bob''''s</u> HTML email. Sent on <b>*** <<FUN_NOW>> ** </b>''
,''text/html'' --or use text/plain for Plain format
)

/*

OR call using SELECT statement 

SELECT * FROM send_message
WITH(From=''me''
,To=''bob@zappysys.com,sam@zappysys.com''
,Cc=''some-cc1@zappysys.com,some-cc2@zappysys.com''
,Bcc=''some-bcc1@zappysys.com,some-bcc1@zappysys.com''
,ReplyTo=''reply-to@gmail.com''
,Subject=''This is sample HTML email''
,Body=''This is <u>Bob''''s</u> HTML email. Sent on <b>*** <<FUN_NOW>> ** </b>''
,MimeType=''text/html'' --or use text/plain for Plain format
)

*/';

EXEC (@MyQuery) AT [LS_TO_GMAIL_IN_GATEWAY];