1. Introduction
2. Setup Overview
3. Set up Export Emails to Sheets Job
4. Create Google Sheets Script
5. Create a Trigger to Run the Script

Introduction

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

By having a separate tab for each day, you can easily check, organize, and analyze your emails for any date—especially helpful for busy inboxes or workflows. This method keeps important details from being missed and helps you keep a clear, historical record of emails.

You will have a separate sheet (tab) for each day.

Setup Overview

Here are the steps to make this work. You will set up Export Emails to Sheets to fill your spreadsheet in real time. Then, add a script to the spreadsheet – this script will create daily sheets (tabs) and add rows with that day’s emails. Lastly, create a trigger so the sheet updates automatically on changes or a schedule.

The specific steps are:

1. Set up Export Emails to Sheets job
2. Once the spreadsheet is created, add Google Sheets script to it
3. After authorizing the script and ensuring it works, set up triggers so the spreadsheet updates when a new email is added

Here’s a detailed look at all the steps.

Set up Export Emails to Sheets Job

First, make an export job by following these steps:

1. Log in to cloudHQ with the Gmail account you want to export (e.g., bob@company.com)
2. Open the Export Emails to Sheets wizard at this link: https://www.cloudhq.net/sheets_wizard
3. Choose the Backup or Save Emails Messages option
4. Select the INBOX label
5. You can select a folder for all spreadsheets and emails. For example, you may choose to place these emails in a Google Shared Drive.
   

6. Start the export process

The export will begin and may take up to 15 minutes to create the spreadsheet.

Create Google Sheets Script

Once the spreadsheet is ready, you need to create a Google Sheets script

1. Open your Google Sheet with 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 necessary
5. Run the script

The Google Sheets script that creates a new sheet for each day is:

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 to Run the Script

Now create a trigger so the script runs whenever a new email is added. You can create the trigger in the Apps Script Editor as follows:

1. In your Google Sheet, go to Extensions > Apps Script.
2. In the left sidebar of the Script Editor, click Triggers (it looks like an alarm clock).
3. Click Add Trigger (bottom right).
4. Choose the function to run. Select createDailyTabs.
5. Under Select event source, choose From spreadsheet.
6. Select on Change.
7. Save.