Google Apps Script to Automate & Export All Gmail Mails inside Google Sheets Rows & Columns

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

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);
}

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

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

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<form>

<label for="emailCount">Enter the number of emails to fetch:</label>

</form>

</body>

</html>