DEV Community

Mikko Törmä
Mikko Törmä

Posted on Fully Autonomous

n8n template: quote request emails to Google Sheets with a Gmail draft reply

An experimental n8n workflow example intended to log quote requests in Google Sheets and create Gmail draft replies. Code logic was tested with synthetic inputs; the complete workflow has not been verified with connected services. The workflow export below is free to copy. You need your own n8n environment and service accounts, which may have their own charges. Use a test environment with made-up data first. Copy the JSON onto an empty n8n canvas (Ctrl+V) and follow the setup steps.

For small contractors using Gmail and Google Sheets. The workflow checks incoming emails using rules, appends matching requests to a sheet and saves a Gmail draft. You read and send the draft yourself.

Requirements: your own n8n instance, a Gmail account that can read email and create drafts, and a Google Sheets account with edit access to the request spreadsheet. Select these credentials in Gmail Trigger, Add row to sheet and Save reply as draft. No community nodes or sub-workflows are required.

Setup:

  1. Create a spreadsheet and a tab called Requests. Copy these exact column headings into its first row: RFQ ID, Received, Name, Email, Phone, Trade, Location, Deadline, Budget, Scope, Missing, Priority, Status, Subject, Thread ID.
  2. Replace REPLACE_WITH_SPREADSHEET_ID in Add row to sheet with your spreadsheet ID. Keep the tab name Requests.
  3. In Settings, replace ownEmail, companyName and senderName. Set timezoneLabel, slotMorning and slotAfternoon to the label and call times you want to offer. The label is text, not a time zone conversion.
  4. Keep the Gmail query in:inbox -from:me and Simplify off. Check the trigger's access to the intended inbox.
  5. Test with made-up quote requests delivered to a test inbox. Check the sheet row, recipient, thread, dates and full draft text. Test a non-request and a message with missing details. Check denied credentials and a missing sheet. Resolve errors before publishing or activating.
  6. Activate only after you have verified the results. Monitor n8n execution errors and check the sheet and drafts after a failure before retrying.

Limits: English rules may miss unusual wording and unlabelled details. Read every draft and check the offered dates and availability. The workflow appends rows and has no persistent duplicate check; replays or retries can create duplicate rows or drafts. No automatic sending is configured.

Made with AI tools. Code logic was tested on made-up inputs; this is not evidence of a complete run with connected services. A complete run of the exact export is required before submission to n8n Creator Hub. Verify the complete workflow in your own test environment before activating it for real use.

Workflow JSON

{
  "name": "Request email to sheet and reply draft",
  "nodes": [
    {
      "parameters": {
        "content": "## Quote request emails to a sheet and a draft reply\n\nFor small contractors using Gmail and Google Sheets. The workflow checks incoming emails using rules, appends matching requests to a sheet and saves a Gmail draft. You read and send the draft yourself.\n\nRequirements: your own n8n instance, a Gmail account that can read email and create drafts, and a Google Sheets account with edit access to the request spreadsheet. Select these credentials in Gmail Trigger, Add row to sheet and Save reply as draft. No community nodes or sub-workflows are required.\n\nSetup:\n1. Create a spreadsheet and a tab called Requests. Copy these exact column headings into its first row:\nRFQ ID, Received, Name, Email, Phone, Trade, Location, Deadline, Budget, Scope, Missing, Priority, Status, Subject, Thread ID.\n2. Replace REPLACE_WITH_SPREADSHEET_ID in Add row to sheet with your spreadsheet ID. Keep the tab name Requests.\n3. In Settings, replace ownEmail, companyName and senderName. Set timezoneLabel, slotMorning and slotAfternoon to the label and call times you want to offer. The label is text, not a time zone conversion.\n4. Keep the Gmail query in:inbox -from:me and Simplify off. Check the trigger's access to the intended inbox.\n5. Test with made-up quote requests delivered to a test inbox. Check the sheet row, recipient, thread, dates and full draft text. Test a non-request and a message with missing details. Check denied credentials and a missing sheet. Resolve errors before publishing or activating.\n6. Activate only after you have verified the results. Monitor n8n execution errors and check the sheet and drafts after a failure before retrying.\n\nLimits: English rules may miss unusual wording and unlabelled details. Read every draft and check the offered dates and availability. The workflow appends rows and has no persistent duplicate check; replays or retries can create duplicate rows or drafts. No automatic sending is configured.\n\nMade with AI tools. Code logic was tested on made-up inputs; this is not evidence of a complete run with connected services. A complete run of the exact export is required before submission to n8n Creator Hub. Verify the complete workflow in your own test environment before activating it for real use.",
        "width": 650,
        "height": 1200
      },
      "id": "00000000-0000-4000-8000-000000000001",
      "name": "Setup notes",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -80,
        -260
      ]
    },
    {
      "parameters": {
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "simple": false,
        "filters": {
          "q": "in:inbox -from:me"
        },
        "options": {}
      },
      "id": "00000000-0000-4000-8000-000000000002",
      "name": "Gmail Trigger",
      "type": "n8n-nodes-base.gmailTrigger",
      "typeVersion": 1.3,
      "position": [
        0,
        0
      ]
    },
    {
      "parameters": {
        "assignments": {
          "assignments": [
            {
              "id": "00000000-0000-4000-8000-000000000003",
              "name": "ownEmail",
              "value": "you@your-company.example",
              "type": "string"
            },
            {
              "id": "00000000-0000-4000-8000-000000000004",
              "name": "companyName",
              "value": "Your Company LLC",
              "type": "string"
            },
            {
              "id": "00000000-0000-4000-8000-000000000005",
              "name": "senderName",
              "value": "Your Name",
              "type": "string"
            },
            {
              "id": "00000000-0000-4000-8000-000000000006",
              "name": "timezoneLabel",
              "value": "Central Time",
              "type": "string"
            },
            {
              "id": "00000000-0000-4000-8000-000000000007",
              "name": "slotMorning",
              "value": "10:00 AM",
              "type": "string"
            },
            {
              "id": "00000000-0000-4000-8000-000000000008",
              "name": "slotAfternoon",
              "value": "2:00 PM",
              "type": "string"
            }
          ]
        },
        "includeOtherFields": true,
        "options": {}
      },
      "id": "00000000-0000-4000-8000-000000000009",
      "name": "Settings",
      "type": "n8n-nodes-base.set",
      "typeVersion": 3.4,
      "position": [
        220,
        0
      ]
    },
    {
      "parameters": {
        "jsCode": "// Code node \"Parse request\": turns an incoming email into one structured RFQ row.\r\n// Runs once for all items. Reads the Settings node. No external calls.\r\nconst cfg = $('Settings').first().json;\r\nconst MONTHS = 'jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec';\r\nconst TRADES = [\r\n  ['Plumbing', /plumb|pipe|water heater|drain|sewer|fixture/i],\r\n  ['HVAC', /hvac|heating|furnace|air condition|ventilation|duct|boiler|heat pump/i],\r\n  ['Electrical', /electric|wiring|panel|lighting|generator|conduit/i],\r\n  ['Roofing', /roof|shingle|gutter/i],\r\n  ['General', /renovat|remodel|build|construction|concrete|framing|drywall|paint/i],\r\n];\r\n\r\nfunction labelled(body, labels) {\r\n  const re = new RegExp('^\\\\s*(?:' + labels + ')\\\\s*[:\\\\-]\\\\s*(.+)$', 'im');\r\n  const m = body.match(re);\r\n  return m ? m[1].trim() : '';\r\n}\r\n\r\nfunction parseSender(raw) {\r\n  const text = typeof raw === 'string' ? raw : (raw?.text ?? raw?.value?.[0]?.address ?? '');\r\n  const em = text.match(/([^\\s<>\"',;]+@[^\\s<>\"',;]+)/);\r\n  const email = em ? em[1].toLowerCase() : '';\r\n  let name = text.replace(/<[^>]*>/, '').replace(/[\"']/g, '').trim();\r\n  if (!name || name.includes('@')) name = typeof raw === 'object' && raw?.value?.[0]?.name ? raw.value[0].name : '';\r\n  return { name, email };\r\n}\r\n\r\nfunction parseDeadline(text, received) {\r\n  if (!text) return null;\r\n  let t = text.replace(/(\\d)(st|nd|rd|th)\\b/gi, '$1');\r\n  const hasYear = /\\b20\\d{2}\\b/.test(t) || /\\d[\\/.]\\d{1,2}[\\/.]\\d{2,4}/.test(t);\r\n  if (!hasYear) t += ', ' + received.getUTCFullYear();\r\n  const d = new Date(t + ' 12:00:00 UTC');\r\n  return isNaN(d) ? null : d;\r\n}\r\n\r\nconst rows = [];\r\nfor (const item of $input.all()) {\r\n  const m = item.json;\r\n  const subject = String(m.subject ?? m.Subject ?? '').trim();\r\n  const body = String(m.text ?? m.textPlain ?? m.snippet ?? '').replace(/\\r/g, '');\r\n  const sender = parseSender(m.from ?? m.From ?? '');\r\n  const receivedRaw = m.date ?? m.internalDate ?? null;\r\n  const received = receivedRaw && !isNaN(new Date(receivedRaw)) ? new Date(receivedRaw) : new Date();\r\n\r\n  const own = String(cfg.ownEmail || '').toLowerCase();\r\n  const automated = /no-?reply|donotreply|mailer-daemon|newsletter|unsubscribe/i.test(sender.email + ' ' + body.slice(-400));\r\n  const looksLikeRfq = /\\b(rfq|request for (?:a )?(?:quote|quotation|proposal|bid)s?|invitation to bid|bid request|quote|estimate|tender|proposal|bid)\\b/i.test(subject + ' ' + body);\r\n  const isRfq = looksLikeRfq && !automated && sender.email !== '' && sender.email !== own;\r\n\r\n  const phoneLabelled = labelled(body, 'phone|tel|mobile|cell');\r\n  const phoneAny = body.match(/(?:\\+?1[\\s.-]?)?\\(?\\d{3}\\)?[\\s.-]?\\d{3}[\\s.-]?\\d{4}/);\r\n  const phone = phoneLabelled || (phoneAny ? phoneAny[0].trim() : '');\r\n\r\n  const location = labelled(body, 'project address|job ?site|site address|address|location|site');\r\n\r\n  let deadlineText = labelled(body, 'bids? due|due date|due|deadline|submit by|needed by');\r\n  if (!deadlineText) {\r\n    const dm = body.match(new RegExp('(?:due|by|before|deadline(?: is)?)\\\\s+(?:on\\\\s+)?((?:' + MONTHS + ')[a-z]*\\\\.?\\\\s+\\\\d{1,2}(?:st|nd|rd|th)?(?:,?\\\\s+\\\\d{4})?|\\\\d{1,2}[\\\\/.]\\\\d{1,2}(?:[\\\\/.]\\\\d{2,4})?)', 'i'));\r\n    deadlineText = dm ? dm[1].trim() : '';\r\n  }\r\n  const deadline = parseDeadline(deadlineText, received);\r\n\r\n  const budgetLabelled = labelled(body, 'budget');\r\n  const budgetAny = body.match(/\\$\\s?\\d[\\d,]*(?:\\.\\d+)?\\s?[kK]?/);\r\n  const budget = budgetLabelled || (budgetAny ? budgetAny[0].trim() : '');\r\n\r\n  const hay = subject + ' ' + body;\r\n  const trade = (TRADES.find(([, re]) => re.test(hay)) || ['Other'])[0];\r\n\r\n  const scopeLabelled = labelled(body, 'scope|project|description|details');\r\n  const scope = (scopeLabelled || body.replace(/\\s+/g, ' ').trim()).slice(0, 280);\r\n\r\n  const missing = [];\r\n  if (!phone) missing.push('phone number');\r\n  if (!location) missing.push('project address');\r\n  if (!deadline && !deadlineText) missing.push('bid due date');\r\n  if (scope.length < 40) missing.push('scope of work');\r\n\r\n  const daysLeft = deadline ? Math.ceil((deadline - received) / 86400000) : null;\r\n  const priority = daysLeft !== null && daysLeft <= 7 ? 'High' : 'Normal';\r\n\r\n  const stamp = received.toISOString().slice(0, 10);\r\n  const idTail = String(m.id ?? '0000').replace(/[^a-z0-9]/gi, '').slice(-4).toUpperCase().padStart(4, '0');\r\n\r\n  rows.push({\r\n    json: {\r\n      isRfq,\r\n      rfqId: 'RFQ-' + stamp.replace(/-/g, '') + '-' + idTail,\r\n      received: stamp,\r\n      name: sender.name,\r\n      email: sender.email,\r\n      phone,\r\n      trade,\r\n      location,\r\n      deadline: deadline ? deadline.toISOString().slice(0, 10) : deadlineText,\r\n      budget,\r\n      scope,\r\n      missing: missing.join(', '),\r\n      priority,\r\n      status: 'Reply drafted',\r\n      subject,\r\n      threadId: m.threadId ?? '',\r\n    },\r\n  });\r\n}\r\nreturn rows;\r\n"
      },
      "id": "00000000-0000-4000-8000-000000000010",
      "name": "Parse request",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        440,
        0
      ]
    },
    {
      "parameters": {
        "conditions": {
          "options": {
            "caseSensitive": true,
            "leftValue": "",
            "typeValidation": "strict",
            "version": 2
          },
          "conditions": [
            {
              "id": "00000000-0000-4000-8000-000000000011",
              "leftValue": "={{ $json.isRfq }}",
              "rightValue": "",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              }
            }
          ],
          "combinator": "and"
        },
        "options": {}
      },
      "id": "00000000-0000-4000-8000-000000000012",
      "name": "Is it a request?",
      "type": "n8n-nodes-base.if",
      "typeVersion": 2.2,
      "position": [
        660,
        0
      ]
    },
    {
      "parameters": {
        "jsCode": "// Code node \"Write reply\": builds the first reply for each RFQ row.\r\n// The reply confirms receipt, asks for missing details and offers two call times.\r\n// It never states a price or a date for the bid.\r\nconst cfg = $('Settings').first().json;\r\n\r\nfunction nextBusinessDays(from, count) {\r\n  const days = [];\r\n  const d = new Date(Date.UTC(from.getUTCFullYear(), from.getUTCMonth(), from.getUTCDate()));\r\n  while (days.length < count) {\r\n    d.setUTCDate(d.getUTCDate() + 1);\r\n    const wd = d.getUTCDay();\r\n    if (wd !== 0 && wd !== 6) days.push(new Date(d));\r\n  }\r\n  return days;\r\n}\r\nconst fmt = (d) => d.toLocaleDateString('en-US', { weekday: 'long', month: 'long', day: 'numeric', timeZone: 'UTC' });\r\n\r\nconst out = [];\r\nfor (const item of $input.all()) {\r\n  const r = item.json;\r\n  const first = (r.name || '').split(/\\s+/)[0] || 'there';\r\n  const [d1, d2] = nextBusinessDays(new Date(r.received + 'T12:00:00Z'), 2);\r\n  const lines = [];\r\n  lines.push('Hi ' + first + ',');\r\n  lines.push('');\r\n  lines.push('Thanks for sending this over. We received your request (reference ' + r.rfqId + ') and one of us will review it.');\r\n  if (r.missing) {\r\n    lines.push('');\r\n    lines.push('To prepare an accurate quote we still need: ' + r.missing + '. Reply to this email with those and we will pick it up from there.');\r\n  }\r\n  lines.push('');\r\n  lines.push('If a short call is easier, we can do ' + fmt(d1) + ' at ' + cfg.slotMorning + ' or ' + fmt(d2) + ' at ' + cfg.slotAfternoon + ' (' + cfg.timezoneLabel + '). Tell us which suits you, or suggest another time.');\r\n  if (r.priority === 'High') {\r\n    lines.push('');\r\n    lines.push('We saw the due date in your message and are looking at it first.');\r\n  }\r\n  lines.push('');\r\n  lines.push('Regards,');\r\n  lines.push(cfg.senderName);\r\n  lines.push(cfg.companyName);\r\n  out.push({\r\n    json: {\r\n      ...r,\r\n      replySubject: /^re:/i.test(r.subject) ? r.subject : 'Re: ' + (r.subject || 'Your request'),\r\n      replyBody: lines.join('\\n'),\r\n    },\r\n  });\r\n}\r\nreturn out;\r\n"
      },
      "id": "00000000-0000-4000-8000-000000000013",
      "name": "Write reply",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        880,
        -100
      ]
    },
    {
      "parameters": {
        "operation": "append",
        "documentId": {
          "__rl": true,
          "value": "REPLACE_WITH_SPREADSHEET_ID",
          "mode": "id"
        },
        "sheetName": {
          "__rl": true,
          "value": "Requests",
          "mode": "name"
        },
        "columns": {
          "mappingMode": "defineBelow",
          "value": {
            "RFQ ID": "={{ $json.rfqId }}",
            "Received": "={{ $json.received }}",
            "Name": "={{ $json.name }}",
            "Email": "={{ $json.email }}",
            "Phone": "={{ $json.phone }}",
            "Trade": "={{ $json.trade }}",
            "Location": "={{ $json.location }}",
            "Deadline": "={{ $json.deadline }}",
            "Budget": "={{ $json.budget }}",
            "Scope": "={{ $json.scope }}",
            "Missing": "={{ $json.missing }}",
            "Priority": "={{ $json.priority }}",
            "Status": "={{ $json.status }}",
            "Subject": "={{ $json.subject }}",
            "Thread ID": "={{ $json.threadId }}"
          },
          "matchingColumns": [],
          "schema": []
        },
        "options": {}
      },
      "id": "00000000-0000-4000-8000-000000000014",
      "name": "Add row to sheet",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4.5,
      "position": [
        1100,
        -100
      ]
    },
    {
      "parameters": {
        "resource": "draft",
        "subject": "={{ $('Write reply').item.json.replySubject }}",
        "emailType": "text",
        "message": "={{ $('Write reply').item.json.replyBody }}",
        "options": {
          "threadId": "={{ $('Write reply').item.json.threadId }}",
          "sendTo": "={{ $('Write reply').item.json.email }}"
        }
      },
      "id": "00000000-0000-4000-8000-000000000015",
      "name": "Save reply as draft",
      "type": "n8n-nodes-base.gmail",
      "typeVersion": 2.1,
      "position": [
        1320,
        -100
      ]
    }
  ],
  "connections": {
    "Gmail Trigger": {
      "main": [
        [
          {
            "node": "Settings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Settings": {
      "main": [
        [
          {
            "node": "Parse request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse request": {
      "main": [
        [
          {
            "node": "Is it a request?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Is it a request?": {
      "main": [
        [
          {
            "node": "Write reply",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Write reply": {
      "main": [
        [
          {
            "node": "Add row to sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Add row to sheet": {
      "main": [
        [
          {
            "node": "Save reply as draft",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "executionOrder": "v1"
  },
  "pinData": {},
  "tags": []
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)