Log new emails to a Google Sheet

1 · Prepare

Put these pieces in place

• Create a Sheet tab named Email log.
• Put these headers in row 1: Date, From, Subject, Thread link, Message ID.
• Open the script from that spreadsheet with Extensions, then Apps Script, so the code writes to the right file.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SHEET_NAME = 'Email log'; // The tab that receives the rows. It must already exist.
const GMAIL_SEARCH = 'is:unread newer_than:1d'; // The same text you would type in the Gmail search box. Change it to choose which mail is logged.
const CONVERSATIONS_PER_RUN = 25; // The largest number of conversations one run reads, which keeps each run quick.
const DATE_FORMAT = 'yyyy-MM-dd HH:mm'; // How the date is written in column A. Change it to yyyy-MM-dd for the day only.

function logNewEmailsToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  if (!sheet) throw new Error('Add a Sheet tab named ' + SHEET_NAME + ' before running this function.');

  const loggedIds = new Set();
  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) {
    sheet.getRange(2, 5, lastRow - 1, 1).getValues().forEach((row) => {
      loggedIds.add(String(row[0]));
    });
  }

  /** @type {string[][]} */
  const newRows = [];
  GmailApp.search(GMAIL_SEARCH, 0, CONVERSATIONS_PER_RUN).forEach((thread) => {
    const threadLink = 'https://mail.google.com/mail/u/0/#inbox/' + thread.getId();
    thread.getMessages().forEach((message) => {
      const messageId = message.getId();
      if (loggedIds.has(messageId)) return;
      loggedIds.add(messageId);
      const sender = message.getFrom();
      const subject = message.getSubject();
      newRows.push([
        Utilities.formatDate(message.getDate(), Session.getScriptTimeZone(), DATE_FORMAT),
        sender.startsWith('=') ? "'" + sender : sender,
        subject.startsWith('=') ? "'" + subject : subject,
        threadLink,
        messageId
      ]);
    });
  });

  if (newRows.length === 0) return;
  sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, 5).setValues(newRows);
  Logger.log('Added ' + newRows.length + ' rows to ' + SHEET_NAME + '.');
}

4 · Run

Follow these steps in order

1. Make the tab and its headers

   Add a tab named Email log with the five headers in row 1. Column E holds the message ID, which is how the script knows what it already logged.

2. Paste and save the function

   In the spreadsheet, choose Extensions, then Apps Script. Replace the starter function with this one and save.

3. Run one small test

   Choose logNewEmailsToSheet in the function dropdown above the code, then click Run. Approve access when Google asks. The run reads at most 25 conversations. A search returns whole conversations, so one conversation can add several rows.

4. Run it a second time

   Click Run again without changing anything. No new rows appear, because every message ID is already in column E.

5. Hide the ID column

   Right-click the column E header and choose Hide column. The script still reads it, and the tab stays easy to read.

5 · Confirm

How to tell it worked

The Email log tab holds one row per recent message, and the link in column D opens that conversation in Gmail. A second run in a row adds nothing.

If the result is different

Troubleshooting

The run stops with "Add a Sheet tab named Email log before running this function."

Rename the tab to Email log, with a capital E and a lowercase l, or change SHEET_NAME in the code to the name of your tab.

A message was logged twice.

Check that column E still holds the message IDs. Sorting the tab is fine, but clearing or deleting that column loses the record of what was already logged. Also check that no one changed the headers so that the IDs moved out of column E.

The run finishes but no rows are added.

Type is:unread newer_than:1d into the Gmail search box. If Gmail finds nothing either, change GMAIL_SEARCH in the code, for example to is:unread newer_than:3d.

The thread link opens the wrong mailbox.

Change the 0 in u/0 in the code to the position of the account that holds this mail, counting from 0. The link opens the first account signed in to that browser.