On
n8n tutorial - Lesson 11: Build a Weekly Report Workflow in n8n

Hi everyone, in this post you will build a complete n8n scheduled workflow that automatically generates a weekly spending report — pulling data from Google Sheets, summarizing it with AI, writing the output to a Google Doc, and sending a Telegram notification. This is the Week 3 capstone project in our n8n Workflow Automation Tutorial series, so by the end you will have a real end-to-end pipeline you can activate on a schedule.

How to do:

Step 1 — Create the Google Doc Report Template

Before building any nodes, you need a Doc template with placeholders that n8n will fill in later.
  1. Create a new Google Doc and name it T3-B4-Weekly-Report-Template.
  2. Add the following 9 placeholders exactly as shown — these are the strings the Docs node will replace:
    • {{week_number}}
    • {{period}}
    • {{total_spending}}
    • {{num_transactions}}
    • {{top_vendor}}
    • {{top_category}}
    • {{transactions_list}}
    • {{transactions_raw}}
    • {{ai_insight}}
  3. Apply formatting: use Heading 1 for the report title and Heading 2 for section headers. Bold any labels you want to stand out.
  4. Note the Doc ID from the URL — you will need it in Step 5. The template used in this tutorial has ID 11kboueFh3VkSIMPXiSbYisfc04SrhUAoZvIdcEq7vNc.

Step 2 — Create the Reports Output Folder in Google Drive

Each time the workflow runs it copies the template into a dedicated folder, keeping your Drive organized.
  1. Open Google Drive and create a new folder named Reports/.
  2. Note the folder ID from the URL — this tutorial uses folder ID 1iJZ1381pvCCSZsZGWWkzUe5I3ncUbzr4.

Step 3 — Configure the Schedule Trigger Node

The Schedule Trigger is the entry point of this n8n scheduled workflow — it fires automatically every Friday at 5 PM.
  1. Open your n8n canvas and add a Schedule Trigger node.
  2. Set Trigger Interval to Weeks.
  3. Set Day of Week to Friday and Hour to 17 (5 PM).
  4. Save the node — no other configuration is needed here.

Note — While developing, you can trigger the workflow manually using the Test Workflow button. Only switch to a live schedule after all downstream nodes are verified.

Step 4 — Pull Spending Data from Google Sheets

The Google Sheets node fetches all rows from your spending sheet; filtering happens in the next node so you keep the logic centralized.
  1. Add a Google Sheets node connected to the Schedule Trigger.
  2. Set Operation to Get Rows.
  3. Select your spending spreadsheet and the correct sheet tab.
  4. Leave the filter empty — do not add a row filter here. The Code node in the next step handles filtering so you have one place to toggle between development mode (all rows) and production mode (last 7 days only).

Production tip — Before you activate the workflow for real weekly runs, go into the Code node and uncomment the two lines that filter rows to the last 7 days. If you forget, every report will include your full transaction history instead of just that week's data.

Step 5 — Aggregate Metrics in the Code Node

The Code node runs JavaScript once across all items to compute 8 summary fields from the raw sheet rows.
  1. Add a Code node and set Mode to Run Once for All Items.
  2. Write JavaScript that computes and returns an object with these 8 fields:
    • week_number — ISO week number of the report period
    • period — human-readable date range string (e.g., "May 4–10, 2026")
    • total_spending — sum of all transaction amounts
    • num_transactions — count of rows
    • top_vendor — vendor with highest total spend
    • top_category — category with highest total spend
    • transactions_list — formatted string summary for the Doc
    • transactions_raw — raw transaction data for the AI prompt
  3. For production mode, uncomment the filter logic inside the Code node:
    • Calculate the date 7 days before today.
    • Filter items to only rows where the transaction date is within that window.
  4. Critical: Cast week_number and num_transactions to strings before returning — wrap them with String(...). The Google Docs API requires replace_text values to be TYPE_STRING; passing a JavaScript number causes a type error.

Tip — Test this node with your sheet data before moving on. During testing with 4 sample rows the node returned correct aggregates — confirming the Run Once for All Items mode is essential here, because Run Once per Item would re-run the aggregation separately for each row and produce wrong totals.

Step 6 — Generate AI Insights with the Basic LLM Chain Node

The Basic LLM Chain node sends the aggregated metrics to Claude and gets back a short, actionable spending insight.
  1. Add a Basic LLM Chain node connected to the Code node.
  2. Set the model to Claude Haiku 4.5 and Temperature to 0.5.
  3. Do not attach an Output Parser — plain text output is all that is needed here.
  4. Write a prompt using an XML 4-block structure:
    • <role> — define the AI as a personal finance analyst
    • <data> — insert the aggregated metrics using n8n expressions (e.g., {{ $json.transactions_raw }})
    • <instructions> — ask for 4–5 sentences with at least one actionable suggestion
    • <format> — specify plain text, no markdown, no bullet points
  5. Test the node — you should get a 4–5 sentence insight that includes a concrete recommendation.

Note — Temperature 0.5 balances creativity and consistency. Lower values (near 0) make the insight more mechanical; higher values (near 1) can make it too speculative for a finance report.

Step 7 — Copy the Template in Google Drive

The Google Drive node duplicates the template Doc into the Reports/ folder so the original template is never modified.
  1. Add a Google Drive node and set Operation to Copy.
  2. Set File ID to the template Doc ID: 11kboueFh3VkSIMPXiSbYisfc04SrhUAoZvIdcEq7vNc.
  3. Set Destination Folder ID to the Reports folder ID: 1iJZ1381pvCCSZsZGWWkzUe5I3ncUbzr4.
  4. Optionally set New Name using an expression that includes the week number, e.g., Weekly-Report-Week-{{ $('Code').item.json.week_number }}.
  5. The node outputs the new Doc's ID — the next node uses this to write content into the copy.

Step 8 — Replace Placeholders in the Google Doc

The Google Docs node performs 9 Find & Replace operations to swap every placeholder with real data.
  1. Add a Google Docs node and set Operation to Update.
  2. Set Document ID to the output ID from the Drive Copy node: {{ $json.id }}.
  3. Add 9 Action cards, one per placeholder, each set to Find & Replace:
    • Find: {{week_number}} → Replace with: {{ $('Code').item.json.week_number }}
    • Find: {{period}} → Replace with: {{ $('Code').item.json.period }}
    • Find: {{total_spending}} → Replace with: {{ $('Code').item.json.total_spending }}
    • Find: {{num_transactions}} → Replace with: {{ $('Code').item.json.num_transactions }}
    • Find: {{top_vendor}} → Replace with: {{ $('Code').item.json.top_vendor }}
    • Find: {{top_category}} → Replace with: {{ $('Code').item.json.top_category }}
    • Find: {{transactions_list}} → Replace with: {{ $('Code').item.json.transactions_list }}
    • Find: {{transactions_raw}} → Replace with: {{ $('Code').item.json.transactions_raw }}
    • Find: {{ai_insight}} → Replace with: {{ $('Basic LLM Chain').item.json.text }}

Tip — If the Docs node throws a TYPE_STRING error on any card, check whether the value coming from the Code node is a JavaScript number. The fix is to wrap the value in String(...) inside the Code node (the cleaner approach), or to wrap the expression in the Action card itself using {{ String($('Code').item.json.week_number) }}.

Step 9 — Send a Telegram Notification

The final node sends a Telegram message with a bold summary and a clickable link to the newly generated report Doc.
  1. Add a Telegram node and set Operation to Send Message.
  2. Set Chat ID to your personal or group chat ID.
  3. Set Parse Mode to Markdown.
  4. Write the message text using Markdown bold and a hyperlink:
    • Use *text* for bold fields (e.g., *Total: {{ $('Code').item.json.total_spending }}*).
    • Use [Open Report](https://docs.google.com/document/d/{{ $('Google Drive').item.json.id }}) for the clickable Doc link.
  5. Test the node — verify the message displays correctly in Telegram and the link opens the Doc.

Note — Always click the link in Telegram during testing to confirm the Doc ID is correct and the document is accessible with your Google account permissions. This end-to-end verification catches permission issues before you go live.

Step 10 — Activate the Workflow for Production

With all 6 nodes tested individually, run one final end-to-end test and then activate the schedule.
  1. Click Test Workflow from the Schedule Trigger node and verify:
    • A new Doc appears in the Reports/ folder with correct data.
    • The Telegram message arrives with a working link.
    • The AI insight is 4–5 sentences and includes an actionable suggestion.
  2. Open the Code node and uncomment the two lines that filter rows to the last 7 days — this switches from development mode (all rows) to production mode (current week only).
  3. Toggle the workflow to Active using the switch in the top-right corner of the canvas.

Production tip — Do not activate before switching the production filter in the Code node. Without the date filter every weekly run will include your entire transaction history, making totals meaningless over time.

Key Lessons from This Session

  1. Always cast numbers to strings before passing to external APIs with strict typing. The Google Docs API rejects replace_text values that are JavaScript numbers — wrap them with String(...) in your Code node as the cleanest fix.
  2. Use "Run Once for All Items" in the Code node for aggregation. "Run Once per Item" re-runs the function per row and produces incorrect totals; "Run Once for All Items" gives you the full array to aggregate in one pass.
  3. Keep filtering logic in one place. By leaving the Sheets node filter-free and handling date filtering inside the Code node, you have a single toggle point for dev vs. production mode.
  4. Separate numeric computation from text generation. n8n's Code node handles all numeric aggregation; the LLM handles all natural-language output. Mixing the two in a single prompt makes debugging harder.
  5. Always verify the full end-to-end path manually before activating a schedule. Click every output link, open every generated file, and read the AI insight — automated schedules surface no UI errors when they silently fail.
  6. n8n nodes are stateless; Google Sheets is stateful. Each workflow run starts fresh with no memory of previous runs. Your Sheet is the persistent data layer — treat it as the source of truth.

Conclusion:

In this n8n tutorial you built a full 6-node n8n scheduled workflow — from a Friday-evening Schedule Trigger through Sheets data retrieval, JavaScript aggregation, AI insight generation, Google Doc creation, and a Telegram notification — covering every real configuration value, a live bug fix, and the production filter toggle you need before going live. This is the Week 3 capstone in the n8n workflow automation series; next week we move into automating Blogger posts, where you will use HTTP Request nodes to call the Blogger API and chain Claude prompts to write full SEO-ready articles from a keyword list in a Sheet.

If you have any questions, feel free to leave a comment below. Thank you!

Tags: n8n scheduled workflow, n8n tutorial, n8n workflow automation, Google Sheets automation, Google Docs automation, Telegram bot n8n, AI workflow automation, n8n beginner to advanced

Maybe you are interested!