GAS Code To Get Email Information Into Google Sheets

Introduction

We have already seen how to send emails directly from Google Sheets. In this post, we will learn the opposite: how to retrieve email information using Google Apps Script (or, for simplicity, GAS). We will assume that you have a valid Google account hence, the information you want to retrieve comes from your Gmail.

The GAS code relies on a search pattern to perform the searching in your mailbox. There are several options you can use in this pattern: email subject/title, the sender (name or email address), the recipient (name or email address), label, file type and several others. In other words, you can perform a powerful query in your mailbox directly from Google Sheets.

The process 

There are several steps involved to get the necessary email data. However, all of them have been presented in previous posts so we will avoid duplication here. Let’s see them in brief:

Step 1: Following the instructions described here (steps 1 to 4) create a new empty spreadsheet on your Google Drive.

Step 2: Then, follow these instructions (steps 2 to 5), so as to switch to the Script Editor. In there, you have to paste the code that you will find below.

Step 3: Finally, if you perform the steps 7 to 14 described in this post, you will be able to run the GAS code and get the results in your spreadsheet.

GAS code 

Here is the GAS code that performs the email search, which is based on the search method of the Gmail service. Read the code comments for more information, especially on the getEmailsUsingPattern function that contains many examples of how you can use the main one (getEmailInfo).

// Start of the GAS code.
 
/*
--------------------------------------------------
Written By:    Christos Samaras
Date:          27/12/2018
Last Updated:  05/01/2019
E-mail:        [email protected]
Site:          https://myengineeringworld.net
--------------------------------------------------
*/
 
function onOpen() 
{
  /*
    ------------------------------------------
    Creates a custom menu at the spreadsheet.
    ------------------------------------------
  */
  
  SpreadsheetApp.getUi()
      .createMenu('Get Emails')
      .addItem('Get Emails Using Pattern...', 'getEmailsUsingPattern')
      .addToUi();
}
 
function getEmailsUsingPattern()
{
  /*
    ----------------------------------------------------
    Retrieves email information using a search pattern.
    ----------------------------------------------------
  */
  
  // Set the search pattern. 
  // Subject/title example (one word search):
  const pattern = 'subject: geocoding';
  
  // Note, when you searching for the exact phrase (e.g. two words) the pattern should be like this:
  // Subject/title example (exact phrase):
  // const pattern = 'subject: "VBA help"';
  
  // Specific email or name of the sender:
  // const pattern = 'from: [email protected]';
  // const pattern = 'from: Disqus';
  
  // Specific email or name of the recipient:
  // const pattern = 'to: [email protected]';
  // const pattern = 'to: "Christos Samaras"';
  
  // Label example:
  // const pattern = 'label: My Engineering World';
  
  // File type example:
  // const pattern = 'has:spreadsheet';  
  
  // All emails (not recommended - it will take a lot of time):
  // const pattern = 'in: anywhere';  
  
  // For more options, check the next link:
  // https://support.google.com/mail/answer/7190
  
  // Finally, call the main function using the preferred pattern and the sheet name.
  getEmailInfo(pattern, 'Sheet3');
  
}
 
function getEmailInfo(searchPattern, sheetName) 
{
  /*
    ---------------------------------------------------------------------------
    Retrieves email information from the associated Gmail account based on the
    input search pattern. The results are written to the preferred sheet.
    ---------------------------------------------------------------------------
  */
  
  // Check if the search pattern parameter is empty.
  if(searchPattern === '') 
  {
    Browser.msgBox('Please provide a search pattern!');
    return;
  }
  
  // Check if the sheet name parameter is empty.
  if(sheetName === '') 
  {
    Browser.msgBox('Please provide a sheet name!');
    return;
  }
  
  // Get the sheet that will contain the data.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);  
 
  // Check that the sheet object is not null (i.e. the sheet name is correct).
  if(sheet == null)
  {
    Browser.msgBox('Invalid sheet name!');
    return;
  }
  
  // Write the headers.
  sheet.getRange('A1:D1').setValues([['Date', 'Name', 'Email', 'Subject']]);
  
  // Make the headers bold.
  sheet.getRange('A1:D1').setFontWeight('bold');
  
  // Set the first row that will contain the data (after headings).
  var startRow = 2;
  
  // Get the last row containing data (in column A).
  var lastRow = sheet.getRange("A1:A").getValues().filter(String).length;
  
  // Clear any existing data bellow the headers.
  if(lastRow > startRow)
    sheet.getRange(startRow, 1, lastRow, 4).clearContent();
    
  // Get all the threads for the specified search pattern.
  var threads =  GmailApp.search (searchPattern);
  
  // Check if there are threads for the particular search pattern.
  if(threads.length == 0)
  {
    Browser.msgBox('There are no threads for the specified pattern!');
    return;
  }
  
  // An empty array that will hold the email data.
  var emailInfo = [];
  
  // Loop through all the threads.
  for (var i = 0; i < threads.length; i++) 
  { 
    // Get all the email messages from the thread.
    var messages = threads[i].getMessages();
    
    // Loop through all the email messages.
    for (var k = 0; k < messages.length; k++) 
    {      
      // Try to get the name from the email (if possible).
      var matchesPattern = messages[k].getFrom().match(/s*"?([^"]*)"?s+<(.+)>/);
      
      // Temporary variables.
      var name;
      var email;
      
      if(matchesPattern) 
      {
        // Success, get the name and the email address.
        name = matchesPattern[1]; 
        email = matchesPattern[2]; 
      }
      else 
      {
        // Fail, get the name/email as one.
        name = 'N/A'; 
        email = messages[k].getFrom(); 
      }
 
      // Push the necessary information into the array (date, name, email, title).
      emailInfo.push([messages[k].getDate(), name, email, messages[k].getSubject()]);
    }
  }
  
  // Write the array data into the sheet.
  if(emailInfo.length > 0) 
    sheet.getRange(startRow, 1, emailInfo.length, 4).setValues(emailInfo);
  
  // Fit the width of the columns.
  sheet.autoResizeColumns(1, 4);
  
  // Inform the user about the process.
  Browser.msgBox('Information from ' + (emailInfo.length == 1 ? '1 email' : emailInfo.length + ' emails') + ' was successfully retrieved!');
  
}
 
// End of the GAS code.

Demonstration video 

In less than 20 seconds, you can see how I got information from 91 emails when searching my Gmail account for emails that have the subject “geocoding”.

Read also 

Send Multiple Emails From Google Sheets Using GAS

Page last updated: 17/06/2019