Send Weekly Project Health Dashboard with Google Sheets, Gpt-4o Mini and Gmail

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "FEVN6srdu14LSDK5",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Weekly Project Health Dashboard with AI Insights",
  "tags": [],
  "nodes": [
    {
      "id": "43816476-1801-49c7-b794-9063c78fed6d",
      "name": "Section: Error Handler",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        64
      ],
      "parameters": {
        "color": 7,
        "width": 556,
        "height": 368,
        "content": "## \u26a0\ufe0f Error Handler\nCatches any failure in the workflow and posts a Slack alert with the error message, failing node name, and execution ID. Wire the error output of any critical node here to prevent silent failures going unnoticed."
      },
      "typeVersion": 1
    },
    {
      "id": "237402f5-41c1-413d-968f-83459ff3ff78",
      "name": "On Workflow Error",
      "type": "n8n-nodes-base.errorTrigger",
      "position": [
        -464,
        224
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "fa835acd-9cf4-4b07-b027-1f85f39c5861",
      "name": "Slack \u2013 Send Error Alert1",
      "type": "n8n-nodes-base.slack",
      "position": [
        -208,
        224
      ],
      "parameters": {
        "text": "=error in the workflow please check ",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "C0AN1UGL0RM",
          "cachedResultName": "all-n8n-automations"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "credentials": {
        "slackOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "fa532c81-9d0c-4a6d-a063-760d1425fbd2",
      "name": "Overview: Weekly Project Health Dashboard",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1456,
        -944
      ],
      "parameters": {
        "width": 680,
        "height": 516,
        "content": "## \ud83d\udcca Weekly Project Health Dashboard\n\n### How it works\nEvery Friday at 5 PM, this workflow reads project data from three Google Sheets tabs (Alpha, Beta, Gamma), calculates a RAG (Red / Amber / Green) status for each project based on schedule variance, budget burn, and open issue count, then asks GPT-4o Mini to write a plain-English executive summary per project. The results are assembled into a styled HTML email and sent to your leadership distribution list. If the AI call fails at any point, a Slack alert fires immediately so nothing goes silently wrong.\n\n### Setup steps\n1. **Google Sheets** \u2014 Connect your Google Sheets OAuth2 credential and update the `documentId` in each of the three Read Sheet nodes to point to your own spreadsheet. Your sheet tabs must include columns: `project_name`, `planned_end`, `forecast_end`, `budget_total`, `budget_spent`, `open_issues_count`, `pct_complete`.\n2. **OpenAI** \u2014 Add your OpenAI API key under the GPT-4o Health Summary node credential.\n3. **Gmail** \u2014 Connect Gmail OAuth2 and update the `sendTo` field in Send Dashboard Email with your recipient address.\n4. **Slack** \u2014 Connect your Slack OAuth2 credential and set the correct channel ID in both Slack alert nodes.\n5. **Test run** \u2014 Use \"Execute Workflow\" manually to verify the email renders correctly before activating the Friday schedule."
      },
      "typeVersion": 1
    },
    {
      "id": "eeb90516-a4d0-4f2c-af99-46bbc1c3ec29",
      "name": "Section: Trigger",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -400,
        -560
      ],
      "parameters": {
        "color": 7,
        "width": 300,
        "height": 424,
        "content": "## \u23f0 Scheduled Trigger\nFires every Friday at 5 PM using a cron expression. To change the schedule, edit the cron field directly. The trigger fans out in parallel to all three sheet readers simultaneously."
      },
      "typeVersion": 1
    },
    {
      "id": "85202b01-e1fa-44e1-9160-3e7f41c2e299",
      "name": "Section: Read Project Sheets",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -80,
        -720
      ],
      "parameters": {
        "color": 7,
        "width": 440,
        "height": 732,
        "content": "## \ud83d\udccb Read Project Sheets\nPulls the latest data from three separate Google Sheets tabs \u2014 one per project. Each tab is read independently in parallel, then merged into a single dataset. Add more sheet nodes here if you need to track additional projects."
      },
      "typeVersion": 1
    },
    {
      "id": "5d1c42b5-64fa-4b1e-aa86-fb10db549387",
      "name": "Section: RAG Scoring and AI Summaries",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        432,
        -672
      ],
      "parameters": {
        "color": 7,
        "width": 688,
        "height": 784,
        "content": "## \ud83e\uddee RAG Scoring & AI Summaries\nMerges all rows, then scores each project across three dimensions \u2014 schedule variance, budget burn %, and open issue count. A composite score determines Red / Amber / Green status. GPT-4o Mini then writes a 3-line executive summary per project. If the AI call errors, the error branch routes to Slack rather than silently dropping the project."
      },
      "typeVersion": 1
    },
    {
      "id": "af608542-53ac-4f56-a2d6-a7b91b102ecb",
      "name": "Section: Email Assembly and Delivery",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1168,
        -624
      ],
      "parameters": {
        "color": 7,
        "width": 572,
        "height": 440,
        "content": "## \ud83d\udce7 Email Assembly & Delivery\nAggregates all project summaries into a single colour-coded HTML email with a portfolio status bar (\ud83d\udd34 Red / \ud83d\udfe1 Amber / \ud83d\udfe2 Green counts). Sent via Gmail OAuth2. Subject line includes the date and status counts for quick scanning in an inbox."
      },
      "typeVersion": 1
    },
    {
      "id": "bee5f513-7f04-4dad-8b1a-8e8174d6dc9d",
      "name": "Section: Credentials and Security",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1376,
        -16
      ],
      "parameters": {
        "color": 3,
        "width": 316,
        "height": 224,
        "content": "## \ud83d\udd10 Credentials & Security\nUse OAuth2 for Google Sheets, Gmail, and Slack. Use an API key credential for OpenAI. Never hardcode tokens in node parameters. Replace all email addresses and channel IDs with your own before sharing this template."
      },
      "typeVersion": 1
    },
    {
      "id": "de8ee607-8e9b-4778-8964-592e360d703f",
      "name": "Trigger: Every Friday at 5 PM",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -256,
        -352
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 17 * * 5"
            }
          ]
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "52211fe4-018b-434a-b565-6675f7bfe9f5",
      "name": "Read Project Alpha Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        0,
        -528
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 630003618,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=630003618",
          "cachedResultName": "Project_Alpha"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
          "cachedResultName": "Project Health Dashboard"
        }
      },
      "typeVersion": 4.3
    },
    {
      "id": "f82f7734-76df-4ddc-8fc7-53b3811d3b5f",
      "name": "Read Project Beta Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        0,
        -352
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1521958463,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=1521958463",
          "cachedResultName": "Project_Beta"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
          "cachedResultName": "Project Health Dashboard"
        }
      },
      "typeVersion": 4.3
    },
    {
      "id": "08b906c9-0aa9-4fd0-aa65-a110d65aa738",
      "name": "Read Project Gamma Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        0,
        -176
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 529192713,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=529192713",
          "cachedResultName": "Project_Gamma"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SPREADSHEET_ID",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
          "cachedResultName": "Project Health Dashboard"
        }
      },
      "typeVersion": 4.3
    },
    {
      "id": "7ac82de2-4c0c-409a-afdf-6e06b83f90d4",
      "name": "Merge All Project Data",
      "type": "n8n-nodes-base.merge",
      "position": [
        240,
        -384
      ],
      "parameters": {
        "numberInputs": 3
      },
      "typeVersion": 3
    },
    {
      "id": "c3d4b17c-81ba-4b72-86fc-e2be942286a3",
      "name": "Calculate RAG Status per Project",
      "type": "n8n-nodes-base.code",
      "position": [
        496,
        -368
      ],
      "parameters": {
        "jsCode": "// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n// RAG STATUS CALCULATOR\n// Reads summary row from each project sheet\n// Returns array of project health objects\n// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n\nconst allItems = $input.all();\n\n// Each item is one row from merged sheets\n// We look for the summary row (where column A = 'project_name')\n// that is the header, and the next row is the data.\n\n// Collect all rows across all inputs\nconst projects = [];\n\nfor (const item of allItems) {\n  const row = item.json;\n  \n  // The summary data row has numeric budget values\n  // Skip header rows and empty rows\n  if (!row.project_name || row.project_name === 'project_name') continue;\n  if (typeof row.budget_total === 'string' && isNaN(parseFloat(row.budget_total))) continue;\n\n  const name = row.project_name || 'Unknown Project';\n  const plannedEnd = row.planned_end || '';\n  const forecastEnd = row.forecast_end || '';\n  const budgetTotal = parseFloat(String(row.budget_total).replace(/[^0-9.]/g, '')) || 0;\n  const budgetSpent = parseFloat(String(row.budget_spent).replace(/[^0-9.]/g, '')) || 0;\n  const openIssues = parseInt(row.open_issues_count) || 0;\n  const pctComplete = parseFloat(String(row.pct_complete).replace('%', '')) || 0;\n\n  // \u2500\u2500 Budget Burn % \u2500\u2500\n  const burnPct = budgetTotal > 0 ? (budgetSpent / budgetTotal) * 100 : 0;\n\n  // \u2500\u2500 Schedule Variance in days \u2500\u2500\n  let scheduleVarianceDays = 0;\n  let scheduleVarianceText = 'On Track';\n  if (plannedEnd && forecastEnd) {\n    const planned = new Date(plannedEnd);\n    const forecast = new Date(forecastEnd);\n    scheduleVarianceDays = Math.round((forecast - planned) / (1000 * 60 * 60 * 24));\n    scheduleVarianceText = scheduleVarianceDays > 0\n      ? `+${scheduleVarianceDays} days late`\n      : scheduleVarianceDays < 0\n        ? `${Math.abs(scheduleVarianceDays)} days early`\n        : 'On Schedule';\n  }\n\n  // \u2500\u2500 RAG Scoring \u2500\u2500\n  // Each dimension scores: 0=Green, 1=Amber, 2=Red\n  let scheduleScore = 0;\n  if (scheduleVarianceDays > 30) scheduleScore = 2;\n  else if (scheduleVarianceDays > 0) scheduleScore = 1;\n\n  let budgetScore = 0;\n  if (burnPct > 90) budgetScore = 2;\n  else if (burnPct > 75) budgetScore = 1;\n\n  let issuesScore = 0;\n  if (openIssues >= 4) issuesScore = 2;\n  else if (openIssues >= 2) issuesScore = 1;\n\n  const totalScore = scheduleScore + budgetScore + issuesScore;\n\n  let ragStatus, ragEmoji, ragColor;\n  if (totalScore >= 4) {\n    ragStatus = 'RED';\n    ragEmoji = '\ud83d\udd34';\n    ragColor = '#C00000';\n  } else if (totalScore >= 2) {\n    ragStatus = 'AMBER';\n    ragEmoji = '\ud83d\udfe1';\n    ragColor = '#ED7D31';\n  } else {\n    ragStatus = 'GREEN';\n    ragEmoji = '\ud83d\udfe2';\n    ragColor = '#1A7340';\n  }\n\n  projects.push({\n    name,\n    plannedEnd,\n    forecastEnd,\n    budgetTotal,\n    budgetSpent,\n    burnPct: burnPct.toFixed(1),\n    openIssues,\n    pctComplete,\n    scheduleVarianceDays,\n    scheduleVarianceText,\n    ragStatus,\n    ragEmoji,\n    ragColor,\n    // Pass to AI prompt\n    aiPrompt: `Project: ${name}\\nRAG Status: ${ragStatus}\\nSchedule: ${scheduleVarianceText}\\nBudget Burn: ${burnPct.toFixed(1)}% (Spent $${budgetSpent.toLocaleString()} of $${budgetTotal.toLocaleString()})\\nOpen Issues: ${openIssues}\\n% Complete: ${pctComplete}%\\n\\nWrite a concise 3-line executive health summary for this project. Line 1: Overall status assessment. Line 2: Key risk or concern. Line 3: Recommended action for leadership. Be direct and specific. No bullet points.`\n  });\n}\n\n// Return one item per project for the loop\nreturn projects.map(p => ({ json: p }));"
      },
      "typeVersion": 2
    },
    {
      "id": "d59fe91d-1db0-4c00-8166-c9a27dd17065",
      "name": "Generate AI Health Summary",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "onError": "continueErrorOutput",
      "position": [
        736,
        -368
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "GPT-4O-MINI"
        },
        "options": {
          "maxTokens": 200,
          "temperature": 0.3
        },
        "messages": {
          "values": [
            {
              "role": "system",
              "content": "You are a senior project management consultant writing concise executive summaries for a weekly leadership dashboard. You are direct, specific, and action-oriented. No fluff."
            },
            {
              "content": "={{ $json.aiPrompt }}"
            }
          ]
        }
      },
      "typeVersion": 1.4
    },
    {
      "id": "41ce9673-985a-4f31-81b4-3635276ea06d",
      "name": "Attach AI Narrative to Project Data",
      "type": "n8n-nodes-base.code",
      "position": [
        1200,
        -384
      ],
      "parameters": {
        "jsCode": "// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n// Attach AI narrative back to project data\n// n8n OpenAI node returns message in content[0].text\n// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\nconst items = $input.all();\nconst results = [];\n\nfor (const item of items) {\n  const proj = item.json;\n  // Extract the text from the OpenAI response\n  const aiText = proj?.choices?.[0]?.message?.content ||\n                 proj?.message?.content ||\n                 proj?.content ||\n                 'Summary not available.';\n  results.push({\n    json: {\n      ...proj,\n      aiNarrative: aiText.trim()\n    }\n  });\n}\n\nreturn results;"
      },
      "typeVersion": 2
    },
    {
      "id": "a846c01a-c329-4e15-9a31-7caf783e57fd",
      "name": "Build HTML Dashboard Email",
      "type": "n8n-nodes-base.code",
      "position": [
        1376,
        -384
      ],
      "parameters": {
        "jsCode": "// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\n// Aggregate all project items and build\n// a single beautiful HTML email\n// \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\nconst items = $input.all();\nconst projects = items.map(i => i.json);\n\nconst today = new Date().toLocaleDateString('en-US', {\n  weekday: 'long', year: 'numeric', month: 'long', day: 'numeric'\n});\n\nconst summaryRow = (p) => {\n  const bgColor = p.ragStatus === 'RED' ? '#FFF0F0' : p.ragStatus === 'AMBER' ? '#FFFBF0' : '#F0FFF5';\n  const borderColor = p.ragStatus === 'RED' ? '#C00000' : p.ragStatus === 'AMBER' ? '#ED7D31' : '#1A7340';\n  const lines = (p.aiNarrative || '').split('\\n').filter(l => l.trim());\n  const narHTML = lines.map(l => `<p style=\"margin:4px 0;color:#333;font-size:14px;line-height:1.5\">${l}</p>`).join('');\n\n  return `\n  <tr>\n    <td style=\"padding:0 0 20px 0\">\n      <div style=\"border-left:5px solid ${borderColor};border-radius:6px;background:${bgColor};padding:20px 24px;\">\n        \n        <!-- Project header -->\n        <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\">\n          <tr>\n            <td>\n              <span style=\"font-size:18px;font-weight:700;color:#1F2937\">${p.ragEmoji} ${p.name}</span>\n            </td>\n            <td align=\"right\">\n              <span style=\"display:inline-block;padding:4px 14px;border-radius:20px;background:${borderColor};color:#fff;font-size:12px;font-weight:700;letter-spacing:1px\">${p.ragStatus}</span>\n            </td>\n          </tr>\n        </table>\n        \n        <!-- Metrics row -->\n        <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" style=\"margin-top:14px\">\n          <tr>\n            <td width=\"25%\" style=\"text-align:center;padding:10px;background:rgba(255,255,255,0.6);border-radius:6px;\">\n              <div style=\"font-size:11px;color:#666;text-transform:uppercase;letter-spacing:0.5px\">Schedule</div>\n              <div style=\"font-size:15px;font-weight:700;color:${borderColor};margin-top:4px\">${p.scheduleVarianceText}</div>\n            </td>\n            <td width=\"4%\"></td>\n            <td width=\"25%\" style=\"text-align:center;padding:10px;background:rgba(255,255,255,0.6);border-radius:6px;\">\n              <div style=\"font-size:11px;color:#666;text-transform:uppercase;letter-spacing:0.5px\">Budget Burn</div>\n              <div style=\"font-size:15px;font-weight:700;color:${borderColor};margin-top:4px\">${p.burnPct}%</div>\n            </td>\n            <td width=\"4%\"></td>\n            <td width=\"25%\" style=\"text-align:center;padding:10px;background:rgba(255,255,255,0.6);border-radius:6px;\">\n              <div style=\"font-size:11px;color:#666;text-transform:uppercase;letter-spacing:0.5px\">Open Issues</div>\n              <div style=\"font-size:15px;font-weight:700;color:${borderColor};margin-top:4px\">${p.openIssues}</div>\n            </td>\n            <td width=\"4%\"></td>\n            <td width=\"25%\" style=\"text-align:center;padding:10px;background:rgba(255,255,255,0.6);border-radius:6px;\">\n              <div style=\"font-size:11px;color:#666;text-transform:uppercase;letter-spacing:0.5px\">% Complete</div>\n              <div style=\"font-size:15px;font-weight:700;color:${borderColor};margin-top:4px\">${p.pctComplete}%</div>\n            </td>\n          </tr>\n        </table>\n        \n        <!-- AI Narrative -->\n        <div style=\"margin-top:14px;padding:14px;background:rgba(255,255,255,0.7);border-radius:6px;\">\n          <div style=\"font-size:11px;color:#888;text-transform:uppercase;letter-spacing:0.5px;margin-bottom:8px\">Executive Summary</div>\n          ${narHTML}\n        </div>\n      </div>\n    </td>\n  </tr>`;\n};\n\n// Count overall statuses\nconst redCount = projects.filter(p => p.ragStatus === 'RED').length;\nconst amberCount = projects.filter(p => p.ragStatus === 'AMBER').length;\nconst greenCount = projects.filter(p => p.ragStatus === 'GREEN').length;\n\nconst html = `\n<!DOCTYPE html>\n<html>\n<head><meta charset=\"utf-8\"><meta name=\"viewport\" content=\"width=device-width\"></head>\n<body style=\"margin:0;padding:0;background:#F3F4F6;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Arial,sans-serif\">\n\n<table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\" style=\"background:#F3F4F6;padding:30px 0\">\n<tr><td align=\"center\">\n<table width=\"640\" cellpadding=\"0\" cellspacing=\"0\" style=\"max-width:640px\">\n\n  <!-- Header -->\n  <tr>\n    <td style=\"background:linear-gradient(135deg,#1F3864 0%,#2F5496 100%);padding:30px 32px;border-radius:10px 10px 0 0\">\n      <div style=\"font-size:22px;font-weight:800;color:#fff\">\ud83d\udcca Weekly Project Health Dashboard</div>\n      <div style=\"font-size:14px;color:#BCC9E8;margin-top:6px\">${today}</div>\n    </td>\n  </tr>\n\n  <!-- Summary bar -->\n  <tr>\n    <td style=\"background:#fff;padding:16px 32px;border-bottom:1px solid #E5E7EB\">\n      <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\">\n        <tr>\n          <td style=\"font-size:13px;color:#6B7280\">Portfolio Summary \u2014 ${projects.length} Projects</td>\n          <td align=\"right\">\n            <span style=\"display:inline-block;margin-left:8px;padding:3px 12px;border-radius:20px;background:#FFF0F0;color:#C00000;font-size:12px;font-weight:700\">\ud83d\udd34 ${redCount} Red</span>\n            <span style=\"display:inline-block;margin-left:8px;padding:3px 12px;border-radius:20px;background:#FFFBF0;color:#9C6500;font-size:12px;font-weight:700\">\ud83d\udfe1 ${amberCount} Amber</span>\n            <span style=\"display:inline-block;margin-left:8px;padding:3px 12px;border-radius:20px;background:#F0FFF5;color:#1A7340;font-size:12px;font-weight:700\">\ud83d\udfe2 ${greenCount} Green</span>\n          </td>\n        </tr>\n      </table>\n    </td>\n  </tr>\n\n  <!-- Projects -->\n  <tr>\n    <td style=\"background:#fff;padding:24px 32px\">\n      <table width=\"100%\" cellpadding=\"0\" cellspacing=\"0\">\n        ${projects.map(summaryRow).join('')}\n      </table>\n    </td>\n  </tr>\n\n  <!-- Footer -->\n  <tr>\n    <td style=\"background:#F9FAFB;padding:20px 32px;border-top:1px solid #E5E7EB;border-radius:0 0 10px 10px;text-align:center\">\n      <div style=\"font-size:12px;color:#9CA3AF\">Auto-generated by n8n Project Health Dashboard \u00b7 Every Friday at 5 PM</div>\n      <div style=\"font-size:12px;color:#9CA3AF;margin-top:4px\">Data sourced from Google Sheets \u00b7 Summaries by GPT-4o Mini</div>\n    </td>\n  </tr>\n\n</table>\n</td></tr></table>\n</body></html>`;\n\nreturn [{ json: { html, subject: `\ud83d\udcca Project Health Dashboard \u2014 ${today} (\ud83d\udd34${redCount} \ud83d\udfe1${amberCount} \ud83d\udfe2${greenCount})` } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "f0d705ab-ee70-4926-9dc6-5fb04a39fb91",
      "name": "Send Dashboard Email to Leadership",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1568,
        -384
      ],
      "parameters": {
        "sendTo": "your-leadership-list@example.com",
        "message": "={{ $json.html }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.subject }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "271dd67f-1f70-4bb6-9ad8-3ec7e52547b0",
      "name": "Slack: Alert on AI Step Failure",
      "type": "n8n-nodes-base.slack",
      "position": [
        1024,
        -128
      ],
      "parameters": {
        "text": "=\u26a0\ufe0f AI summary step failed for a project in the health dashboard workflow. Please check the execution log.",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "list",
          "value": "YOUR_SLACK_CHANNEL_ID",
          "cachedResultName": "project-alerts"
        },
        "otherOptions": {},
        "authentication": "oAuth2"
      },
      "typeVersion": 2.3
    }
  ],
  "active": false,
  "settings": {
    "binaryMode": "separate",
    "availableInMCP": false,
    "executionOrder": "v1"
  },
  "versionId": "c757483c-1bc7-40b6-bfda-4a3f10fa2ad7",
  "connections": {
    "On Workflow Error": {
      "main": [
        [
          {
            "node": "Slack \u2013 Send Error Alert1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge All Project Data": {
      "main": [
        [
          {
            "node": "Calculate RAG Status per Project",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Project Beta Sheet": {
      "main": [
        [
          {
            "node": "Merge All Project Data",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Read Project Alpha Sheet": {
      "main": [
        [
          {
            "node": "Merge All Project Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Project Gamma Sheet": {
      "main": [
        [
          {
            "node": "Merge All Project Data",
            "type": "main",
            "index": 2
          }
        ]
      ]
    },
    "Build HTML Dashboard Email": {
      "main": [
        [
          {
            "node": "Send Dashboard Email to Leadership",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate AI Health Summary": {
      "main": [
        [
          {
            "node": "Attach AI Narrative to Project Data",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Slack: Alert on AI Step Failure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Trigger: Every Friday at 5 PM": {
      "main": [
        [
          {
            "node": "Read Project Alpha Sheet",
            "type": "main",
            "index": 0
          },
          {
            "node": "Read Project Beta Sheet",
            "type": "main",
            "index": 0
          },
          {
            "node": "Read Project Gamma Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Slack: Alert on AI Step Failure": {
      "main": [
        [
          {
            "node": "Read Project Gamma Sheet",
            "type": "main",
            "index": 0
          },
          {
            "node": "Read Project Beta Sheet",
            "type": "main",
            "index": 0
          },
          {
            "node": "Read Project Alpha Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate RAG Status per Project": {
      "main": [
        [
          {
            "node": "Generate AI Health Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Attach AI Narrative to Project Data": {
      "main": [
        [
          {
            "node": "Build HTML Dashboard Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• gmailOAuth2
• slackOAuth2Api

About this workflow

This workflow runs every Friday at 5 PM, reads project metrics from three Google Sheets tabs, calculates a Red/Amber/Green health status, uses OpenAI (GPT-4o mini) to generate executive summaries, and sends a color-coded HTML dashboard email via Gmail, with Slack alerts for…

Source: https://n8n.io/workflows/16376/ — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.