From practice 6 min read

How to build an automated sales and cost report in n8n and Google Sheets

I'll show the steps that make up a workflow which regularly pulls data from several sources and writes it into one table, and what to watch out for when writing to Google Sheets.

How to build an automated sales and cost report in n8n and Google Sheets
Author: Maksym Kamenchuk, Nexflow — Banská Bystrica Published Updated

A report in a single table is built in n8n from five parts: Schedule Trigger starts the workflow at a fixed time, HTTP Request or an app-specific node pulls the data, Merge combines it, Aggregate or a Code node groups the items, and Google Sheets writes the result into a row.

A workflow with a Schedule Trigger must be saved and published. The table is then filled on the set schedule, with no manual copying of exports.

What such a report should contain

For a small company, I recommend starting with a small number of columns. One row is one period (a day or a week), and the columns are order revenue, number of orders and costs that you have somewhere in electronic form, for example in a spreadsheet or in a system that offers an API.

It is important to decide in advance which sources the data will come from. Every additional source means more login credentials and another step that can break.

The trigger: how the workflow runs by itself

Schedule Trigger runs the workflow at fixed intervals and times, similar to the Cron tool in Unix-type systems. You choose the interval in the Trigger Interval parameter: seconds, minutes, hours, days, weeks, months or a custom Cron expression. The other parameters depend on the chosen interval.

A workflow with a Schedule Trigger must be saved and published. The n8n documentation states this as a requirement, so check it before the first scheduled run.

Getting data from the e-shop and other sources

The HTTP Request node is one of the most versatile in n8n. It can query any application or service that has a REST API (an interface through which programs exchange data).

I wrote about connecting e-shops in more detail in the articles Shoptet and n8n and E-shop automation with n8n.

Combining data: Merge, Aggregate and Code

The Merge node is used to combine data from different sources into one. It can also combine data from two previous nodes even if the workflow has not been split into separate branches, which is useful when you need a single dataset from the outputs of several nodes.

More inputs into one Merge are available only in Append mode, which collects all items. Merge waits for all inputs and only then sends them on. The output may still need adjusting, for example with an Aggregate node.

NodeWhat to use it for in a report
Mergecombines data from several sources into one dataset
Aggregategroups separate items (or parts of them) into individual items
Codemore complex logic that an expression can't handle, e.g. aggregating items or changing the structure of arrays and objects

Writing to Google Sheets

The Append or Update Row operation is used for writing. If it finds a matching row, it updates it; if not, it adds a new one at the end of the data.

Step-by-step from scratch

  1. Prepare a table with a header: period, revenue, number of orders, costs.
  2. Add a Schedule Trigger and set the interval, for example days.
  3. Add an HTTP Request or an app node for each data source.
  4. Combine the outputs with Merge. Connecting more inputs to one Merge is only possible in Append mode. Adjust the output as needed with an Aggregate or Code node.
  5. Name the fields exactly like the columns and write them with Google Sheets, Append or Update Row.
  6. Save and publish the workflow, then check the first automatic run.

When it isn't worth it

If you need the report once a quarter and it has a single source, I recommend a manual export to a spreadsheet. Automation makes sense for regular repetition and several sources.

If you don't have costs in any machine-readable form, the workflow has nowhere to get them from and the report would be only half complete. First you need to sort out where the data is recorded. If the structure of your data in the sources changes often, maintaining the workflow can, in my opinion, be more demanding than copying by hand. If it isn't clear, also see what to automate in a company first.

Frequently asked questions

Can n8n send data to Google Sheets automatically every day?

Yes. Schedule Trigger can run the workflow at a daily interval or at a time set by a Cron expression. The workflow must be saved and published.

What permission should I give the API key in WooCommerce?

The Read level only allows retrieving data from the shop, while Write and Read/Write also allow changes. For a report I recommend giving the minimum the workflow needs, which is Read.

Why are new rows written below an empty space in the table?

n8n adds data after the last used row and does not fill gaps. If you have manual notes or leftovers below the data, new rows will go after them.

How do I combine data from several sources into one row?

Use the Merge node, which combines data from several sources. More inputs are available only in Append mode, and the result can be adjusted with an Aggregate or Code node.

Can I also connect an application that has no node of its own in n8n?

If it has a REST API, it can be queried through the HTTP Request node. You need access credentials for it according to that application's documentation.

Sources

Related services

Servicen8n automation ServiceE-commerce automation

Related articles

E-commerceE-shop Automation with n8n: WooCommerce Webhooks E-commerceShoptet and n8n: API and Webhook Integration GuideWhat to automate in a company first

Want an overview in one table?

Tell me where your sales, orders and costs come from. We'll discuss whether a regular report is worth automating and what it would involve. My work starts at €390, details are in the article on pricing.

I want a free consultation →