Track Recurring Job Pain Points with Serpapi, Gemini, 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 →

{
  "id": "plCCZbCjDQncohMs",
  "name": "Weekly Job Pain-Point Scraper Updated",
  "tags": [],
  "nodes": [
    {
      "id": "1c207b49-034f-498b-843f-9a580cbb780f",
      "name": "Sticky Note - Overview",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        16,
        272
      ],
      "parameters": {
        "color": 2,
        "width": 600,
        "height": 744,
        "content": "Track recurring pain points from job postings weekly\n\nHow it works\n\nEvery week, this workflow searches Google Jobs (via SerpApi) for a target role and location, sends each posting to Gemini to extract recurring operational pain points and one automation opportunity, and logs the results to Google Sheets. It then re-reads the whole sheet, ranks pain-point phrases by how often they appear across all postings collected so far, and writes that ranked list to a summary tab.\n\nSetup steps\n\n- Add your SerpApi key in the Fetch Job Postings node (or move it into an HTTP credential).\n- Connect a Google Sheets credential and point the three Sheets nodes at your own spreadsheet.\n- Connect your Gemini credential in the AI Model node.\n- Adjust role/location/sheetTab in Set Search Config to target the job title you want to track.\n\nRequirements\n\nSerpApi account, Google Sheet with a tab per role plus a \"summary\" tab, Google Gemini API key.\n\nHow to customize\n\nTrack multiple roles by duplicating the Set Search Config -> Sheets branch per role, or swap Gemini for another LLM in the AI Model node."
      },
      "typeVersion": 1
    },
    {
      "id": "aab8c35d-e173-4548-9846-5b8aba98de9c",
      "name": "Sticky - Trigger",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        672,
        272
      ],
      "parameters": {
        "color": 4,
        "width": 508,
        "height": 464,
        "content": "Trigger\n\nRuns weekly\n\nSets the role, location, and sheet tab to search for this run."
      },
      "typeVersion": 1
    },
    {
      "id": "be5dc998-cfb1-442e-b1d8-2fee063edf1a",
      "name": "Sticky - Fetch Jobs",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1248,
        272
      ],
      "parameters": {
        "color": 5,
        "width": 460,
        "height": 464,
        "content": "Fetch job postings\n\nQueries Google Jobs via SerpApi and splits the results into one item per posting."
      },
      "typeVersion": 1
    },
    {
      "id": "0362266e-ded4-40e8-b0cf-95f24679251c",
      "name": "Sticky - AI Analysis",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1776,
        272
      ],
      "parameters": {
        "color": 6,
        "width": 528,
        "height": 464,
        "content": "AI analysis\n\nGemini reads each posting and extracts recurring pain points plus one automation opportunity, returned as structured JSON."
      },
      "typeVersion": 1
    },
    {
      "id": "2dcdba98-a0a4-41eb-bc3a-ceb60f8d7708",
      "name": "Sticky - Store & Aggregate",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2336,
        272
      ],
      "parameters": {
        "color": 5,
        "width": 672,
        "height": 464,
        "content": "Store and aggregate\n\nAppends each result to the sheet, then re-reads and combines every row collected so far (not just this run)."
      },
      "typeVersion": 1
    },
    {
      "id": "cd394aff-b529-4a17-9ea6-1f79a0b8ead4",
      "name": "Sticky - Rank",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3040,
        272
      ],
      "parameters": {
        "color": 6,
        "width": 296,
        "height": 464,
        "content": "Rank pain points\n\nCounts how often each pain-point phrase appears across all postings and sorts by frequency."
      },
      "typeVersion": 1
    },
    {
      "id": "69729b56-e948-4ef7-9cb0-c47dcf5a1814",
      "name": "Sticky - Output",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3376,
        272
      ],
      "parameters": {
        "width": 280,
        "height": 464,
        "content": "Write summary\n\nSaves the ranked pain-point list to the summary tab."
      },
      "typeVersion": 1
    },
    {
      "id": "2791cbb5-bcc6-4155-8fe0-e0ed7acceed6",
      "name": "Weekly Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        704,
        432
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "ace5af54-4344-4f16-8d7c-ff5129efebb2",
      "name": "Set Search Config",
      "type": "n8n-nodes-base.set",
      "notes": "Change role/location/sheetTab here to target a different job title.",
      "position": [
        1040,
        432
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "role-field",
              "name": "role",
              "type": "string",
              "value": "receptionist"
            },
            {
              "id": "location-field",
              "name": "location",
              "type": "string",
              "value": "United States"
            },
            {
              "id": "sheet-tab-field",
              "name": "sheetTab",
              "type": "string",
              "value": "receptionist"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "06ecb711-2298-4c7d-a61b-a50a471af05a",
      "name": "Fetch Job Postings (SerpApi)",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Replace YOUR_SERPAPI_KEY_HERE with your real SerpApi key, or move it into an n8n credential for security.",
      "position": [
        1312,
        432
      ],
      "parameters": {
        "url": "https://serpapi.com/search.json",
        "options": {},
        "sendQuery": true,
        "queryParameters": {
          "parameters": [
            {
              "name": "engine",
              "value": "google_jobs"
            },
            {
              "name": "q",
              "value": "={{ $json.role }}"
            },
            {
              "name": "location",
              "value": "={{ $json.location }}"
            },
            {
              "name": "api_key",
              "value": "YOUR_SERPAPI_KEY_HERE"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "5bce6cae-b59f-49d0-9c89-2b8c10d3681a",
      "name": "Split Out Jobs",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        1568,
        432
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "jobs_results"
      },
      "typeVersion": 1
    },
    {
      "id": "20d3cb7b-e6ce-4423-9f38-e6d377d7dfcd",
      "name": "Parse AI Output",
      "type": "n8n-nodes-base.code",
      "position": [
        2160,
        432
      ],
      "parameters": {
        "jsCode": "// Parse the AI's JSON reply safely and pass it downstream as structured fields\n// Gemini's 'Message a Model' node returns text at content.parts[0].text\nconst raw = $input.item.json.content?.parts?.[0]?.text || $input.item.json.text || '';\nconst cleaned = raw.replace(/```json|```/g, '').trim();\n\nlet parsed;\ntry {\n  parsed = JSON.parse(cleaned);\n} catch (e) {\n  parsed = {\n    company: 'PARSE_ERROR',\n    title: 'PARSE_ERROR',\n    pain_points: [],\n    automation_opportunity: raw.slice(0, 200)\n  };\n}\n\nreturn {\n  json: {\n    company: parsed.company || '',\n    title: parsed.title || '',\n    pain_points: (parsed.pain_points || []).join(', '),\n    automation_opportunity: parsed.automation_opportunity || '',\n    date_collected: new Date().toISOString().split('T')[0]\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "89449a84-b617-46f1-9211-acfb4cdcdfaf",
      "name": "Append to Google Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Replace YOUR_GOOGLE_SHEET_ID_HERE. This runs once per job posting, automatically, in sequence \u2014 no manual loop needed.",
      "position": [
        2400,
        432
      ],
      "parameters": {
        "columns": {
          "value": {
            "Title": "={{ $json.title }}",
            "Company": "={{ $json.company }}",
            "Pain Points": "={{ $json.pain_points }}",
            "Date Collected": "={{ $json.date_collected }}",
            "Automation Opportunity": "={{ $json.automation_opportunity }}"
          },
          "schema": [
            {
              "id": "Company",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Company",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Title",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Title",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Pain Points",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Pain Points",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Automation Opportunity",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Automation Opportunity",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date Collected",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date Collected",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "=receptionist"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "f701a70e-76ae-45b6-870d-69f7bf87d0e7",
      "name": "Read All Sheet Rows",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Runs once after all rows are appended. Reads the whole sheet back so we can rank pain points across ALL postings, not just this run's in-memory items. Same Sheet ID as the Append node.",
      "position": [
        2640,
        432
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "receptionist"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "1ffd4310-744a-4909-8512-8a7e99b67f85",
      "name": "Aggregate Sheet Rows",
      "type": "n8n-nodes-base.aggregate",
      "notes": "Combines all sheet rows into a single list so the ranking code can process them in one pass.",
      "position": [
        2896,
        432
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData"
      },
      "typeVersion": 1
    },
    {
      "id": "f98f54c8-7765-4636-a6f9-ac5e92ca5dee",
      "name": "Rank Pain Points by Frequency",
      "type": "n8n-nodes-base.code",
      "position": [
        3136,
        432
      ],
      "parameters": {
        "jsCode": "// Count frequency of each pain-point phrase across ALL rows in the sheet\nconst items = $input.all();\nconst counts = {};\n\nfor (const item of items) {\n  const rows = item.json.data || [item.json];\n  for (const row of rows) {\n   const phrases = (row['Pain Points'] || '').split(',').map(p => p.trim()).filter(Boolean);\n    for (const phrase of phrases) {\n      const key = phrase.toLowerCase();\n      counts[key] = (counts[key] || 0) + 1;\n    }\n  }\n}\n\nconst ranked = Object.entries(counts)\n  .sort((a, b) => b[1] - a[1])\n  .map(([phrase, count]) => ({ phrase, count }));\n\nreturn ranked.map(r => ({ json: r }));"
      },
      "typeVersion": 2
    },
    {
      "id": "9db9cd0e-427c-407e-b693-71fe932675a5",
      "name": "Write Summary Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "notes": "Replace YOUR_GOOGLE_SHEET_ID_HERE. Writes ranked frequency list to the 'summary' tab. Uses append, not update.",
      "position": [
        3456,
        432
      ],
      "parameters": {
        "columns": {
          "value": {
            "Count": "={{ $json.count }}",
            "Pharse": "={{ $json.phrase }}"
          },
          "schema": [
            {
              "id": "Pharse",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Pharse",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Pharse"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "summary"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/YOUR_GOOGLE_SHEET_ID/edit",
          "__regex": "https:\\/\\/(?:drive|docs)\\.google\\.com(?:\\/.*|)\\/d\\/([0-9a-zA-Z\\-_]+)(?:\\/.*|)"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "999d52d2-84d7-4577-a913-4491a0f49a27",
      "name": "AI Model",
      "type": "@n8n/n8n-nodes-langchain.googleGemini",
      "notes": "Runs automatically once per item from Split Out Jobs \u2014 no manual loop node needed. Connect your Gemini credential here.",
      "position": [
        1808,
        432
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "models/gemini-3.1-flash-lite",
          "cachedResultName": "models/gemini-3.1-flash-lite"
        },
        "options": {
          "systemMessage": "You extract recurring operational pain points from job postings. Read the job description and return ONLY valid JSON, no markdown, no preamble, in this exact shape: {   \"company\": string,   \"title\": string,   \"pain_points\": string[] (3-6 short phrases),   \"automation_opportunity\": string (1 sentence naming the single biggest automatable task) }"
        },
        "messages": {
          "values": [
            {
              "content": "You extract recurring operational pain points from job postings. Read the job description and return ONLY valid JSON, no markdown, no preamble, in this exact shape:\n{\n  \"company\": string,\n  \"title\": string,\n  \"pain_points\": string[] (3-6 short phrases, e.g. \"high call volume\", \"multi-line phone system\", \"manual appointment scheduling\", \"bilingual requirement\", \"insurance/billing follow-up\", \"visitor/package management\"),\n  \"automation_opportunity\": string (1 sentence naming the single biggest automatable task in this posting)\n}"
            },
            {
              "content": "=Job Title: {{ $json.title }}\nCompany: {{ $json.company_name }}\nDescription: {{ $json.description }}"
            }
          ]
        }
      },
      "credentials": {
        "googlePalmApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "settings": {
    "binaryMode": "separate",
    "executionOrder": "v1"
  },
  "versionId": "95ed5918-adc7-427a-976f-f5a4810fb8b2",
  "nodeGroups": [],
  "connections": {
    "AI Model": {
      "main": [
        [
          {
            "node": "Parse AI Output",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Split Out Jobs": {
      "main": [
        [
          {
            "node": "AI Model",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Weekly Trigger": {
      "main": [
        [
          {
            "node": "Set Search Config",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Parse AI Output": {
      "main": [
        [
          {
            "node": "Append to Google Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Search Config": {
      "main": [
        [
          {
            "node": "Fetch Job Postings (SerpApi)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read All Sheet Rows": {
      "main": [
        [
          {
            "node": "Aggregate Sheet Rows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate Sheet Rows": {
      "main": [
        [
          {
            "node": "Rank Pain Points by Frequency",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Append to Google Sheet": {
      "main": [
        [
          {
            "node": "Read All Sheet Rows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Job Postings (SerpApi)": {
      "main": [
        [
          {
            "node": "Split Out Jobs",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rank Pain Points by Frequency": {
      "main": [
        [
          {
            "node": "Write Summary Sheet",
            "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.

• googlePalmApi
• googleSheetsOAuth2Api

About this workflow

This workflow runs weekly to pull Google Jobs results via SerpApi, uses Google Gemini to extract operational pain points and an automation opportunity from each posting, logs them to Google Sheets, and writes a frequency-ranked pain-point summary to a summary tab. Runs weekly on…

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