Build Your First AI Data Analyst Chatbot in n8n

Import data from Google Sheets → AI Agent retrieves and calculates → Chat interface returns analysis

Who

Users who want to quickly build an AI data-query and analysis chatbot in n8n

You get

A chat-interactive AI Agent workflow that pulls data from Google Sheets and performs mathematical calculations

You need

n8n

Steps

1. Import template and configure credentials

   Import the template workflow.json into n8n. Set up credentials for openAiApi (OpenAI API Key) and googleSheetsOAuth2Api (Google OAuth single-service credentials, see https://docs.n8n.io/integrations/builtin/credentials/google/oauth-single-service/). Copy the sample Google Sheets file at https://docs.google.com/spreadsheets/d/18A4d7KYrk8-uEMbu7shoQe_UIzmbTLV1FMN43bjA7qc/edit?gid=0#gid=0 to your own Google Drive, then update the documentId in all Google Sheets tool nodes to your file's URL.

2. Configure AI Agent core parameters

   Open the AI Agent node and confirm the Options > System Message is: '=You are a helpful assistant.\nCurrent timestamp is {{ $now }}' (keep the leading equals sign so n8n parses it as an expression). Set the OpenAI Chat Model to gpt-4o with temperature 0.2. Connect Buffer Memory (short-term memory, default last 5 interactions) to the AI Agent's Memory socket.

   Execution prompt · Source text。AI Agent node → Options → System Message field

   Copy prompt

   =You are a helpful assistant.
   Current timestamp is {{ $now }}

3. Connect data retrieval tools

   Connect the following four tools to the AI Agent's Tools socket: 1) Get all transactions (Google Sheets Tool) — set manual description to 'Only use this as last resort, because it will pull all data at once.'; 2) Get transactions by status — use $fromAI('transaction_status', 'Transaction status can be Refund, Completed or Error', 'string') to filter the Status column; 3) Get transactions by product name — use $fromAI('product_name', 'The product name', 'string') to filter the Product column, with tool description listing Widget A/B/C/D; 4) Records by date — a Tool Workflow node calling the sub-workflow within the same workflow, using $fromAI for start_date, end_date, and status parameters. All tools use the $fromAI function to let the AI auto-fill parameters.

   Execution prompt · Source text。Records by date node → Description field

   Copy prompt

   Use this tool to get records filtered by date. You can also filter by status at the same time, if you want.

   Execution prompt · Source text。Records by date node → Workflow Inputs → start_date

   Copy prompt

   ={{ $fromAI("start_date", "Start date in format YYYY-MM-DD", "string") }}

   Execution prompt · Source text。Records by date node → Workflow Inputs → end_date

   Copy prompt

   ={{ $fromAI("end_date", "End date in format YYYY-MM-DD", "string") }}

   Execution prompt · Source text。Records by date node → Workflow Inputs → status

   Copy prompt

   ={{ $fromAI("status", "Status of the transaction. Can be Completed, Refund or Error. Leave empty if you don't need this now.", "string") }}

   Execution prompt · Source text。Get transactions by product name node → Tool Description

   Copy prompt

   Find transactions by product.
   Our products are:
   - Widget A
   - Widget B
   - Widget C
   - Widget D

   Execution prompt · Source text。Get transactions by product name node → Filters UI → lookupValue

   Copy prompt

   ={{ $fromAI("product_name", "The product name", "string") }}

   Execution prompt · Source text。Get transactions by status node → Tool Description

   Copy prompt

   Find transactions by status

   Execution prompt · Source text。Get transactions by status node → Filters UI → lookupValue

   Copy prompt

   ={{ $fromAI("transaction_status", "Transaction status can be Refund, Completed or Error", "string") }}

   Execution prompt · Source text。Get all transactions node → Tool Description

   Copy prompt

   Only use this as last resort, because it will pull all data at once.

4. Configure date-filter sub-workflow

   The sub-workflow is triggered by When Executed by Another Workflow, receiving start_date, end_date, and status inputs. The Google Sheets request node sends an HTTP request to the Google Visualization API: URL https://docs.google.com/spreadsheets/d/{your-sheet-id}/gviz/tq, query params sheet=Sheet1 and tq='SELECT * WHERE A >= DATE "{{ $json.start_date }}" AND A <= DATE "{{ $json.end_date }}"'. The Code node parses the JSONP response, extracts cols and rows, and formats them into an array of JSON objects (including date format conversion). The Filter by status node filters where $json.Status contains the status input from the trigger. The Aggregate node combines all results into a single item returned to the AI Agent.

   Execution prompt · Source text。Code node → JavaScript Code field

   Copy prompt

   // Ensure there's at least one input item.
   if (!items || items.length === 0) {
     throw new Error("No input items found.");
   }
   
   // Our input is expected to have a 'data' property containing the JSONP string.
   const input = items[0].json;
   
   if (!input.data) {
     throw new Error("Input JSON does not have a 'data' property.");
   }
   
   const rawData = input.data;
   
   // Use a regex to extract the JSON content from the Google Visualization JSONP response.
   const regex = /google\.visualization\.Query\.setResponse\((.*)\);?$/s;
   const match = rawData.match(regex);
   
   if (!match) {
     throw new Error("Input data does not match the expected Google Visualization JSONP format.");
   }
   
   const jsonString = match[1];
   
   // Parse the extracted JSON string.
   let parsed;
   try {
     parsed = JSON.parse(jsonString);
   } catch (error) {
     throw new Error("Failed to parse JSON: " + error.message);
   }
   
   // Verify that the parsed JSON has the expected 'table' structure with 'cols' and 'rows'.
   if (!parsed.table || !Array.isArray(parsed.table.cols) || !Array.isArray(parsed.table.rows)) {
     throw new Error("Parsed JSON does not have the expected 'table' structure with 'cols' and 'rows'.");
   }
   
   const cols = parsed.table.cols;
   const rows = parsed.table.rows;
   
   // Helper function to convert date string from "Date(YYYY,M,D)" to "YYYY-MM-DD"
   function formatDate(dateStr) {
     const match = dateStr.match(/^Date\((\d+),(\d+),(\d+)\)$/);
     if (!match) return dateStr;
     const year = parseInt(match[1], 10);
     const month = parseInt(match[2], 10) + 1; // JavaScript months are 0-indexed
     const day = parseInt(match[3], 10);
     // Format with leading zeros
     return `${year}-${String(month).padStart(2, '0')}-${String(day).padStart(2, '0')}`;
   }
   
   // Map each row into an object using the column labels as keys.
   const newItems = rows.map(row => {
     const obj = {};
     cols.forEach((col, index) => {
       let value = row.c && row.c[index] ? row.c[index].v : null;
       // If the column type is "date" and the value is a string that looks like "Date(YYYY,M,D)"
       if (col.type === "date" && typeof value === "string") {
         value = formatDate(value);
       }
       obj[col.label] = value;
     });
     return { json: obj };
   });
   
   // Return the new array of items.
   return newItems;

5. Attach Calculator tool and test

   Connect the Calculator tool to the AI Agent's Tools socket so the Agent can perform mathematical calculations on retrieved data. Save and activate the workflow. In the Chat Trigger test panel, try asking: 'How many refunds in January and what was the amount refunded?' or 'How many successful sales did we have in January 2025 and what was the final income of those?' Observe whether the AI correctly invokes tools, filters data, and computes results.

You are an n8n workflow setup assistant. The user wants to reproduce the 'AI Data Analyst Chatbot' template. Assist in this order:
1. Confirm the user has an n8n instance, an OpenAI API Key, and Google Sheets OAuth2 credentials.
2. Guide them to import workflow.json and update the Google Sheets document URL.
3. Check the AI Agent's System Message, OpenAI Chat Model settings (gpt-4o, temperature 0.2), and Buffer Memory connection.
4. Verify the four data retrieval tools (Get all transactions / by status / by product name / Records by date) and Calculator are correctly connected.
5. Explain the sub-workflow input/output logic, and note that switching to a real database can simplify the HTTP Request + Code nodes into a direct SQL query.
Do not perform any API calls or credential applications on behalf of the user.