AI-Powered Multi-Source Web Research and Google Sheets Report Generator - n8n Workflow

• Market researchers needing automatically structured data from the web.


• Data analysts who require sophisticated, pre-formatted reports in Google Sheets.


• Automation specialists seeking advanced n8n templates demonstrating AI agents and custom code orchestration.


• Developers using n8n to integrate multiple external scraping APIs (Brave, Firecrawl, Apify).


• Users looking for a complex n8n workflow to manage AI-driven data collection.

Generating structured, analysis-ready data from unstructured web content is often time-consuming. This powerful n8n workflow solves this by using a central AI Orchestrator (Google Gemini) to intelligently select and execute the best web scraping or search tools (Firecrawl, Brave, Apify) based on a natural language request.

The resulting data, which is typically in Markdown format, is then processed by a custom n8n node, the 'Format Research Data' Code node. This node not only parses the output but also constructs highly detailed Google Sheets API batch update requests, ensuring the final report is delivered across multiple, perfectly formatted sheets (Summary, Categories, Jobs, Statistics). This n8n workflow is a prime example of high-level automation, blending generative AI, multiple APIs, and complex data restructuring into one seamless process.

This n8n workflow begins with the 'When chat message received' n8n trigger, initiating the process upon receiving a user's research query.

1. AI Orchestration: The query flows to the 'Gemini Research Orchestrator' (an n8n Agent node) powered by the 'Google Gemini Chat Model' and supported by 'Simple Memory' for context. The agent's core instruction is to use available tools (Brave for searching, Firecrawl and Apify for scraping) to fulfill the request.


2. Tool Execution: Based on the agent's decision, the relevant MCP Client Tool (such as 'Brave execute' or 'Firecrawl execute') runs, collecting the raw web data.


3. Report Initialization: Once the agent completes its research, the data is passed to the 'Create Research Report' Google Sheets n8n node, which creates a new spreadsheet.


4. Data Structuring & Formatting: The critical 'Format Research Data' Code n8n node takes the agent’s final output. This script parses the Markdown results, extracts structured job and category data, and generates two sets of Google Sheets API calls: one for creating and renaming four detailed sheets (Resume, Categories, Emplois, Statistiques) and another for inserting the actual data.


5. Report Population: The 'Populate Research Report' n8n node executes the sheet formatting requests (creating tabs, setting colors, and borders) via an HTTP Request node. The subsequent 'Data Enrichment Request' n8n node inserts all the structured data into the pre-formatted cells.


6. Final Output: The process concludes at the 'Finalize Output Data' n8n node, which aggregates metadata like the new spreadsheet ID for the user.

To deploy this comprehensive n8n template, follow these steps:

1. Import the n8n workflow: Copy the provided JSON data and paste it into your n8n instance using the 'New' -> 'Import from JSON' function.


2. Set up AI Credentials: Configure the 'Google Gemini Chat Model' n8n node with your Google Gemini (or PaLM) API Key.


3. Set up Tool Credentials (MCP): This workflow relies on external scraping services. You must create credentials for the following MCP Client Tool nodes (Firecrawl, Brave, Apify) within your n8n environment.


4. Set up Google Sheets: Establish a Google Sheets OAuth2 API credential and select it in the 'Create Research Report' n8n node and the two subsequent HTTP Request nodes. Ensure the credential has sufficient permissions to create and modify spreadsheets.


5. Activate and Test: Activate the n8n workflow. Use the 'When chat message received' n8n trigger to send a natural language research request (e.g., 'Find the latest job openings for AI specialists on five major tech company career pages').

• When chat message received (n8n trigger): The entry point. It receives the user's research query via an API or chat interface.


• Google Gemini Chat Model: The Large Language Model providing the intelligence for the agent, driving the analysis and tool selection.


• Gemini Research Orchestrator (n8n node): The core LangChain Agent. It acts as the brain, determining which external tool (Brave, Firecrawl, or Apify) to utilize for web searching and scraping based on the user's prompt.


• Firecrawl/Brave/Apify (MCP Client Tool n8n node): These nodes provide the actual web interaction capabilities. The workflow uses list operations to expose the tools to the agent and execute operations to run the scraping or searching logic, enabling multi-source extraction.


• Create Research Report (Google Sheets n8n node): Creates the target spreadsheet where all the collected and structured data will reside.


• Format Research Data (Code n8n node): This is the most complex n8n node in the flow. It parses Markdown output from the AI, calculates statistics, and dynamically generates batchUpdate requests to Google Sheets to structure the report across four different tabs with detailed cell formatting (colors, fonts, borders).


• Populate Research Report / Data Enrichment Request (HTTP Request n8n node): These nodes communicate directly with the Google Sheets API to apply the sheet formatting requests and insert the structured data, leveraging batch operations for efficiency.