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 safely

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 and press Run. Approve the access when Google asks. The run reads at most 25 conversations, and a search returns whole conversations, so one conversation can add several rows.

4. Run it a second time

   Press Run again without changing anything. No new rows should appear, because every message ID is already in column E. That confirms a repeat run does not duplicate rows.

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 script says 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 the name in the code and in the editable values.

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.

Nothing is added and the log stays empty.

Try the search is:unread newer_than:1d in the Gmail search box. If Gmail finds nothing, change the search, for example to newer_than:3d.

The thread link opens the wrong mailbox.

The link uses the first account signed in to that browser, which is the u/0 part of the address. Change 0 to the position of the account that holds this mail.