How I Automated GA4 Data into Google Sheets (And Reclaimed My Mondays)

The "Export CSV" Purgatory

For the first three years of my agency life, my Monday mornings looked exactly the same.

1. Open GA4.
2. Select client A.
3. Set date range to "Last Week."
4. Click "Share" -> "Download File" -> "Download CSV."
5. Open CSV.
6. Copy rows.
7. Paste into "Master Reporting Sheet."
8. Repeat for 12 clients.

It was mind-numbing. It was prone to error (I once pasted Client A's data into Client B's report—that was a fun email to write). And worst of all, it wasn't scalable.

When I signed my 13th client, I snapped. "I am a digital marketer," I told myself. "I should be able to automate this."

The Exact Reporting Pain

Aside from the manual labor, the native exports were just... bad.

• Row Limits: GA4 exports are capped. If looking at a "Page Path" report for a large site, the export cuts off after 5,000 rows. The "long tail" SEO data just vanishes.
• Date Ranges: I could export "Last 30 Days," but if I wanted to compare it to "Previous Period" with specific custom groupings, I had to export two files and VLOOKUP them together.
• Sampling: Again, the heavy sampling in the UI exports meant I was reporting on trends, not facts.

My Automation Blueprint

I decided to build a system where the data lived in Google Sheets automatically, updating every single morning at 5 AM.

1. The Architecture

I structured my Google Sheet with three distinct types of tabs. This "Three-Layer" approach is crucial for organization.

Layer 1: The "Raw_Data" Tabs
These tabs are ugly. Nobody sees them but me. They are purely for ingesting the API data. Columns are named date, eventReference, activeUsers. I never touch these cells manually.

Layer 2: The "Staging" Tabs
Here is where the magic happens. I use QUERY() and ARRAYFORMULA() functions to clean the raw data.
Example: Converting /blog/post-1?utm_source=fb into just /blog/post-1 to consolidate pageviews.

Layer 3: The "Client_View" Tabs
These are the pretty tables with conditional formatting, sparklines, and big bold headers. This is what the client actually sees (or what feeds Looker Studio).

2. The Tooling

I tried writing my own Apps Script (free, but high maintenance). I eventually settled on using a connector add-on (like Mixed Analytics or Supermetrics). For the cost of a few coffees, it handled the authentication and quota management.

Note: You can use the GA4 Magic Reports add-on or similar free tools for basics.

What Broke (And How I Fixed It)

1. The "Date" Format Crisis

GA4 API returns dates as YYYYMMDD (string). Google Sheets treats this as a number. My charts broke immediately because "20240101" is not a date to a spreadsheet.

Fix: I added a helper column in my Staging tab: =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)). Now it’s a usable date object.

2. The Quota limit

GA4 introduced aggressive API quotas in late 2022. My sheets started returning "Resource Exhausted" errors because I was trying to update 20 tabs simultaneously.

Fix: I spaced out the triggers. Client A updates at 4:00 AM. Client B at 4:15 AM. Client C at 4:30 AM. It’s a simple fix, but it saved the system.

3. "NULL" isn't Zero

When a campaign has no conversions, the API often returns a blank or null, not a 0. This breaks calculated fields like "CPA" (dividing by null = error).

Fix: I wrapped all my raw imports in IFERROR() or specialized cleaning formulas to force blanks to become 0.

The Business Value

This wasn't just about saving me 2 hours on Mondays.

• Velocity: I could spot a drop in conversion rate on Tuesday morning, not Monday of the next week.
• Granularity: I could archive the data. GA4 has data retention limits (2 months or 14 months). My Google Sheets have data fro 3 years ago. I own my history now.
• Agility: When a client asks, "Can we see performance by Day of Week?" I just add a pivot table in Sheets. In GA4, that's a custom report project.

Automation isn't just about laziness; it's about ownership. When your data lives in your sheets, you control the narrative.