Save Gmail Messages to a Google Spreadsheet

June 04, 2019

The Google Script will save the body of email messages from Gmail to the currently active worksheet inside your Google Spreadsheet. You need to specify the Gmail search query and the sheet ID where the matching messages are to be exported. It saves the text content of the message sans any HTML tags or images.

To get started, paste the code in the script editor of a Google Spreadsheet and run SaveEmail from the Run menu.

Also see: Save Gmail Attachment to Google Drive

var SEARCH_QUERY = 'label:inbox is:unread to:me';

/*
 Credit: Alexander Ivanov
 https://gist.github.com/contributorpw/70e04a67f1f5fd96a708
*/

function getEmails_(q) {
  var emails = [];
  var threads = GmailApp.search(q);
  for (var i in threads) {
    var msgs = threads[i].getMessages();
    for (var j in msgs) {
      emails.push([
        msgs[j]
          .getBody()
          .replace(/<.+?>/g, '\n')
          .replace(/^\s*\n/gm, '')
          .replace(/^\s*/gm, '')
          .replace(/\s*\n/gm, '\n'),
      ]);
    }
  }
  return emails;
}

function appendData_(sheet, array2d) {
  sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);
}

function saveEmails() {
  var array2d = getEmails_(SEARCH_QUERY);
  if (array2d) {
    appendData_(SpreadsheetApp.getActiveSheet(), array2d);
  }
}

Gmail Google Apps Script Google Sheets

ShareWhatsAppTwitterFacebook

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory