Summarize Newsletters with Gmail, Gemini & Sheets

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": "newsletter_automation_template",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Newsletter Automation",
  "tags": [],
  "nodes": [
    {
      "id": "d8679432-116c-482e-b600-9b2f9150f31f",
      "name": "Memory3",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        1696,
        192
      ],
      "parameters": {
        "sessionKey": "newsletter_briefing_2025",
        "sessionIdType": "customKey",
        "contextWindowLength": 10
      },
      "notesInFlow": false,
      "typeVersion": 1.3
    },
    {
      "id": "2dfc95ed-abb8-4962-adb7-0a63fa010d58",
      "name": "Send email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        2208,
        0
      ],
      "parameters": {
        "html": "={{ $json.html }}",
        "options": {},
        "subject": "={{ \"Newsletter Zusammenfassung von \" + $now.toLocaleString(\"de-DE\", { weekday: \"long\", day: \"2-digit\", month: \"long\", year: \"numeric\" }).replace(/^\\w/, c => c.toUpperCase()) }}",
        "toEmail": "user@example.com",
        "fromEmail": "user@example.com"
      },
      "typeVersion": 2.1
    },
    {
      "id": "b5a64548-1654-4908-8037-d9943d486c1a",
      "name": "Google Gemini Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        1424,
        192
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 1
    },
    {
      "id": "5e2cd214-e13b-476a-9029-2983deba5271",
      "name": "Gmail",
      "type": "n8n-nodes-base.gmail",
      "position": [
        192,
        0
      ],
      "parameters": {
        "simple": false,
        "filters": {
          "labelIds": []
        },
        "options": {},
        "operation": "getAll"
      },
      "typeVersion": 2.1
    },
    {
      "id": "6f833907-1e69-494a-9826-fe8f942946e2",
      "name": "Google Spreadsheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -32,
        0
      ],
      "parameters": {
        "title": "={{ new Date().toISOString().slice(2,10).replace(/-/g, '/') }}",
        "options": {},
        "resource": "spreadsheet",
        "sheetsUi": {
          "sheetValues": [
            {
              "title": "Inbox"
            }
          ]
        }
      },
      "executeOnce": true,
      "typeVersion": 4.7
    },
    {
      "id": "e40152a4-2b46-4794-9ade-8bcd21a145b1",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -240,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 9
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "e22ec30e-1e7f-468f-8a13-1942d192e127",
      "name": "Parse HTML-Code",
      "type": "n8n-nodes-base.code",
      "position": [
        416,
        0
      ],
      "parameters": {
        "mode": "runOnceForEachItem",
        "jsCode": "// Function Item -- nimmt $json.html, gibt cleanText + cleanLength zur\u00fcck\nconst KEEP_LINKS_BASE = true;\n\nlet s = $json.html || '';\nif (!s.trim()) return $json;\n\n// 0) RTF-Artefakte: Backslashes entfernen\ns = s.replace(/\\/g, '');\n\n// 1) Vorarbeit: Zeilenumbr\u00fcche f\u00fcr Bl\u00f6cke\ns = s\n  .replace(/</(p|div|h[1-6]|li|section|article|header|footer)>/gi, '\n\n')\n  .replace(/<(br|hr)\\s*/?>/gi, '\n');\n\n// 2) Head/Design/Kommentare entfernen\ns = s\n  .replace(/<script[\\s\\S]*?</script>/gi, '')\n  .replace(/<style[\\s\\S]*?</style>/gi, '')\n  .replace(/<head[\\s\\S]*?</head>/gi, '')\n  .replace(/<!--[\\s\\S]*?-->/g, '')\n  .replace(/<meta[^>]*>/gi, '')\n  .replace(/<!doctype[^>]*>/gi, '');\n\n// 3) \u00dcbrige HTML-Tags strippen\ns = s.replace(/<[^>]+>/g, '');\n\n// 4) HTML-Entities & Zero-Width-Zeichen normalisieren\ns = s\n  .replace(/&nbsp;/gi, ' ')\n  .replace(/&amp;/gi, '&')\n  .replace(/&quot;/gi, '\"')\n  .replace(/&apos;/gi, \"'\")\n  .replace(/&lt;/gi, '<')\n  .replace(/&gt;/gi, '>')\n  .replace(/[\u200b-\u200d\u2060\ufeff\u00ad]/g, '')\n  .replace(/[\u202f\u2009\u200a]/g, ' ')\n  .replace(/\u00a0/g, ' ');\n\n// 5) Links stark reduzieren\ns = s.replace(/https?://[^\\s)]+/g, (url) => {\n  const low = url.toLowerCase();\n  // Komplett entfernen\n  if (/(unsubscribe|abmelden|privacy|datenschutz|linkedin|facebook|twitter|instagram|update.*preferences|newsletter.*verwalten|impressum|kontakt|anmelden|registr|login|account)/i.test(low)) return '';\n  // Kurz halten f\u00fcr relevante Links\n  const base = url.split('?')[0];\n  if (base.length > 60) return base.substring(0, 60) + '...';\n  return KEEP_LINKS_BASE ? base : '';\n});\n\n// 6) Email-Adressen entfernen\ns = s.replace(/[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}/g, '[E-Mail]');\n\n// 7) SEHR AGGRESSIVE Boilerplate-Filter\nconst boilerplate = [\n  'Im Browser ansehen', 'Zum Briefing', 'Briefing im Browser \u00f6ffnen', 'Browser lesen',\n  'Zur\u00fcck nach oben', '^Anzeige$', '^Presseschau$', '^Nachrichten$',\n  '^Die wichtigsten Themen im \u00dcberblick:?$', '^Folgen Sie .* auf LinkedIn$',\n  'Hilfe & Support', '^Zum\\\\s+Tagesspiegel Background$', 'Hier geht.*zur Anmeldung',\n  'Gesundheit & E-Health', 'Smart\\\\s*City', 'Energie\\\\s*&\\\\s*Klima',\n  'Digitalisierung\\\\s*&\\\\s*KI', 'Sustainable\\\\s*Finance', 'Verkehr\\\\s*&\\\\s*Smart\\\\s*Mobility',\n  'Agrar\\\\s*&\\\\s*Ern\u00e4hrung', 'Cybersecurity', '^Tipp:$',\n  '^Mehr\\\\s+auf\\\\s+pkv\\\\.de$', '^Mit\\\\s+Caspar\\\\s+Schwietering$',\n  '^dpa$', '^ibi$', '^AFP$', '^Reuters$', '^KNA$', '^csa$',\n  'Fragen zu redaktionellen Inhalten:', 'Fragen zu Anzeigen:', 'Fragen zum Abonnement',\n  '^Sie m\u00f6chten.*kompakterer Form\\\\?$', 'Hier k\u00f6nnen Sie.*aktivieren',\n  'Diese.*Newsletter.*an', 'Wurde Ihnen.*weitergeleitet', 'Jetzt.*weiterlesen',\n  'M\u00f6chten Sie \u00e4ndern.*E-Mails', 'Newsletter verwalten', 'von dieser Liste',\n  'F\u00fcgen Sie.*Adressbuch', 'Sichern Sie sich', 'Lizenz kaufen',\n  'Individuelles Angebot', '^Kontakt$', '^Datenschutz$', '^Impressum$',\n  '^Mediadaten$', 'Klicken Sie hier', 'update your preferences',\n  'This email was sent', 'ABONNIEREN Sie', 'Jetzt Artikel lesen',\n  'Jetzt mit H\\\\+ weiterlesen', '^Jobs$', '^Weitere Jobs finden$',\n  'Handelsblatt-Probeabo', 'Zur Angebotsauswahl', '^Podcast:',\n  'H\u00f6ren statt lesen', 'Das Handelsblatt als E-Paper', '^Karikatur$',\n  'Wie zufrieden sind Sie', 'Sie haben Fragen.*Kritik', '^Bonus$',\n  'Morning Briefing als Podcast', '^Latest News$', 'Was heute noch wichtig ist',\n  '^Mit H\\\\+ lesen Sie auch$', '^Geldanlage plus$', '^Kopf des Tages',\n  'Sie lesen die kostenlose Vorschau', '^Monitoring$', '^Briefing$',\n  'Eine umfangreiche Presseschau', 'Jeden Morgen um 6 Uhr',\n  'Der Tagesspiegel Background.*erscheint', 'Alle Artikel.*finden Sie',\n  'Mehr \u00fcber das Monitoring', 'Monat kostenlos testen',\n  'Untersuchen und beobachten Sie', 'Erhalten Sie den vollen Zugang'\n];\n\nconst boilerRe = new RegExp(`^(${boilerplate.join('|')})\\\\s*$`, 'i');\n\n// 8) KOMPLETT Footer abschneiden (sehr aggressiv)\nconst footerMarkers = [\n  'Verlag Der Tagesspiegel GmbH', 'Gesch\u00e4ftsf\u00fchrer:', 'Chefredakteur',\n  'AG Charlottenburg', 'Diese kostenlosen Newsletter', 'ABONNIEREN Sie die Newsletter',\n  'Brussels Playbook', 'London Playbook', 'DC Decoded', 'Global Playbook',\n  'Fragen zum Datenschutz', 'user@example.com',\n  'Copyright', '\u00a9 20', 'Alle Rechte vorbehalten'\n];\n\nfor (const marker of footerMarkers) {\n  const idx = s.indexOf(marker);\n  if (idx > s.length * 0.5) { // nur wenn im letzten Drittel\n    s = s.substring(0, idx).trim();\n    break;\n  }\n}\n\n// 9) Zeilen filtern\nconst lines = s.split('\n');\nconst filteredLines = [];\nlet lastLine = '';\nlet inPresseschau = false;\nlet presseschauCount = 0;\nlet anzeigenSkipLines = 0;\n\nfor (let i = 0; i < lines.length; i++) {\n  const line = lines[i].trim();\n  \n  // Komplett leere Zeilen\n  if (!line) {\n    if (lastLine !== '') filteredLines.push('');\n    lastLine = '';\n    continue;\n  }\n  \n  // Boilerplate \u00fcberspringen\n  if (boilerRe.test(line)) {\n    lastLine = '';\n    continue;\n  }\n  \n  // Anzeigen-Block: Max 6 Zeilen \u00fcberspringen, aber fr\u00fcher stoppen bei echtem Content\n  if (/^Anzeige$/i.test(line) || /^\\(Anzeige\\)/i.test(line)) {\n    anzeigenSkipLines = 6;\n    continue;\n  }\n  \n  if (anzeigenSkipLines > 0) {\n    anzeigenSkipLines--;\n    // Fr\u00fcher stoppen, wenn echte \u00dcberschrift oder l\u00e4ngerer Content kommt\n    if (line.length > 40 && /^[A-Z]/.test(line) && !/(Mehr|Jetzt|www\\.|https?:)/i.test(line)) {\n      anzeigenSkipLines = 0; // Abbrechen, ist echter Content\n    } else {\n      continue; // Weiter \u00fcberspringen\n    }\n  }\n  \n  // Presseschau MASSIV reduzieren\n  if (/^Presseschau$/i.test(line)) {\n    filteredLines.push('\n=== Presseschau (stark gek\u00fcrzt) ===');\n    inPresseschau = true;\n    presseschauCount = 0;\n    lastLine = line;\n    continue;\n  }\n  \n  if (inPresseschau) {\n    presseschauCount++;\n    // Nur erste 3 Eintr\u00e4ge behalten\n    if (presseschauCount > 6) {\n      if (/^[A-Z][\\w\\s&-]{15,}$/.test(line) && line !== line.toUpperCase()) {\n        inPresseschau = false;\n      } else {\n        continue;\n      }\n    }\n  }\n  \n  // Sehr kurze Link-Zeilen entfernen\n  if (line.length < 20 && /^https?:|^www\\.|\\.de$|\\.com$/i.test(line)) continue;\n  \n  // \"Mehr lesen\" etc.\n  if (/^(Mehr lesen|Weiterlesen|Jetzt lesen|Hier lesen|Zum Artikel)$/i.test(line)) continue;\n  \n  // Wiederholte Zeilen vermeiden\n  if (line === lastLine) continue;\n  \n  // Zeilen mit nur Sonderzeichen\n  if (/^[\\s\\-\u2014\u2014=_*#]+$/.test(line)) continue;\n  \n  // Sehr kurze Zeilen (au\u00dfer \u00dcberschriften)\n  if (line.length < 3 && !/^[IVX0-9]+$/i.test(line)) continue;\n  \n  filteredLines.push(line);\n  lastLine = line;\n}\n\ns = filteredLines.join('\n');\n\n// 10) Mehrfache Leerzeilen komprimieren\ns = s\n  .replace(/[ \t]+\n/g, '\n')\n  .replace(/\n{4,}/g, '\n\n\n')\n  .replace(/[ \t]{2,}/g, ' ')\n  .trim();\n\n// 11) Excel-Formel-Schutz\nif (/^[+=]/.test(s)) s = \"'\" + s;\n\n// 12) L\u00e4ngencheck f\u00fcr Debugging\nconsole.log(`L\u00e4nge nach Bereinigung: ${s.length} Zeichen`);\n\nreturn { ...$json, cleanText: s, cleanLength: s.length };"
      },
      "typeVersion": 2
    },
    {
      "id": "63fa5ffb-dad9-486e-aadc-fc89569b0145",
      "name": "Split in Chunks",
      "type": "n8n-nodes-base.code",
      "position": [
        640,
        0
      ],
      "parameters": {
        "jsCode": "// Function: Text in mehrere Zeilen aufteilen wenn > 50.000 Zeichen\nconst MAX_LENGTH = 50000;\nconst items = [];\n\nfor (const item of $input.all()) {\n  const text = item.json.cleanText || item.json.Text || '';\n  const absender = item.json.Absenderadresse || '';\n  const absenderName = item.json.Absendername || '';\n  const betreff = item.json.Betreff || '';\n  \n  // Wenn Text kurz genug ist: normal durchreichen\n  if (text.length <= MAX_LENGTH) {\n    items.push({\n      json: {\n        Absenderadresse: absender,\n        Absendername: absenderName,\n        Betreff: betreff,\n        Text: text,\n        Teil: '1/1',\n        Zeichenanzahl: text.length\n      }\n    });\n    continue;\n  }\n  \n  // Text ist zu lang: in Chunks aufteilen\n  const chunks = [];\n  let remaining = text;\n  \n  while (remaining.length > 0) {\n    let chunk = remaining.substring(0, MAX_LENGTH);\n    \n    // Versuche bei Absatz-Ende zu schneiden (nicht mitten im Wort)\n    if (remaining.length > MAX_LENGTH) {\n      const lastNewline = chunk.lastIndexOf('\n\n');\n      const lastPeriod = chunk.lastIndexOf('. ');\n      \n      // Schneide bei Absatz oder Satzende\n      const cutPoint = lastNewline > MAX_LENGTH - 500 ? lastNewline : \n                       lastPeriod > MAX_LENGTH - 200 ? lastPeriod + 1 : \n                       MAX_LENGTH;\n      \n      chunk = remaining.substring(0, cutPoint);\n    }\n    \n    chunks.push(chunk.trim());\n    remaining = remaining.substring(chunk.length).trim();\n  }\n  \n  // F\u00fcr jeden Chunk ein Item erstellen\n  chunks.forEach((chunk, index) => {\n    items.push({\n      json: {\n        Absenderadresse: absender,\n        Absendername: absenderName,\n        Betreff: `${betreff} (Teil ${index + 1}/${chunks.length})`,\n        Text: chunk,\n        Teil: `${index + 1}/${chunks.length}`,\n        Zeichenanzahl: chunk.length\n      }\n    });\n  });\n}\n\nreturn items;"
      },
      "typeVersion": 2
    },
    {
      "id": "086ea1bd-8075-4856-be16-0f9d83e9c7bc",
      "name": "Set Table Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        864,
        0
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "e1bb9a57-e7d5-479b-af92-22ab5a93dcd5",
              "name": "Absenderadresse",
              "type": "string",
              "value": "={{ $json.Absenderadresse }}"
            },
            {
              "id": "06048f24-3da8-4978-bf6d-54bbfe5b3ced",
              "name": "Absendername",
              "type": "string",
              "value": "={{ $json.Absendername }}"
            },
            {
              "id": "3077d498-ee8a-41f1-a4c9-57018ca01ec6",
              "name": "Betreff",
              "type": "string",
              "value": "={{ $json.Betreff }}"
            },
            {
              "id": "6beb9b2a-a672-4a6b-941f-de7cb1041869",
              "name": "Text",
              "type": "string",
              "value": "={{ $json.Text }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "d1676830-038e-418a-950a-8539ca9f1791",
      "name": "Append Row",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1088,
        0
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [
            {
              "id": "Absenderadresse",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Absenderadresse",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Absendername",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Absendername",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Betreff",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Betreff",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Text",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Text",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "={{ $('Google Spreadsheet').first().json.sheets[0].properties.title }}"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $('Google Spreadsheet').first().json.spreadsheetId }}"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "a9ebbba4-06a6-4137-8fce-8c94c31b8597",
      "name": "Public Affairs Consultant",
      "type": "@n8n/n8n-nodes-langchain.code",
      "position": [
        1440,
        0
      ],
      "parameters": {
        "code": {
          "execute": {
            "code": "const llm = await this.getInputConnectionData('ai_languageModel', 0)\nconst { PromptTemplate } = require('@langchain/core/prompts')\nconst { RunnableSequence } = require('@langchain/core/runnables')\nconst { JsonOutputParser } = require('@langchain/core/output_parsers')\n\nconst jsonParser = new JsonOutputParser()\n\n// YOUR_AWS_SECRET_KEY_HERE\n// Alle Newsletter einsammeln\n// YOUR_AWS_SECRET_KEY_HERE\nconst allNewsletters = [];\nlet totalText = '# NEWSLETTER BRIEFING\n\n';\n\nfor (const item of $input.all()) {\n  const betreff = item.json.Betreff || 'Ohne Betreff';\n  const absender = item.json.Absendername || item.json.Absenderadresse || 'Unbekannt';\n  const text = item.json.Text || '';\n  allNewsletters.push({ absender, betreff, text });\n\n  totalText += `## Newsletter ${allNewsletters.length}\n`;\n  totalText += `**Von:** ${absender}\n`;\n  totalText += `**Betreff:** ${betreff}\n\n`;\n  totalText += `${text}\n\n`;\n  totalText += '---\n\n';\n}\n\nconst newsletterCount = allNewsletters.length;\nconst today = new Date().toLocaleDateString('de-DE');\n\n// YOUR_AWS_SECRET_KEY_HERE\n// LANGFASSUNG (Deep Dive) - AUF DEUTSCH\n// YOUR_AWS_SECRET_KEY_HERE\nconst longPrompt = PromptTemplate.fromTemplate(`\n<role>\nDu bist ein erfahrener Policy-Analyst und strategischer Berater f\u00fcr Public Affairs Professionals.\n</role>\n\n<goal>\nAnalysiere ALLE {count} Newsletter von heute ({date}) und erstelle ein umfassendes Briefing (1500-2500 W\u00f6rter auf DEUTSCH). \nSynthetisiere Informationen aus allen Quellen, identifiziere Muster und gib strategische Empfehlungen.\n</goal>\n\n<language>\nWICHTIG: Antworte ausschlie\u00dflich auf DEUTSCH. Alle Texte, Analysen und Empfehlungen m\u00fcssen in deutscher Sprache verfasst sein.\n</language>\n\n<context>\nDer Leser ben\u00f6tigt:\n- Umfassende Analyse \u00fcber mehrere Quellen hinweg\n- Stakeholder-Positionen und deren Implikationen\n- Strategische Risiken und Chancen\n- Wie sich verschiedene Quellen erg\u00e4nzen oder widersprechen\n- Handlungsempfehlungen basierend auf dem Gesamtbild\n</context>\n\n<style>\n- Professionell, analytisch\n- Strukturiert mit klaren Abschnitten\n- Synthese \u00fcber alle Quellen hinweg\n- Hervorhebung von \u00dcbereinstimmungen/Widerspr\u00fcchen\n- Keine Werbung oder Boilerplate-Texte\n- Alle Inhalte auf DEUTSCH\n</style>\n\n<newsletters>\n{input}\n</newsletters>\n\n<output_format>\nGib NUR valides JSON zur\u00fcck (alle Textinhalte auf DEUTSCH). WICHTIG: Verwende doppelte geschweifte Klammern f\u00fcr das JSON-Format:\n\n{{\n  \"executiveSummary\": \"3-4 S\u00e4tze \u00dcberblick auf DEUTSCH\",\n  \"sourcesAnalyzed\": {count},\n  \"majorThemes\": [\n    {{\n      \"theme\": \"Themenname auf Deutsch\",\n      \"description\": \"Beschreibung auf Deutsch\",\n      \"mentionedIn\": [\"Quelle 1\", \"Quelle 2\"],\n      \"strategicImportance\": \"Strategische Bedeutung auf Deutsch\"\n    }}\n  ],\n  \"keyDevelopments\": [\n    {{\n      \"topic\": \"Thema auf Deutsch\",\n      \"details\": \"Details auf Deutsch\",\n      \"sources\": [\"Quelle 1\"],\n      \"implications\": \"Implikationen auf Deutsch\"\n    }}\n  ],\n  \"stakeholderAnalysis\": \"Analyse der wichtigsten Akteure \u00fcber alle Quellen hinweg AUF DEUTSCH\",\n  \"crossCuttingInsights\": \"\u00dcbergreifende Erkenntnisse aus allen Quellen AUF DEUTSCH\",\n  \"strategicImplications\": \"Bedeutung f\u00fcr Corporate Affairs Strategie AUF DEUTSCH\",\n  \"recommendedActions\": [\"Handlungsempfehlung 1 auf Deutsch\", \"Handlungsempfehlung 2 auf Deutsch\"],\n  \"gapsAndQuestions\": [\"Offene Frage 1 auf Deutsch\", \"Offene Frage 2 auf Deutsch\"]\n}}\n</output_format>\n\n<reminder>\nWICHTIG: Die gesamte Analyse, alle Beschreibungen, Empfehlungen und Erkenntnisse m\u00fcssen auf DEUTSCH verfasst sein!\n</reminder>\n`);\n\n// Chain nur f\u00fcr Langfassung\nconst longChain = RunnableSequence.from([longPrompt, llm, jsonParser]);\n\ntry {\n  const longResponse = await longChain.invoke({ \n    input: totalText,\n    count: newsletterCount,\n    date: today\n  });\n\n  const timestamp = new Date().toISOString().split('T')[0];\n  const filename = `PA-Briefing_${timestamp}_${newsletterCount}NL_REPORT.json`;\n\n  // EIN Output, EIN Item\n  return [\n    {\n      json: {\n        type: \"deep_dive_report\",\n        date: today,\n        newslettersAnalyzed: newsletterCount,\n        executiveSummary: longResponse.executiveSummary || '',\n        majorThemes: longResponse.majorThemes || [],\n        keyDevelopments: longResponse.keyDevelopments || [],\n        stakeholderAnalysis: longResponse.stakeholderAnalysis || '',\n        crossCuttingInsights: longResponse.crossCuttingInsights || '',\n        strategicImplications: longResponse.strategicImplications || '',\n        recommendedActions: longResponse.recommendedActions || [],\n        gapsAndQuestions: longResponse.gapsAndQuestions || [],\n        filename: filename,\n        createdAt: new Date().toISOString()\n      }\n    }\n  ];\n\n} catch (error) {\n  return [\n    {\n      json: {\n        error: true,\n        message: error.message,\n        newslettersReceived: newsletterCount\n      }\n    }\n  ];\n}"
          }
        },
        "inputs": {
          "input": [
            {
              "type": "main"
            },
            {
              "type": "ai_languageModel",
              "maxConnections": 1
            },
            {
              "type": "ai_memory",
              "maxConnections": 1
            }
          ]
        },
        "outputs": {
          "output": [
            {
              "type": "main"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "16ecc421-cb56-4e37-b544-cb061b17ed31",
      "name": "Structure HTML-Mail",
      "type": "n8n-nodes-base.code",
      "position": [
        1952,
        0
      ],
      "parameters": {
        "jsCode": "// Funktion um Markdown in HTML zu konvertieren\nfunction markdownToHtml(text) {\n  if (!text) return 'N/A';\n  \n  return text\n    // ** bold ** zu <strong>\n    .replace(/\\*\\*([^*]+)\\*\\*/g, '<strong>$1</strong>')\n  \n    // * Listen-Items zu <li>\n    .replace(/^\\s*\\*\\s+(.+)$/gm, '<li>$1</li>')\n    \n    // Doppelte Zeilenumbr\u00fcche = neue Abs\u00e4tze\n    .replace(/\n\n/g, '</p><p>')\n    \n    // Einzelne Zeilenumbr\u00fcche = <br>\n    .replace(/\n/g, '<br>')\n    \n    // Wrap Listen in <ul>\n    .replace(/(<li>.*?</li>)/gs, '<ul>$1</ul>')\n    \n    // Wrap Text in Paragraphen (nur wenn noch nicht wrapped)\n    .replace(/^(?!<[pu])/gm, '<p>')\n    .replace(/(?<!>)$/gm, '</p>');\n}\n\nconst json = $input.item.json;\n\n// HTML Template mit Markdown-Konvertierung\nconst html = `<!DOCTYPE html>\n<html>\n<head>\n  <style>\n    body { \n      font-family: Arial, sans-serif; \n      line-height: 1.6; \n      color: #333; \n      max-width: 800px; \n      margin: 0 auto; \n      padding: 20px; \n      background: #f5f5f5;\n    }\n    h1 { \n      color: #2c3e50; \n      border-bottom: 3px solid #3498db; \n      padding-bottom: 10px; \n    }\n    h2 { \n      color: #34495e; \n      margin-top: 30px; \n      border-left: 4px solid #3498db; \n      padding-left: 15px;\n      background: #ecf0f1;\n      padding: 10px 10px 10px 15px;\n      border-radius: 3px;\n    }\n    .info-box { \n      background: white;\n      padding: 15px; \n      border-radius: 5px; \n      margin: 20px 0;\n      box-shadow: 0 2px 4px rgba(0,0,0,0.1);\n    }\n    .content-box {\n      background: white;\n      padding: 20px;\n      margin: 15px 0;\n      border-radius: 5px;\n      box-shadow: 0 2px 4px rgba(0,0,0,0.1);\n    }\n    .content-box p {\n      margin: 10px 0;\n    }\n    .content-box ul {\n      margin: 10px 0;\n      padding-left: 20px;\n    }\n    .content-box li {\n      margin: 8px 0;\n      line-height: 1.5;\n    }\n    .content-box strong {\n      color: #2c3e50;\n    }\n    .theme { \n      background: white; \n      border-left: 4px solid #27ae60; \n      padding: 15px; \n      margin: 15px 0;\n      border-radius: 3px;\n      box-shadow: 0 2px 4px rgba(0,0,0,0.1);\n    }\n    .action { \n      background: #e8f5e9; \n      padding: 12px; \n      margin: 10px 0; \n      border-radius: 5px;\n      border-left: 3px solid #4caf50;\n    }\n    .question { \n      background: #fff3cd; \n      padding: 12px; \n      margin: 10px 0; \n      border-radius: 5px;\n      border-left: 3px solid #ffc107;\n    }\n    .meta { \n      color: #7f8c8d; \n      font-size: 0.9em; \n      margin-top: 30px; \n      border-top: 2px solid #bdc3c7; \n      padding-top: 15px;\n      text-align: center;\n    }\n  </style>\n</head>\n<body>\n  <h1>Newsletter Briefing</h1>\n  \n  <div class=\"info-box\">\n    <strong>\ud83d\udcca Analysierte Newsletter:</strong> ${json.newslettersAnalyzed}<br>\n    <strong>\ud83d\udcc5 Datum:</strong> ${json.date}\n  </div>\n\n  <h2>Executive Summary</h2>\n  <div class=\"content-box\">${markdownToHtml(json.executiveSummary)}</div>\n\n  <h2>Hauptthemen</h2>\n  ${json.majorThemes ? json.majorThemes.map(t => `\n    <div class=\"theme\">\n      <h3>${t.theme}</h3>\n      ${markdownToHtml(t.description)}\n      <p><strong>Erw\u00e4hnt in:</strong> ${t.mentionedIn ? t.mentionedIn.join(', ') : 'N/A'}</p>\n      <p><strong>Strategische Bedeutung:</strong> ${markdownToHtml(t.strategicImportance)}</p>\n    </div>\n  `).join('') : '<p>Keine Themen verf\u00fcgbar</p>'}\n\n  <h2>Stakeholder-Analyse</h2>\n  <div class=\"content-box\">${markdownToHtml(json.stakeholderAnalysis)}</div>\n\n  <h2>\u00dcbergreifende Erkenntnisse</h2>\n  <div class=\"content-box\">${markdownToHtml(json.crossCuttingInsights)}</div>\n\n  <h2>Strategische Implikationen</h2>\n  <div class=\"content-box\">${markdownToHtml(json.strategicImplications)}</div>\n\n  <h2>Empfohlene Aktionen</h2>\n  ${json.recommendedActions ? json.recommendedActions.map((a, i) => `\n    <div class=\"action\"><strong>${i+1}.</strong> ${a}</div>\n  `).join('') : '<p>Keine Aktionen</p>'}\n\n  <h2>Offene Fragen</h2>\n  ${json.gapsAndQuestions ? json.gapsAndQuestions.map((q, i) => `\n    <div class=\"question\"><strong>${i+1}.</strong> ${q}</div>\n  `).join('') : '<p>Keine offenen Fragen</p>'}\n\n  <div class=\"meta\">\n    Automatisch generiert durch KI-Analyse | ${json.newslettersAnalyzed} Quellen\n  </div>\n</body>\n</html>`;\n\nreturn [{ json: { ...json, html } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "82ec1fd7-354c-4115-b27e-0efe0c108b81",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -880,
        -176
      ],
      "parameters": {
        "width": 400,
        "height": 320,
        "content": "## Overview\n\n**Step 1:** Collect: Fetch ~10 newsletters from Gmail and strip HTML noise.\n**Step 2:** Organize: Chunk long texts (token-safe), log chunks + metadata to Google Sheets.\n**Step 3:** Summarize & Send: An LLM creates a concise daily digest (key topics, implications, actions), builds an HTML email, and sends it to stakeholders.\n\n**Note:** This template is not limited to Public Affairs. Swap the prompt and filters to use it for Legal, ESG, Finance, Marketing, Competitive Intel, etc."
      },
      "typeVersion": 1
    },
    {
      "id": "68c78878-0678-4500-85cf-40e842090ff9",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -304,
        -240
      ],
      "parameters": {
        "width": 432,
        "height": 464,
        "content": "## Group A \u2013 Timing & Destination\n\n**When & where**\n\u2022 Runs daily at the set time.\n\u2022 Creates/updates a Google Sheet to store raw text + metadata.\n\nCustomize: Interval/time; sheet name/tab."
      },
      "typeVersion": 1
    },
    {
      "id": "aa154741-1d13-4eba-ab88-0df4a5bc8ade",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        160,
        -304
      ],
      "parameters": {
        "width": 1088,
        "height": 528,
        "content": "## Inbox, Pre-Clean & Chunking \n\n**Collect & sanitize**\n\u2022 Pulls the 10 target newsletters via Gmail query.\n\u2022 Removes HTML clutter, trackers, inline styles \u2192 clean plain text.\n\n**Customize:** Gmail query (e.g., label:PA-News newer_than:1d), sender/subject filters.\n\n**Token-safe structuring**\n\u2022 Splits long bodies into manageable chunks.\n\u2022 Maps fields (date, source, subject, chunk id, text).\n\n**Customize:** Chunk size, field names, destination sheet/tab."
      },
      "typeVersion": 1
    },
    {
      "id": "4a01fa46-b13f-4fe8-be15-2220af449f28",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1312,
        -304
      ],
      "parameters": {
        "width": 528,
        "height": 688,
        "content": "## LLM Policy Digest\n\n**Create the daily brief**\n\u2022 Merges chunks into one digest with: Top topics, EU/DE relevance, risks/opportunities, action items.\n\u2022 Memory saves content when you want to analyze the newsletters over a longer period of time.\n\nCustomize: System prompt (tone, depth, output format), temperature/tokens, memory scope.\n\nReuse beyond PA: Change the prompt to Legal, ESG, Finance, Competitive Intel, Marketing, etc."
      },
      "typeVersion": 1
    },
    {
      "id": "739d7e59-6b61-4436-a46f-2f88d2530a91",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1888,
        -208
      ],
      "parameters": {
        "width": 496,
        "height": 416,
        "content": "Build & Send Email \n\n**Deliver the digest**\n\u2022 Generates a clean HTML email (headline, TL;DR, bullets, sources).\n\u2022 Sends to the PA Director/team (add CC/BCC as needed).\nCustomize: Recipients, subject , branding.)"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Gmail": {
      "main": [
        [
          {
            "node": "Parse HTML-Code",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Memory3": {
      "ai_memory": [
        [
          {
            "node": "Public Affairs Consultant",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Append Row": {
      "main": [
        [
          {
            "node": "Public Affairs Consultant",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse HTML-Code": {
      "main": [
        [
          {
            "node": "Split in Chunks",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split in Chunks": {
      "main": [
        [
          {
            "node": "Set Table Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Google Spreadsheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Table Fields": {
      "main": [
        [
          {
            "node": "Append Row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Spreadsheet": {
      "main": [
        [
          {
            "node": "Gmail",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Structure HTML-Mail": {
      "main": [
        [
          {
            "node": "Send email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "Public Affairs Consultant",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Public Affairs Consultant": {
      "main": [
        [
          {
            "node": "Structure HTML-Mail",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

Your inbox is overflowing with daily newsletters: Public Affairs, ESG, Legal, Finance, you name it. You want to stay informed, but reading 10 emails every morning? Impossible.

Source: https://n8n.io/workflows/9633/ — 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.