Introduction

This case explains how to use Export Emails to Sheets to make a spreadsheet of all incoming emails, with each day’s emails in a separate Google Sheets tab.

By creating a separate tab for each day, you can quickly review, organize, and analyze your emails for any given date—especially useful for high-volume inboxes or busy workflows. This approach prevents important details from getting lost and helps you maintain a clear, historical record of email communications.

Setup overview

1. Setup Export Emails to Sheets job
2. After the spreadsheet is created, add Google Sheets script to it
3. Configure triggers so that spreadsheet is updated as soon as new email is added to spreadheet

Setup Export Emails to Sheets job

In this

Google Sheets script

1. Open your Google Sheet that contains the emails.
2. Go to Extensions → Apps Script to open the Script Editor.
3. Paste the script below into the script editor.
4. Edit the script if needed
5. Run the script

The Google Sheets script which will create a new Google sheet for each day is the following:

function createDailyTabs() {
  // 1) Name of your master sheet (tab)
  var MASTER_SHEET_NAME = "Sheet1";
  
  // 2) Get the entire master spreadsheet
  var masterSS = SpreadsheetApp.getActiveSpreadsheet();
  
  // 3) Find the master sheet by name, or fallback to the first sheet
  var masterSheet = masterSS.getSheetByName(MASTER_SHEET_NAME);
  if (!masterSheet) {
    var allSheets = masterSS.getSheets();
    if (allSheets.length === 0) {
      throw new Error("No sheets exist in this spreadsheet.");
    }
    masterSheet = allSheets[0];
    Logger.log(
      "Master sheet named '" + MASTER_SHEET_NAME + 
      "' not found. Using first sheet: '" + masterSheet.getName() + "'."
    );
  }
  
  // 4) Read both values and formulas from the master sheet
  var range = masterSheet.getDataRange();
  var masterValues = range.getValues();      // Display values
  var masterFormulas = range.getFormulas();  // Actual formulas ("" if none)
  
  if (masterValues.length <= 1) {
    Logger.log("No data rows found in the master sheet.");
    return;
  }
  
  // 5) Identify the header row and important columns
  var headers = masterValues[0];
  var dateTimeIndex = headers.indexOf("Date & Time Sent");
  var messageIdIndex = headers.indexOf("Message ID");
  
  if (dateTimeIndex === -1) {
    throw new Error("'Date & Time Sent' column not found in header.");
  }
  if (messageIdIndex === -1) {
    throw new Error("'Message ID' column not found in header.");
  }
  
  // Number of columns (for setting row data later)
  var numCols = range.getNumColumns();
  
  // 6) Group the rows by date (format: YYYY-MM-DD), skipping the header (i=0).
  var rowsByDate = {};
  for (var i = 1; i < masterValues.length; i++) {
    var rowVals = masterValues[i];
    var rowFormulas = masterFormulas[i];
    
    var dateCellVal = rowVals[dateTimeIndex];
    var dateObj = new Date(dateCellVal);
    var dateStr = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "yyyy-MM-dd");
    
    if (!rowsByDate[dateStr]) {
      rowsByDate[dateStr] = [];
    }
    
    rowsByDate[dateStr].push({
      values: rowVals,
      formulas: rowFormulas
    });
  }
  
  // 7) For each date, either create a new tab or reuse an existing one
  for (var dateKey in rowsByDate) {
    var dailyRows = rowsByDate[dateKey]; // array of { values: [...], formulas: [...] }
    
    // EXAMPLE: rename to "Emails " if you prefer
    var tabName = "Emails " + dateKey;
    
    var dailySheet = masterSS.getSheetByName(tabName);
    if (!dailySheet) {
      // Create a new sheet (tab) for this date
      dailySheet = masterSS.insertSheet(tabName);
      Logger.log("Created new tab for " + dateKey);
    } else {
      Logger.log("Appending to existing tab for " + dateKey);
    }
    
    // 8) If the tab is empty, add a header row (text) and copy styling + column widths
    if (dailySheet.getLastRow() === 0) {
      // Put headers in row 1
      dailySheet.appendRow(headers);
      // Copy the styling from masterSheet row 1 to dailySheet row 1
      copyHeaderStyles(masterSheet, dailySheet);
      // Copy column widths
      for (var c = 1; c <= numCols; c++) {
        var colWidth = masterSheet.getColumnWidth(c);
        dailySheet.setColumnWidth(c, colWidth);
      }
    }
    
    // 9) Collect existing Message IDs from the daily tab to avoid duplicates
    var existingData = dailySheet.getDataRange().getValues();
    var dailyHeaders = existingData[0];
    var dailyMsgIdIndex = dailyHeaders.indexOf("Message ID");
    
    if (dailyMsgIdIndex === -1) {
      throw new Error("'Message ID' column not found in tab: " + tabName);
    }
    
    var existingMessageIds = {};
    for (var r = 1; r < existingData.length; r++) {
      var rowMsgId = existingData[r][dailyMsgIdIndex];
      if (rowMsgId) {
        existingMessageIds[rowMsgId] = true;
      }
    }
    
    // 10) Insert new rows, preserving formulas
    var startRowBefore = dailySheet.getLastRow();
    for (var j = 0; j < dailyRows.length; j++) {
      var rowObj = dailyRows[j];
      var thisVals = rowObj.values;
      var thisFormulas = rowObj.formulas;
      var thisMsgId = thisVals[messageIdIndex];
      
      if (!existingMessageIds[thisMsgId]) {
        var finalRow = [];
        for (var col = 0; col < numCols; col++) {
          if (thisFormulas[col] && thisFormulas[col].length > 0) {
            // Use the formula (e.g. "=HYPERLINK(...)")
            finalRow[col] = thisFormulas[col];
          } else {
            // Use the plain displayed value
            finalRow[col] = thisVals[col];
          }
        }
        
        // Insert a new blank row at the bottom
        dailySheet.insertRowAfter(dailySheet.getLastRow());
        var newRowIndex = dailySheet.getLastRow() + 1;
        
        // Set the row's data
        dailySheet.getRange(newRowIndex, 1, 1, numCols).setValues([finalRow]);
      }
    }
    
    var endRowAfter = dailySheet.getLastRow();
    
    // 11) If new rows were appended, fix their formatting (row height & normal style)
    if (endRowAfter > startRowBefore) {
      var newlyAddedRowCount = endRowAfter - startRowBefore;
      var firstNewRow = startRowBefore + 1;
      
      // (a) Set minimal row height (e.g., 20) for all newly added rows
      dailySheet.setRowHeightsForced(firstNewRow, newlyAddedRowCount, 20);
      
      // (b) Force newly added rows to normal style
      var newRowsRange = dailySheet.getRange(firstNewRow, 1, newlyAddedRowCount, numCols);
      newRowsRange.setFontWeight("normal");   // no bold
      newRowsRange.setFontColor("black");     // black text
      newRowsRange.setBackground("white");    // white background
    }
  }
  
  Logger.log(
    "Finished creating/appending daily tabs.\n" +
    "Rows are now forced to normal black text on white background.\n" +
    "Formulas are preserved, no duplicates, header styling remains."
  );
}


/**
 * Copy header styles from the master sheet's row 1 to the daily sheet's row 1, cell by cell.
 * This replicates background color, font color, font size, bold/italic, alignment, etc.
 */
function copyHeaderStyles(masterSheet, dailySheet) {
  var lastColumn = masterSheet.getLastColumn();
  
  // Master header range and daily header range
  var masterHeaderRange = masterSheet.getRange(1, 1, 1, lastColumn);
  var dailyHeaderRange  = dailySheet.getRange(1, 1, 1, lastColumn);
  
  // All of these return 2D arrays. Because it's only 1 row, we access [0].
  var backgrounds        = masterHeaderRange.getBackgrounds()[0];
  var fontColors         = masterHeaderRange.getFontColors()[0];
  var fontWeights        = masterHeaderRange.getFontWeights()[0];
  var fontFamilies       = masterHeaderRange.getFontFamilies()[0];
  var fontLines          = masterHeaderRange.getFontLines()[0];
  var fontSizes          = masterHeaderRange.getFontSizes()[0];
  var fontStyles         = masterHeaderRange.getFontStyles()[0];
  var hAligns            = masterHeaderRange.getHorizontalAlignments()[0];
  var vAligns            = masterHeaderRange.getVerticalAlignments()[0];
  
  // Apply them to the daily sheet's header
  dailyHeaderRange.setBackgrounds([backgrounds]);
  dailyHeaderRange.setFontColors([fontColors]);
  dailyHeaderRange.setFontWeights([fontWeights]);
  dailyHeaderRange.setFontFamilies([fontFamilies]);
  dailyHeaderRange.setFontLines([fontLines]);
  dailyHeaderRange.setFontSizes([fontSizes]);
  dailyHeaderRange.setFontStyles([fontStyles]);
  dailyHeaderRange.setHorizontalAlignments([hAligns]);
  dailyHeaderRange.setVerticalAlignments([vAligns]);
  
  // Optionally copy row height from the master header row
  var headerRowHeight = masterSheet.getRowHeight(1);
  dailySheet.setRowHeight(1, headerRowHeight);
}

Create a Trigger via the Apps Script Editor (UI)

In your Google Sheet, go to Extensions > Apps Script.
In the left sidebar of the Script Editor, click Triggers (it looks like an alarm clock).
Click Add Trigger (bottom right).
Set the function to run, e.g. createDailyTabs (or whatever your main function is).
Under “Select event source,” choose Time-driven.
Specify the frequency (e.g., daily at midnight).

Save.

Example

The example Google Sheet which the above is here