How to Use Google Apps Script to Put Gmail Data into Google Spreadsheets

I tried using Google Apps Script to automatically record information from Gmail, like the sender (“From”), the date and time (“Date”), and the message itself (“Body”), into a Google Spreadsheet.

Here’s how you do it:

Creating a Google Apps Script Project

First, you create a new script inside a Google Spreadsheet.

Using the Gmail API

Next, you read emails using the Gmail API.

This is pretty easy because Google Apps Script supports Gmail.

Getting Email Data

You search for emails using certain rules and get the data you need, like who sent it, when it was sent, and what the message says.

Writing Data to the Spreadsheet

Finally, you put the data you got into the spreadsheet.

Below is a sample code that does this. It takes information from the latest five emails and records it in the spreadsheet.

function importEmailsToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var threads = GmailApp.getInboxThreads(0, 5);

  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 from = message.getFrom();
      var date = message.getDate();
      var body = message.getPlainBody();

      sheet.appendRow([from, date, body]);
    }
  }
}

When you run this code, you need to make sure the spreadsheet lets you in. If you need to filter the emails more or handle the data in a special way, you can change the script to do that.

For example, if you only want emails with a certain word in the subject, you can use the GmailApp.search method to find those emails and get the information you need.

function importEmailsWithBulletedListAndOptionalTitleFromSpecificSender() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var subject = 'YourSubject';           // The string to search for in the subject line
  var startDate = 'startYYYY/MM/DD';     // The date to start the search
  var endDate = 'endYYYY/MM/DD';         // The date to end the search
  var specificSender = 'sample@example.com'; // The email address of a specific sender
  var titleMarker = '#'; // The marker for titles
  var bulletMarker = '-'; // The marker for bulleted lists
  var replyMarker = '>';   // The marker for replies
  var signatureKeywords = ["Best regards,", "Sincerely,", "Regards,", "-----"]; // Keywords indicating a signature
  var sortAscending = true; // Set to 'true' for ascending order, 'false' for descending order
  var query = 'from:' + specificSender + ' subject:' + subject + ' after:' + startDate + ' before:' + endDate;
  var threads = GmailApp.search(query);
  var requiredRows = threads.reduce(function(count, thread) {
    return count + thread.getMessages().length;
  }, 0); // Calculate the number of rows that need to be written; Spreadsheet row limit (50,000 rows)

  // Get the position of the last filled row of existing data
  var lastFilledRow = sheet.getLastRow();

  // If the required number of rows exceeds the current number, add more rows
  var totalRowsNeeded = lastFilledRow + requiredRows;
  var currentRowCount = sheet.getMaxRows();
  if (totalRowsNeeded > currentRowCount) {
    sheet.insertRowsAfter(currentRowCount, totalRowsNeeded - currentRowCount);
  }

  // Below, processing the emails and writing data to the spreadsheet
  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 bodyLines = message.getPlainBody().split('\n');
      var bulletList = '';   // Bulleted list section
      var bodyText = '';     // The rest of the body text
      var inTitleSection = false;

      for (var k = 0; k < bodyLines.length; k++) {
        var line = bodyLines[k].trim();
        var isSignatureLine = signatureKeywords.some(keyword => line.startsWith(keyword));
        if (isSignatureLine) {
          break; // If the start of a signature is detected, ignore the remaining lines
        }
        if (line.startsWith(replyMarker)) {
          continue;
        }
        if (titleMarker && line.startsWith(titleMarker)) {
          bulletList += line + '\n';
          inTitleSection = true;
        } else if (bulletMarker && line.startsWith(bulletMarker)) {
          bulletList += line + '\n';
        } else {
          if (inTitleSection) {
            inTitleSection = false;
          } else {
            bodyText += line + '\n';
          }
        }
      }

      sheet.appendRow([
        message.getFrom(),    // Sender
        message.getDate(),    // Date
        message.getSubject(), // Subject
        bodyText,             // Body text
        bulletList            // Bulleted list
      ]);
    }
  }

  // Sort based on the date column
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    var range = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()); // Start from the first row
    range.sort({column: 2, ascending: sortAscending}); // Sort based on the second column (date)
  }

}

In this example, it looks for emails with “YourSubject” in the subject line and puts the sender, date, and message into the spreadsheet. You can change “YourSubject” to whatever you’re looking for.

Using Google Apps Script like this helps you manage your Gmail data efficiently and automate your work. But remember, there’s a limit to how many emails you can get. It seems like about 1,000 is the most.

I hope this article helps you learn more about Google Apps Script.

googleappscript