Welcome folks today in this blog post we will be exporting
gmail mails inside google sheets
rows and columns in google apps script. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new code.gs
file and copy paste the following code
code.gs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Export Emails') .addItem('Export Emails', 'showDialog') .addItem('Clear All Data', 'clearSheetData') .addToUi(); } function clearSheetData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.getDataRange().clearContent(); } function showDialog() { var html = HtmlService.createHtmlOutputFromFile('index') .setWidth(250) .setHeight(100); SpreadsheetApp.getUi().showModalDialog(html, 'Enter the number of emails to fetch'); } function getEmailCount(emailCount) { exportEmails(emailCount); } function exportEmails(emailCount) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var threads = GmailApp.getInboxThreads(0, emailCount); var messageData = []; for (var i = 0; i < threads.length; i++) { var messages = threads[i].getMessages(); for (var j = 0; j < messages.length; j++) { var message = messages[j]; var date = message.getDate(); var subject = message.getSubject(); var from = message.getFrom(); var body = message.getPlainBody(); var labels = threads[i].getLabels().join(","); messageData.push([date, subject, from, body, labels]); } } sheet.getRange(1, 1, messageData.length, messageData[0].length).setValues(messageData); } |
As you can see in the above javascript
code we are adding the menu
items to the google sheets. And then we also have to show the html
modal window where we allow the user to enter the number of emails
to fetch.
index.html
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<!DOCTYPE html> <html> <head> <base target="_top"> </head> <body> <form> <label for="emailCount">Enter the number of emails to fetch:</label> <input type="number" id="emailCount" name="emailCount" required> <input type="button" value="OK" onclick="google.script.run.getEmailCount(document.getElementById('emailCount').value)"> </form> </body> </html> |