Introduction to get Gmail with REST API
In this example, we will show how to read emails by calling Gmail REST API using SSIS. To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more.
Requirements
- First, you will need ZappySys SSIS PowerPack installed.
- Secondly, you will need SSDT installed.
Getting started
How to get information about Gmail with REST API
- First, you will need to create a developer application for Gmail. The following tutorial shows how to create an application for Google Drive, but the steps are similar:
- Secondly, go to SSDT and create a new SQL Server Integration Project.
- Subsequently, drag and drop a Data Flow task and double-click it:
Drag and drop Data Flow
- Also, in the connection manager create a new ZS-OAuth connection. Select Google as the OAuth provider. In scopes, add the following:
In addition, press generate token and add your credentials if necessary:1https://mail.google.com/
OAuth credentials to connect to Gmail
- To illustrate, the following example shows how to get the Gmail messages from an account. Check the option use credentials and select the OAuth connection just created. To get your current email messages, you can use the following URL:
1https://www.googleapis.com/gmail/v1/users/me/messages/
The filter used is the following:
1$.messages[*].idThe result displayed shows the ids of the messages. You can see the id of the message in Gmail in the URL when you click a message:
JSON Source Rest API
- If you want to check to information of a specific message you can use the following URL.
1https://www.googleapis.com/gmail/v1/users/me/messages/1632106341364d6f
Where 1632106341364d6f is the message id. The filter used will be the following:
1$.id
Gmail message information
The query shows the snippet (body of the message), the history id, size estimated and more.
- After this, we can use the following filter:
1$.payload.headers[*]
This will show the sender, the receiver, subject, references, message ID and more information:
Information about Gmail
How to get label information in Gmail with REST API
- First, you can use the following URL to get the Gmail labels:
Filter:1https://www.googleapis.com/gmail/v1/users/me/labels/
1$.labels[*].id - Also, the information displayed is the following:
Labels in Gmail
- Finally, the labels are the same than in Gmail:
Gmails labels, categories
How to get threads information in Gmail with REST API
- Finally, you can also get the thread information using the following URL:
The filter used will be the following:1https://www.googleapis.com/gmail/v1/users/me/threads
1$.threads[*].id - The information displayed will be the following:
Get Threads from Gmail
- If you press the Preview button, you will see the following information:
Thread information using Gmail
How to export the data from Gmail to a CSV file
- First, to export the data, we will use the ZS Trash Destination:
Export data from Gmail to a CSV file
- Finally, in the Trash Destination specify the path to store the data from Gmail:
CSV destination to store Gmail info
Conclusion
To conclude, we can say that ZappySys SSIS PowerPack is a powerful tool where you can get information from Gmail. This information can be exported to Excel, SQL Server, XML, JSON or any other format using SSIS. Download FREE Trial of SSIS PowerPack to find out more use cases like this.
References
For more information about Gmail, you can refer to these links:
