Simple & Free Way to Get your Gmail Emails into Spreadsheet / Excel

When it comes to organizing our online life, getting email contents into a spreardsheet or an Excel file becomes important. If you are currently doing that manually with a lot of copy-paste work, you will end up spending time and exerting effort on things that matter less than things that matter the most. With the help of powerful free tools like Google Apps Script, you can automate that.

Here in this blog and the video tutorial listed at the end, I will guide you how to get Gmail emails into a spreadsheet automatically and how to export them to an Excel sheet! let me go straight and explain to you how to achieve that with simplicity.

Gmail Filters

I assume that you need to get certain emails that have specific texts or that are received from a specific sender. Therefore, we will first create a Gmail filter that will automatically filter these emails whether new or old ones.

Creating Gmail filters can be done in different ways. One way is to use the search bar and click on the options icon that is at the right side as demonstrated in the screen recording below. Here you can filter out the emails that, for example, come from a specific sender and/or the ones containing specific texts in the email body or subject line. In this screen recording, I assign a label named Nak Collection to every email that comes from the address [email protected] and that has the word ramadan in it. In our script, we will use this label to read all the emails that are labeled with Nak Collection.

View post on imgur.com

Creating the Spreadsheet Script/Program

Intuitively, we need a spreadsheet to get our emails into it. To do that, you need to visit http://sheets.google.com and create a new spreadsheet or use any of your existing spreadsheets. To start a script for your spreadsheet, you will need to go to the Tools menu and click Script Editor option. This will open a new window where you can type JavaScript codes using Google Apps Script. By default, the following empty function presents:

function myFunction() {
  
}

We will change the name of the function myFunction() to getGmailEmails().

It is important to know that Gmail messages are grouped in conversations or threads. The program below loops through every single email that is assigned the label and, when it finishes, it removes the label from the emails so that it does not read the same emails again the next time the program runs. Every message will be stored in a variable named message.

function getGmailEmails(){
  var label = GmailApp.getUserLabelByName('Nak Collection');
  var threads = label.getThreads();
  for(var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    for (var j = 0; j <messages.length; j++){
      var message = messages[j];
    }
    threads[i].removeLabel(label);
  }
}

Now, we understand that the message variable actually has the desired email data, but we do not yet know how to get its details like the email date, sender, subject, body, etc. To get this information, we can create a new function called extractDetails() that takes this message variable as a parameter. The following code snippet stores this information in the variables dateTime, subjectText, senderDetails and bodyContent.

function extractDetails(message){
  var dateTime = message.getDate();
  var subjectText = message.getSubject();
  var senderDetails = message.getFrom();
  var bodyContent = message.getPlainBody();
}

All we need to do next is to populate the values of these variables to our spreadsheet. We can do that by getting the current active sheet in our spreadsheet, then appending a new row with this information. This is done using the following code snippet:

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  activeSheet.appendRow([dateTime, senderDetails, subjectText, bodyContents]);

I am not a mind-reader, but I guess that the question that is coming to your mind would be how to run this program, right? Well, one simple way is to create a custom menu in our spreadsheet so that when we click it, the functions will be executed. This menu should appear automatically once you open the spreadsheet. In Google Apps Script, there is a dedicated function that gets executed automatically immediately after the spreadsheet is opened. It is onOpen(e). The following code snippet creates a cutom menu called Gmail Manager which has an option called Get Emails to get all the emails that are labeled Nak Collection.

var ui = SpreadsheetApp.getUi();
function onOpen(e){
  ui.createMenu("Gmail Manager").addItem("Get Emails", "getGmailEmails").addToUi();
}

The full program that extracts the emails and populates them to the spreadsheet would then be:

var ui = SpreadsheetApp.getUi();
function onOpen(e){
  ui.createMenu("Gmail Manager").addItem("Get Emails", "getGmailEmails").addToUi();
}

function getGmailEmails(){
  var label = GmailApp.getUserLabelByName('Nak Collection');
  var threads = label.getThreads();
  for(var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    for (var j = 0; j <messages.length; j++){
      var message = messages[j];
      extractDetails(message);
    }
    threads[i].removeLabel(label);
  }
}

function extractDetails(message){
  var dateTime = message.getDate();
  var subjectText = message.getSubject();
  var senderDetails = message.getFrom();
  var bodyContents = message.getPlainBody();
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  activeSheet.appendRow([dateTime, senderDetails, subjectText, bodyContents]);
}

Granting Permissions & Running the Program

When you are done with adding this code, you click on save and name the project whatever name you like and reload the spreadsheet. When you reload it, you will see the Gmail Manager menu beside the Help menu. When you click it, the program will get executed and you will need to grant this program the required permissions to run on your Gmail account. The following screen recording shows how to grant the required permissions to your Gmail extractor project:

View post on imgur.com

Once you grant the required permissions, you can click the menu again and you will see your emails getting brought to your spreadsheet and arranged in new rows.

How to Get Only the First or Last Message in a Conversation?

The previous code loops through all of the messages within a thread/conversation. Some of you guys are interested in getting only the first message or the last one in a thread/conversation. It is really easy to do that by modifying our getGmailEmails() function. Here we loop through the messages variable using a for loop, right?

Well, to get the first message only, we do not need to loop through it but we would rather take the first message whose index is 0 into consideration (the first element index in a Javascript array starts from 0 not 1). The code to get the first message only would then be:

function getGmailEmails(){
  var label = GmailApp.getUserLabelByName('Nak Collection');
  var threads = label.getThreads();
  for(var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    var message = messages[0];
    extractDetails(message);
    threads[i].removeLabel(label);
  }
}

A similar argument is applicable to how to get the last message in a thread. The difference is that the number of the last message would be equal to the length of the messages array. Since array elements in Google Apps Script start from 0 index not 1, then the index of the last message would be messages.length - 1. We use this calculation because we do not know, in advance, how many messages the current Gmail thread is having. The final code to get the last message in thread would be:

function getGmailEmails(){
  var label = GmailApp.getUserLabelByName('Nak Collection');
  var threads = label.getThreads();
  for(var i = threads.length - 1; i >=0; i--){
    var messages = threads[i].getMessages();
    var message = messages[messages.length - 1];
    extractDetails(message);
    threads[i].removeLabel(label);
  }
}

Export Spreadsheet to Excel

If you wish to export your Google spreadsheet to an Excel file, you can do that by downloading it from File>Download>Microsoft Excel (.xlsx). This is a manual work to do although this process can be programmed.

---

I published a YouTube video to my Code Wondo channel that explains this in quite good details. Here is the video: