How to build an AI inbox dashboard with Gmail and Google Sheets

What this workflow does

This setup does not clean your inbox for you. It turns selected email threads into rows you can review every day. Each row stores the sender, subject, email body, summary, action category, urgency, owner, next action, deadline, confidence and Gmail link.

Start by labelling only the email threads that matter. That keeps newsletters, receipts and routine notifications out of the system while you test the workflow.

Step 1: create the Gmail label

In Gmail, create a label named AI Review. Apply it manually to a few important threads first. Once the dashboard works, add Gmail filters for client emails, leads, vendor messages, approvals or follow-ups.

Step 2: create the Google Sheet

Create a blank Google Sheet. Open Extensions, then Apps Script. Paste the script below, save it, and run setupInboxDashboard(). Google will ask for permission because the script reads labelled Gmail threads and writes rows into your Sheet.

The script creates one main tab named Inbox and dashboard tabs for Replies, Tasks, Waiting On, Decisions, Overdue and Low Confidence.

Copy-paste Apps Script

This script imports labelled Gmail threads, creates the dashboard tabs and can classify rows through OpenRouter if you add an API key.

/**
 * AI Inbox Dashboard for Gmail + Google Sheets.
 *
 * What this does:
 * 1. Imports Gmail threads with the label "AI Review" into the active Sheet.
 * 2. Optionally classifies unprocessed rows with OpenRouter.
 * 3. Creates dashboard tabs for Replies, Tasks, Waiting On, Decisions, Overdue, and Low Confidence.
 *
 * Setup:
 * 1. Create a Google Sheet.
 * 2. Extensions -> Apps Script.
 * 3. Paste this file.
 * 4. Run setupInboxDashboard().
 * 5. In Gmail, create a label named AI Review.
 * 6. Add that label to emails you want tracked.
 * 7. Optional for AI classification:
 *    Project Settings -> Script properties:
 *    OPENROUTER_API_KEY = your key
 *    OPENROUTER_MODEL = a free model from https://openrouter.ai/models?max_price=0
 */

const CONFIG = {
  inboxSheet: "Inbox",
  gmailLabel: "AI Review",
  processedLabel: "AI Logged",
  maxThreadsPerRun: 25,
  maxBodyChars: 6000,
  openRouterUrl: "https://openrouter.ai/api/v1/chat/completions",
  defaultStatus: "New"
};

const HEADERS = [
  "Imported At",
  "Thread ID",
  "Email Date",
  "From",
  "Subject",
  "Snippet",
  "Body",
  "Gmail Link",
  "Summary",
  "Category",
  "Urgency",
  "Owner",
  "Next Action",
  "Deadline",
  "Confidence",
  "Status",
  "Notes"
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Inbox Dashboard")
    .addItem("Setup dashboard", "setupInboxDashboard")
    .addItem("Import labelled Gmail threads", "importLabeledEmails")
    .addItem("Classify empty rows with OpenRouter", "classifyRowsWithOpenRouter")
    .addItem("Create dashboard views", "createDashboardViews")
    .addItem("Create 30 minute trigger", "createThirtyMinuteTrigger")
    .addToUi();
}

function setupInboxDashboard() {
  const ss = SpreadsheetApp.getActive();
  const sheet = getOrCreateSheet_(CONFIG.inboxSheet);
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  sheet.setFrozenRows(1);
  sheet.getRange(1, 1, 1, HEADERS.length).setFontWeight("bold");
  sheet.autoResizeColumns(1, HEADERS.length);
  sheet.hideColumns(7);

  getOrCreateGmailLabel_(CONFIG.gmailLabel);
  getOrCreateGmailLabel_(CONFIG.processedLabel);
  createDashboardViews();

  ss.toast("Inbox dashboard is ready. Label emails as AI Review, then run Import.");
}

function importLabeledEmails() {
  const sheet = getOrCreateSheet_(CONFIG.inboxSheet);
  ensureHeaders_(sheet);

  const reviewLabel = getOrCreateGmailLabel_(CONFIG.gmailLabel);
  const processedLabel = getOrCreateGmailLabel_(CONFIG.processedLabel);
  const existingIds = getExistingThreadIds_(sheet);
  const threads = reviewLabel.getThreads(0, CONFIG.maxThreadsPerRun);
  const rows = [];

  threads.forEach(thread => {
    const threadId = thread.getId();
    if (existingIds.has(threadId)) return;

    const messages = thread.getMessages();
    const lastMessage = messages[messages.length - 1];
    const plainBody = trimBody_(lastMessage.getPlainBody());

    rows.push([
      new Date(),
      threadId,
      lastMessage.getDate(),
      lastMessage.getFrom(),
      thread.getFirstMessageSubject(),
      thread.getSnippet(),
      plainBody,
      thread.getPermalink(),
      "",
      "",
      "",
      "",
      "",
      "",
      "",
      CONFIG.defaultStatus,
      ""
    ]);

    thread.addLabel(processedLabel);
  });

  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, HEADERS.length).setValues(rows);
    sheet.autoResizeColumns(1, HEADERS.length);
  }

  SpreadsheetApp.getActive().toast(`Imported ${rows.length} new thread(s).`);
}

function classifyRowsWithOpenRouter() {
  const apiKey = PropertiesService.getScriptProperties().getProperty("OPENROUTER_API_KEY");
  const model = PropertiesService.getScriptProperties().getProperty("OPENROUTER_MODEL");

  if (!apiKey || !model) {
    throw new Error("Set OPENROUTER_API_KEY and OPENROUTER_MODEL in Script properties first.");
  }

  const sheet = getOrCreateSheet_(CONFIG.inboxSheet);
  ensureHeaders_(sheet);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  const values = sheet.getRange(2, 1, lastRow - 1, HEADERS.length).getValues();

  values.forEach((row, index) => {
    const rowNumber = index + 2;
    const category = row[9];
    const body = row[6];
    if (category || !body) return;

    const result = classifyEmail_(apiKey, model, {
      from: row[3],
      subject: row[4],
      snippet: row[5],
      body
    });

    sheet.getRange(rowNumber, 9, 1, 7).setValues([[
      result.summary || "",
      normalizeCategory_(result.category),
      result.urgency || "Normal",
      result.owner || "",
      result.next_action || "",
      result.deadline || "",
      result.confidence || ""
    ]]);
  });

  SpreadsheetApp.getActive().toast("Classification complete.");
}

function createDashboardViews() {
  const views = [
    ["Replies", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!J2:J="Reply")}'],
    ["Tasks", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!J2:J="Task")}'],
    ["Waiting On", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!J2:J="Waiting On")}'],
    ["Decisions", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!J2:J="Decision")}'],
    ["Overdue", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!N2:N<TODAY(), Inbox!N2:N<>"", Inbox!P2:P<>"Done")}'],
    ["Low Confidence", '={Inbox!A1:Q1; FILTER(Inbox!A2:Q, Inbox!O2:O<0.75, Inbox!O2:O<>"")}']
  ];

  views.forEach(([name, formula]) => {
    const sheet = getOrCreateSheet_(name);
    sheet.clear();
    sheet.getRange("A1").setFormula(formula);
    sheet.setFrozenRows(1);
  });
}

function createThirtyMinuteTrigger() {
  ScriptApp.newTrigger("importLabeledEmails")
    .timeBased()
    .everyMinutes(30)
    .create();
}

function classifyEmail_(apiKey, model, email) {
  const prompt = [
    "Classify this email for an execution dashboard.",
    "",
    "Return only valid JSON with these keys:",
    "summary, category, urgency, owner, next_action, deadline, confidence",
    "",
    "Allowed category values:",
    "Reply, Task, Waiting On, Decision, Calendar, Archive, Ignore",
    "",
    "Allowed urgency values:",
    "Low, Normal, Urgent",
    "",
    "Rules:",
    "- Use Waiting On when someone else needs to act next.",
    "- Use Decision when the user must choose or approve something.",
    "- Use Task when work needs to be done outside email.",
    "- Use Reply when the main next action is sending a response.",
    "- Use Archive or Ignore only when no action is needed.",
    "- Deadline should be YYYY-MM-DD or empty.",
    "- Confidence should be a number from 0 to 1.",
    "",
    `From: ${email.from}`,
    `Subject: ${email.subject}`,
    `Snippet: ${email.snippet}`,
    "",
    "Body:",
    email.body
  ].join("\n");

  const response = UrlFetchApp.fetch(CONFIG.openRouterUrl, {
    method: "post",
    muteHttpExceptions: true,
    contentType: "application/json",
    headers: {
      Authorization: `Bearer ${apiKey}`,
      "HTTP-Referer": "https://docs.google.com",
      "X-Title": "AI Inbox Dashboard"
    },
    payload: JSON.stringify({
      model,
      messages: [
        { role: "system", content: "You classify emails into structured workflow data." },
        { role: "user", content: prompt }
      ],
      temperature: 0.1
    })
  });

  const status = response.getResponseCode();
  const text = response.getContentText();
  if (status < 200 || status >= 300) {
    throw new Error(`OpenRouter error ${status}: ${text}`);
  }

  const parsed = JSON.parse(text);
  const content = parsed.choices[0].message.content.trim();
  return JSON.parse(content.replace(/^```json\s*/i, "").replace(/```$/i, "").trim());
}

function getOrCreateSheet_(name) {
  const ss = SpreadsheetApp.getActive();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

function ensureHeaders_(sheet) {
  const firstRow = sheet.getRange(1, 1, 1, HEADERS.length).getValues()[0];
  if (firstRow.join("") !== HEADERS.join("")) {
    sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
    sheet.setFrozenRows(1);
  }
}

function getOrCreateGmailLabel_(name) {
  return GmailApp.getUserLabelByName(name) || GmailApp.createLabel(name);
}

function getExistingThreadIds_(sheet) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return new Set();
  return new Set(sheet.getRange(2, 2, lastRow - 1, 1).getValues().flat().filter(Boolean));
}

function trimBody_(body) {
  return String(body || "")
    .replace(/\s+/g, " ")
    .trim()
    .slice(0, CONFIG.maxBodyChars);
}

function normalizeCategory_(value) {
  const text = String(value || "").toLowerCase().replace(/[_-]/g, " ");
  if (text.includes("reply")) return "Reply";
  if (text.includes("waiting")) return "Waiting On";
  if (text.includes("decision")) return "Decision";
  if (text.includes("calendar") || text.includes("meeting")) return "Calendar";
  if (text.includes("archive")) return "Archive";
  if (text.includes("ignore")) return "Ignore";
  return "Task";
}

Step 3: import labelled emails

After the script is saved, reload the Sheet. You should see an Inbox Dashboard menu. Click Import labelled Gmail threads. The script reads up to 25 threads with the AI Review label, adds them to the Inbox tab and marks them with an AI Logged label so they are not imported again.

Use Create 30 minute trigger only after the manual import works. That trigger checks for newly labelled threads on a schedule.

Step 4: classify rows

You have two practical options. Use Gemini inside Google Sheets if the AI function is available in your account. If it is not available, use the OpenRouter path inside Apps Script.

=AI("Classify this email as Reply, Task, Waiting On, Decision, Calendar, Archive, or Ignore. Return only the category.", G2)

Step 5: use the dashboard views

The script creates filtered tabs from the main Inbox tab. Use them as your morning dashboard.

• Replies: emails where the next action is a response.
• Tasks: work that belongs in your task system.
• Waiting On: threads where someone else needs to act.
• Decisions: approvals, choices and trade-offs.
• Overdue: rows with a past deadline and no done status.
• Low Confidence: rows that need human review before you trust the classification.

Safety rules

Do not auto-send emails from this first version. Do not delete threads automatically. Let the system classify, draft and organise. You still approve replies, decisions and calendar commitments.

If you use OpenRouter or any external model, the script sends email text to that provider. Test with non-sensitive email first and remove the Body column from prompts if you only want to classify snippets.

Official references

• GmailApp reference
• SpreadsheetApp reference
• Apps Script installable triggers
• UrlFetchApp reference
• Google Sheets AI function help
• OpenRouter quickstart
• OpenRouter free models