Reporting that assembles itself: n8n, Apps Script and the end of the Monday spreadsheet
Somewhere in every marketing team there is a person whose Monday morning is spent exporting CSVs from Google Ads, Meta and the CRMCRM Customer relationship management software, such as HubSpot or Salesforce, where leads, contacts and deals are recorded. Usually the only system that knows whether a lead was real., pasting them into a sheet, fixing the columns that moved, and screenshotting the result into Slack.
It takes two hours if nothing breaks. It is also the single most automatable job in the department, and the reason it is still manual is that nobody owns the plumbing.
Here is the architecture I build for clients, what each layer is for, and the limits that will bite you if you ignore them.
Three layers, three tools
Layer 1: pull. Something has to call the ad platform and CRM APIs on a schedule and land the raw rows somewhere. For this I use n8nn8n An open-source automation tool for connecting apps and moving data between them, similar to Zapier but self-hostable and cheaper at volume..
It has native nodes for Google Ads, Meta, HubSpot and most CRMs, it runs on a cron, and it handles pagination and retries so you do not write that code yourself (n8n documentation). Self-hosted, it costs the price of a small server. The output is one row per campaign per day in a Google Sheet or a Postgres table.
Layer 2: shape. Raw platform rows are not a report. They need currency conversionConversion The action you want someone to take after seeing an ad: a purchase, a form fill, a booked call. Each platform counts conversions by its own rules, which is why two dashboards rarely agree., campaign names parsed into their parts, spend joined to revenue from the CRM, and the “which number do we trust” rules applied.
For clients who live in Google Sheets, Google Apps ScriptApps Script Google's built-in scripting language for automating Google Sheets, Gmail and the rest of Google Workspace. does this well and costs nothing. It is a full JavaScript runtime attached to the sheet, it can call APIs directly, and it runs on time-based triggers.
Layer 3: show. A Looker Studio dashboard, or a lightweight web app, reads the shaped table. Nobody edits anything here. If a number is wrong, the fix goes into layer 2, not into the chart.
The discipline that makes this work: data flows one way. Sheets people edit are not sheets the automation writes to.
The limits to design around
Apps Script is free, which is why it is worth knowing where it stops. A single execution is capped at 6 minutes.
Time-based triggers get 90 minutes of total runtime per day on a consumer account and 6 hours on Google Workspace. URL fetch calls are capped at 20,000 a day on consumer accounts and 100,000 on Workspace (Google, Apps Script quotas).
Those numbers are fine for shaping a few thousand rows a day. They are not fine for pulling ad-level data for a large account, which is why the pull layer lives in n8n, where a job can run for an hour if it needs to. Put the heavy lifting where the limits are generous and the light lifting where the tool is free.
Two more rules learned the hard way:
- Pin API versions. Meta’s Marketing API and the Google Ads API both retire versions on a schedule, and the day they do, your Monday report is blank. Write the version into the code and put the retirement date in the calendar.
- Store the raw pull, not just the shaped result. When someone asks why March looks different from what Ads ManagerAds Manager Meta's interface for building campaigns and reading their results. Its numbers reflect Meta's attribution rules, not your bank account. shows today, you want the March rows as they were pulled in March. Platforms restate history.
What the report should contain
Less than you think. Spend, attributed revenue, cost per qualified leadQualified lead A lead that meets your sales team's definition of real: right kind of company, a budget, a working phone number. Different from a form fill, which anyone (or any bot) can produce. or customer, and the same three numbers for the previous period, by campaign. One line of plain-language commentary per campaign that moved. If the report needs a legend, it is too complicated to be read on a phone on Monday morning, and it will not be.
Where to start
Do not automate the whole thing in one go. Automate the pull for one platform into one sheet and leave the rest manual for a month.
When that pull has run for thirty days without anyone touching it, add the next platform. The failure mode is a beautiful end-to-end pipeline nobody trusts because it has never been boring for long enough.