Send a Weekly Digest of Open Quotes with Gmail and Google 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 →

{
  "name": "Send yourself a weekly digest of unanswered quotes with Gmail and Google Sheets",
  "nodes": [
    {
      "id": "f6a0s001-0000-4000-8000-000000000001",
      "name": "Sticky Note \u2014 read me first",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -860,
        -80
      ],
      "parameters": {
        "width": 700,
        "height": 860,
        "content": "## The quotes you forgot to chase \u2014 in your inbox every Monday\n\n### How it works\nEvery Monday morning this workflow reads your quotes sheet, finds every quote still marked `open`, and works out how long each one has been sitting without an answer. It sorts them by money at risk and emails you one short digest: what is going cold, what needs a nudge this week, and what is still fresh. **Nothing is ever sent to your customers** \u2014 this is a report for you.\n\nIt also writes one row per week into a small log sheet, so after a month you can see whether your pipeline is drying up or piling up. That same log is what stops a second digest going out if you re-run the workflow by hand.\n\n### Setup\n1. Connect **Gmail** and **Google Sheets** (one-click Google sign-in on n8n Cloud).\n2. Quotes sheet headers: `quote_id, customer_name, quote_amount, sent_date, status` \u2014 set `status` to `open`, `won` or `lost`, and write `sent_date` as YYYY-MM-DD.\n3. Log sheet headers: `week_start, open_count, value_at_risk, needs_nudge, going_cold, sent_at`. Leave the rows empty; the workflow fills them.\n4. Select your sheets in all three Google Sheets nodes, and put your own email address and the two day thresholds in **\u2699\ufe0f Your settings**. Heads-up: picking a sheet in **Log this week's snapshot** silently resets that node's column settings - re-open it and restore Map Automatically, match on week_start, Cell Format = Let n8n format.\n5. Open **Workflow settings \u2192 Timezone** and pick your own. Both the Monday 8am send and the day counts follow it. Then activate."
      },
      "typeVersion": 1
    },
    {
      "id": "f6a0s002-0000-4000-8000-000000000002",
      "name": "Sticky Note \u2014 section 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -100,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 1060,
        "height": 860,
        "content": "## 1. Read what you already track\nRuns every Monday at 8am. Pulls your quotes sheet and the small digest log. Both reads always output data, so the very first run still works while the log sheet is still empty."
      },
      "typeVersion": 1
    },
    {
      "id": "f6a0s003-0000-4000-8000-000000000003",
      "name": "Sticky Note \u2014 section 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1000,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 560,
        "height": 860,
        "content": "## 2. Work out what is stalling\nKeeps the quotes marked `open`, counts days since `sent_date` in your workflow timezone, sorts by money at risk, and buckets them into going cold, needs a nudge, still fresh. Stops here if this week's digest already went out."
      },
      "typeVersion": 1
    },
    {
      "id": "f6a0s004-0000-4000-8000-000000000004",
      "name": "Sticky Note \u2014 section 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1600,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 860,
        "height": 860,
        "content": "## 3. Mail it, then log it\nSends one HTML digest to your own inbox \u2014 never to a customer \u2014 then writes one row for the week into the log sheet. Re-run it the same week and it stops before sending: that week is already logged."
      },
      "typeVersion": 1
    },
    {
      "id": "f6a00001-0000-4000-8000-00000000000a",
      "name": "Every Monday at 8am",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        340
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtDay": [
                1
              ],
              "triggerAtHour": 8,
              "weeksInterval": 1,
              "triggerAtMinute": 0
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "f6a00002-0000-4000-8000-00000000000b",
      "name": "\u2699\ufe0f Your settings",
      "type": "n8n-nodes-base.set",
      "position": [
        240,
        340
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "s1",
              "name": "yourEmail",
              "type": "string",
              "value": "you@example.com"
            },
            {
              "id": "s3",
              "name": "currencySymbol",
              "type": "string",
              "value": "$"
            },
            {
              "id": "s4",
              "name": "nudgeDays",
              "type": "number",
              "value": 7
            },
            {
              "id": "s5",
              "name": "coldDays",
              "type": "number",
              "value": 21
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "f6a00003-0000-4000-8000-00000000000c",
      "name": "Read all quotes",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        480,
        340
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.5,
      "alwaysOutputData": true,
      "waitBetweenTries": 5000
    },
    {
      "id": "f6a00004-0000-4000-8000-00000000000d",
      "name": "Read the digest log",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        720,
        340
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 4.5,
      "alwaysOutputData": true,
      "waitBetweenTries": 5000
    },
    {
      "id": "f6a00005-0000-4000-8000-00000000000e",
      "name": "Build the weekly digest",
      "type": "n8n-nodes-base.code",
      "position": [
        1080,
        340
      ],
      "parameters": {
        "jsCode": "// Build ONE weekly digest item from the quote ledger. Nothing here is sent to a customer.\nconst cfg = $('\u2699\ufe0f Your settings').first().json;\n\nconst pad = (n) => String(n).padStart(2, '0');\nconst ymd = (d) => d.getFullYear() + '-' + pad(d.getMonth() + 1) + '-' + pad(d.getDate());\n\n// Parse YYYY-MM-DD as LOCAL midnight. new Date('YYYY-MM-DD') is UTC midnight, which lands on\n// the previous calendar day in UTC-minus timezones (US/Canada) and shifts every day count by one.\n// Out-of-range parts are rejected instead of rolling over: '2026-13-05' is a typo, not next January.\nconst parseLocalDate = (raw) => {\n  const s = String(raw || '').trim();\n  const m = s.match(/^(\\d{4})-(\\d{1,2})-(\\d{1,2})$/);\n  if (!m) return new Date(s);\n  const y = +m[1], mo = +m[2], da = +m[3];\n  const d = new Date(y, mo - 1, da);\n  if (d.getFullYear() !== y || d.getMonth() !== mo - 1 || d.getDate() !== da) return new Date(NaN);\n  return d;\n};\n\n// 'today' comes from $now so day counts follow the workflow timezone (Settings -> Timezone).\nconst nowTz = $now;\nconst today = new Date(nowTz.year, nowTz.month - 1, nowTz.day);\n\n// Monday of the current week, local time. This key is what makes the digest go out once a week.\nconst weekStart = new Date(today);\nweekStart.setDate(weekStart.getDate() - ((weekStart.getDay() + 6) % 7));\nconst weekKey = ymd(weekStart);\n\n// A date written to Sheets can come back reformatted (7/27/2026, or a full timestamp), so normalise\n// whatever the log returns before comparing. A raw string compare here would silently re-send.\n// Slash dates are ambiguous (7/27/2026 vs 27/7/2026), so BOTH readings count as a match -\n// safer to block a resend than to double-send because of a locale guess.\nconst toWeekKeys = (raw) => {\n  const s = String(raw == null ? '' : raw).trim();\n  if (!s) return [];\n  const iso = s.match(/^(\\d{4})-(\\d{1,2})-(\\d{1,2})/);\n  if (iso) return [iso[1] + '-' + pad(+iso[2]) + '-' + pad(+iso[3])];\n  const slash = s.match(/^(\\d{1,2})\\/(\\d{1,2})\\/(\\d{4})/);\n  if (slash) return [slash[3] + '-' + pad(+slash[1]) + '-' + pad(+slash[2]), slash[3] + '-' + pad(+slash[2]) + '-' + pad(+slash[1])];\n  const d = new Date(s);\n  return isNaN(d.getTime()) ? [] : [ymd(d)];\n};\n\n// An empty log sheet arrives as one blank item, which simply matches nothing.\nconst alreadySent = $('Read the digest log').all().some((r) => toWeekKeys(r.json.week_start).includes(weekKey));\n\nconst nudgeDays = Number(cfg.nudgeDays) || 7;\nconst coldDays = Number(cfg.coldDays) || 21;\nconst sym = cfg.currencySymbol || '$';\n\n// Accept both 1,234.56 and 1.234,56 \u2014 whichever separator comes last is the decimal point.\nconst toNumber = (raw) => {\n  let s = String(raw == null ? '' : raw).replace(/[^0-9.,-]/g, '').trim();\n  if (!s) return 0;\n  const dec = Math.max(s.lastIndexOf('.'), s.lastIndexOf(','));\n  s = (dec > -1 && s.length - dec <= 3)\n    ? s.slice(0, dec).replace(/[.,]/g, '') + '.' + s.slice(dec + 1).replace(/[.,]/g, '')\n    : s.replace(/[.,]/g, '');\n  const n = Number(s);\n  return isNaN(n) ? 0 : n;\n};\n\nconst rows = [];\nlet skipped = 0;\nfor (const item of $('Read all quotes').all()) {\n  const q = item.json;\n  if (String(q.status || '').trim().toLowerCase() !== 'open') continue;\n  if (!q.sent_date) { skipped++; continue; }\n  const sent = parseLocalDate(q.sent_date);\n  if (isNaN(sent.getTime())) { skipped++; continue; }\n  sent.setHours(0, 0, 0, 0);\n  // round, not floor: both sides are local midnights, and floor undercounts by one across a DST spring-forward\n  const days = Math.round((today - sent) / 86400000);\n  if (days < 0) { skipped++; continue; } // a sent_date in the future is a typo, not a stale quote\n  rows.push({\n    quote_id: String(q.quote_id || ''),\n    customer_name: String(q.customer_name || '(no name)'),\n    amount: toNumber(q.quote_amount),\n    days,\n    bucket: days >= coldDays ? 'cold' : (days >= nudgeDays ? 'nudge' : 'fresh'),\n  });\n}\nrows.sort((a, b) => (b.amount - a.amount) || (b.days - a.days));\n\nconst cold = rows.filter((r) => r.bucket === 'cold');\nconst nudge = rows.filter((r) => r.bucket === 'nudge');\nconst fresh = rows.filter((r) => r.bucket === 'fresh');\nconst valueAtRisk = cold.concat(nudge).reduce((s, r) => s + r.amount, 0);\n\nconst esc = (s) => String(s).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');\nconst fmt = (n) => sym + n.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 });\nconst cell = 'padding:6px 10px;border-bottom:1px solid #eee';\nconst head = 'padding:6px 10px;border-bottom:2px solid #ddd';\n\nconst section = (title, note, list) => {\n  if (!list.length) return '';\n  const trs = list.map((r) =>\n    '<tr><td style=\"' + cell + '\">' + esc(r.customer_name) + '</td>'\n    + '<td style=\"' + cell + '\">' + esc(r.quote_id) + '</td>'\n    + '<td style=\"' + cell + ';text-align:right\">' + fmt(r.amount) + '</td>'\n    + '<td style=\"' + cell + ';text-align:right\">' + r.days + '</td></tr>').join('');\n  return '<h3 style=\"margin:22px 0 2px\">' + title + ' (' + list.length + ')</h3>'\n    + '<p style=\"margin:0 0 8px;color:#666;font-size:13px\">' + note + '</p>'\n    + '<table style=\"border-collapse:collapse;font-size:14px;width:100%\">'\n    + '<tr><th align=\"left\" style=\"' + head + '\">Customer</th>'\n    + '<th align=\"left\" style=\"' + head + '\">Quote</th>'\n    + '<th align=\"right\" style=\"' + head + '\">Amount</th>'\n    + '<th align=\"right\" style=\"' + head + '\">Days waiting</th></tr>' + trs + '</table>';\n};\n\nlet html;\nif (!rows.length) {\n  html = '<p style=\"font-size:15px\">Nothing is waiting on a reply this week \u2014 every quote in the sheet is won, lost, or has no usable send date.</p>';\n} else {\n  html = '<p style=\"font-size:15px\">You have <b>' + rows.length + '</b> open quote' + (rows.length === 1 ? '' : 's')\n    + ', and <b>' + fmt(valueAtRisk) + '</b> of that is sitting in quotes that have been quiet for ' + nudgeDays + '+ days.</p>'\n    + section('Going cold', 'No answer for ' + coldDays + '+ days. Decide: chase once more, or mark it lost and free up the headspace.', cold)\n    + section('Needs a nudge', 'Quiet for ' + nudgeDays + '-' + (coldDays - 1) + ' days. This is the week to follow up.', nudge)\n    + section('Still fresh', 'Sent less than ' + nudgeDays + ' days ago. Leave them alone for now.', fresh);\n}\nif (skipped) {\n  html += '<p style=\"margin-top:18px;color:#b45309;font-size:13px\">' + skipped + ' open quote' + (skipped === 1 ? ' was' : 's were')\n    + ' left out because their sent_date is missing, unreadable, or in the future. Fix those rows so they stop hiding.</p>';\n}\nhtml += '<p style=\"margin-top:26px;color:#888;font-size:12px\">Week of ' + weekKey + ' \u00b7 sent by your own n8n quote digest \u00b7 nobody else received this email.</p>';\n\nreturn [{\n  json: {\n    week_start: weekKey,\n    already_sent: alreadySent,\n    open_count: rows.length,\n    skipped_rows: skipped,\n    value_at_risk: Math.round(valueAtRisk * 100) / 100,\n    needs_nudge: nudge.length,\n    going_cold: cold.length,\n    to: cfg.yourEmail,\n    subject: 'Quotes waiting on a reply \u2014 week of ' + weekKey,\n    html,\n  },\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "f6a00006-0000-4000-8000-00000000000f",
      "name": "Send it once per week",
      "type": "n8n-nodes-base.if",
      "position": [
        1320,
        340
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "g1",
              "operator": {
                "type": "boolean",
                "operation": "false",
                "singleValue": true
              },
              "leftValue": "={{ $json.already_sent }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "f6a00007-0000-4000-8000-000000000010",
      "name": "Email me the digest",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1680,
        324
      ],
      "parameters": {
        "sendTo": "={{ $json.to }}",
        "message": "={{ $json.html }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.subject }}",
        "emailType": "html"
      },
      "retryOnFail": false,
      "typeVersion": 2.1
    },
    {
      "id": "f6a00008-0000-4000-8000-000000000011",
      "name": "Build the log row",
      "type": "n8n-nodes-base.code",
      "position": [
        1940,
        324
      ],
      "parameters": {
        "jsCode": "// Concrete keys only. The Sheets node maps plain input fields onto columns, so a column name\n// built from an expression would be written as a literal instead of being evaluated.\nconst d = $('Build the weekly digest').first().json;\nreturn [{\n  json: {\n    week_start: d.week_start,\n    open_count: d.open_count,\n    value_at_risk: d.value_at_risk,\n    needs_nudge: d.needs_nudge,\n    going_cold: d.going_cold,\n    sent_at: $now.toFormat('yyyy-MM-dd HH:mm'),\n  },\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "f6a00009-0000-4000-8000-000000000012",
      "name": "Log this week's snapshot",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        2200,
        324
      ],
      "parameters": {
        "columns": {
          "value": {},
          "mappingMode": "autoMapInputData",
          "matchingColumns": [
            "week_start"
          ]
        },
        "options": {
          "cellFormat": "RAW"
        },
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": ""
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.5,
      "waitBetweenTries": 5000
    }
  ],
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Read all quotes": {
      "main": [
        [
          {
            "node": "Read the digest log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build the log row": {
      "main": [
        [
          {
            "node": "Log this week's snapshot",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Email me the digest": {
      "main": [
        [
          {
            "node": "Build the log row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Every Monday at 8am": {
      "main": [
        [
          {
            "node": "\u2699\ufe0f Your settings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read the digest log": {
      "main": [
        [
          {
            "node": "Build the weekly digest",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "\u2699\ufe0f Your settings": {
      "main": [
        [
          {
            "node": "Read all quotes",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send it once per week": {
      "main": [
        [
          {
            "node": "Email me the digest",
            "type": "main",
            "index": 0
          }
        ],
        []
      ]
    },
    "Build the weekly digest": {
      "main": [
        [
          {
            "node": "Send it once per week",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

This workflow runs every Monday morning, reads your quotes from Google Sheets, and emails you an HTML digest via Gmail of quotes still marked open, grouped by how long they have been unanswered, then logs a weekly snapshot back to Google Sheets to prevent duplicate sends. Runs…

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

More Email & Gmail workflows → · Browse all categories →

Related workflows

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