Turn forwarded emails into project tasks

The best moment to capture a task is the moment it appears — and at Northwind tasks appear as emails all day long. A client asks for a change, a supplier flags a delay, a teammate forwards something with “can you handle this?” Copying each one into a tracker by hand is the step that quietly gets skipped, and then the work lives only in someone’s memory of an email they half-read.

This script removes that step. Forward any email to [email protected] and, within minutes, a row appears in the Projects sheet — captured from the subject line, filed under the right client, and tagged with a priority. The client and priority come from simple hashtags you type into the forwarded body, so the person forwarding stays in control without leaving their inbox.

What you’ll need

• A Google Sheet with a tab for tasks. The script appends five columns in this order: task title, client, priority, status, and a timestamp.
• The spreadsheet’s ID, taken from its URL, set in the config below.
• An address — here [email protected] — that the script account can search. This is normally an alias or group that delivers into the mailbox the script runs against.

The script

// The spreadsheet that holds the project task list.
const TASKS_SHEET_ID = '1abcTasksSheetId';

// The address tasks are forwarded to. The script searches for mail
// addressed here, so it must land in the script account's mailbox.
const TASKS_ADDRESS = '[email protected]';

// Used when no #client tag is found in the forwarded body.
const DEFAULT_CLIENT = 'unsorted';

/**
 * Finds recently forwarded task emails, extracts a client and priority
 * from hashtags in the body, and appends one row per email to the
 * Projects sheet. Designed to run on a short time-driven trigger.
 */
function processForwardedTasks() {
  // 1. Find unread mail sent to the task address in the last day.
  const threads = GmailApp.search(
    `to:${TASKS_ADDRESS} is:unread newer_than:1d`
  );

  // 2. Nothing forwarded recently — stop before opening the Sheet.
  if (threads.length === 0) {
    Logger.log('No forwarded tasks to process.');
    return;
  }

  const sheet = SpreadsheetApp.openById(TASKS_SHEET_ID).getSheets()[0];

  let added = 0;
  for (const thread of threads) {
    // 3. Use the most recent message — that's the forward itself.
    const message = thread.getMessages().slice(-1)[0];
    const body = message.getPlainBody();

    // 4. Clean the "Fwd:" prefix off the subject for the task title.
    const title = message.getSubject().replace(/^Fwd:\s*/i, '');

    // 5. First #hashtag in the body names the client; fall back if none.
    const client = (body.match(/#([a-z0-9-]+)/i) || [, DEFAULT_CLIENT])[1];

    // 6. #high or #urgent anywhere in the body bumps the priority.
    const priority = /#high|#urgent/i.test(body) ? 'high' : 'normal';

    // 7. Append the task row and mark the message read so it isn't
    //    picked up again on the next run.
    sheet.appendRow([title, client, priority, 'open', new Date()]);
    message.markRead();
    added++;
  }

  Logger.log(`Added ${added} task(s) from forwarded email.`);
}

How it works

1. The script searches Gmail for unread mail addressed to the task address within the last day — the combination of is:unread and the date window keeps each run small and fast.
2. If nothing matches, it logs a message and returns before even opening the spreadsheet, so an idle period costs almost nothing.
3. For each thread it takes the last message, which is the forward you just sent rather than any earlier reply in the conversation.
4. It strips a leading Fwd: from the subject so the task title reads cleanly in the Sheet.
5. It scans the body for the first hashtag of the form #client-name and uses that as the client. If there is no hashtag, it falls back to unsorted so the task is never silently lost.
6. It checks the body for #high or #urgent and sets the priority accordingly, defaulting to normal.
7. It appends a row — title, client, priority, status open, and a timestamp — then marks the message read. Marking it read is what stops the same email being captured twice on the next run.

Example run

You forward an email to [email protected]. The original subject is “Homepage hero needs new copy”, and you add two lines to the top of the body before sending:

#acme #urgent
Client wants this live before the campaign launch.

On the next run the script captures it and appends this row to the Projects sheet:

Task title	Client	Priority	Status	Created
Homepage hero needs new copy	acme	high	open	2026-05-30 09:15

Forward an email with no hashtags and the row still lands — filed under unsorted with normal priority — so it surfaces in a triage view rather than disappearing.

Trigger it

Forwarding should feel like it “just works”, so run the script often:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose processForwardedTasks, event source Time-driven, type Minutes timer, and pick Every 10 minutes.
4. Save, and approve the authorisation prompt the first time it runs.

Watch out for

• The script reads only mail addressed to TASKS_ADDRESS. If that alias does not deliver into the mailbox the script runs against, the search returns nothing and no tasks are captured.
• It marks messages read to avoid duplicates. If you mark a captured email unread again, it will be re-captured on the next run. Treat “read” as “already filed”.
• The client tag matches the first hashtag in the body. If the forwarded email happens to contain an unrelated hashtag higher up, that will be picked up as the client. Put your #client tag on the first line to be safe.
• newer_than:1d means an email forwarded more than a day before the trigger runs will be missed. If the trigger is paused, widen the window before re-enabling it.
• Hashtag matching for the client allows letters, numbers, and hyphens only. A client name with spaces or other characters needs a hyphenated tag — #crane-co rather than #crane co.
• The task title is whatever the forwarded subject says. A vague subject makes a vague task; edit the subject before forwarding when it helps.