Every Monday morning you export tables one by one from GA4, paste them into the weekly report doc, and half an hour is gone; when someone suddenly asks for a different metric breakdown, you redo the whole thing. Hook the GA4 Data API into Google Sheets and this repetitive chore can be handed entirely to a machine.
The way to automate the report is to connect the GA4 Data API with a service account, write a data-pulling function in Apps Script inside Sheets, fix it to pull four sets of metrics — sessions, engagement rate, key events, and top landing pages — then attach a scheduled trigger for Monday morning. The weekly report goes from “manually exported” to “open and it’s there”, and you just add one sentence of conclusion.
- Enable the API and create a service account: Enable Google Analytics Data API in the Cloud console, create a service account and download the JSON key, and add the service account email as a GA4 viewer.
- Get the first pull working: Add the Analytics Data API advanced service in Apps Script, fill in the property ID and date range, run runReport, and write the results into a hidden raw sheet.
- Fix the four metric groups: Track weekly sessions, engagement rate, key events, and top 5 landing pages, set the dateRange to the last 7 days, and also pull the prior 7 days for week-over-week comparison.
- Attach the scheduled trigger: A weekly timer at 07:00 on Monday auto-refreshes; enable failure email notifications, clear the raw target area before writing, and log a timestamp row on every run.
- Add conditional formatting and alerts: Add a color scale to the session column, mark cells red where engagement rate is below 40%, and use a formula in the top alert cell to show “needs review” when weekly sessions drop more than 15% WoW.
Enable GA4 Data API and the service account
In the Google Cloud console, create a project or pick an existing one, go to “APIs & Services → Library”, search for Google Analytics Data API and enable it. Back under “Credentials → Create credentials → Service account”, give it a name you’ll recognize, then generate and download a JSON key from the “Keys” page.
Once you have the service account email, go to GA4 → Admin → Property access management and add that email as a “Viewer”. Give view-only access, never edit access — no matter how the script is written, it can’t touch your conversion configuration.
- Enable Google Analytics Data API in the Cloud project
- Create a service account and download the JSON key file
- Add that email as a viewer in the GA4 property
- Note the property ID — a string of digits under “Admin → Property details”
- Store the key file somewhere only you can access
Get the first pull working in Sheets
Open the target spreadsheet, click “Extensions → Apps Script” to enter the editor. Under “Services” on the left, add the Google Analytics Data API advanced service and you can call runReport directly, skipping the hassle of assembling your own OAuth flow. Fill in the property ID, date range, dimensions, and metrics in the function, run it once, and check the execution log for returned row data.
Don’t trip on permissions: don’t hardcode the key into the script and then share the spreadsheet with colleagues — that hands out your GA4 data access along with it. Store the JSON in script properties via PropertiesService, or let each person authorize with their own account, keeping permissions tied to people.
Once the first pull works, write the results into a hidden sheet named raw, and have the dashboard page only reference cells in raw. Separating the data layer from the display layer means future layout changes won’t break the data-pulling logic.
Which four metric groups the weekly report should track
The worst thing a weekly report can do is keep adding metrics until nobody reads any of them. Four groups are enough: weekly sessions for the size of the pie, engagement rate for whether content holds people, key events for real conversions, and top 5 landing pages for where the volume comes from. Set the dateRange to the last 7 days and also pull the prior 7 days for comparison. To split by channel, add the sessionDefaultChannelGroup dimension and see at a glance whether search or social is contributing.
| Metric | API field | What it shows | Signal to watch for |
|---|---|---|---|
| Weekly sessions | sessions | Overall trend | Down more than 15% WoW |
| Engagement rate | engagementRate | Content quality | Below 40% |
| Key events | keyEvents | Conversion results | Falling two weeks in a row |
| Top landing pages | pagePath | Traffic source distribution | One page above 50% |
Metrics beyond these four go into the raw sheet first, off the dashboard. When one gets asked about at the weekly meeting for two weeks running, then promote it. To align with finer metric definitions, cross-check against the SEO KPI dashboard metric list.
Let the report refresh itself with a scheduled trigger
In Apps Script, click “Triggers → Add trigger” on the left, select your main pulling function, set the event source to “Time-driven”, the type to a weekly timer, and the time to Monday 07:00. Since the weekly meeting is usually in the morning, the report lands early and you still have time to read it and add your judgment.
- The timezone follows your Google account — confirm it in project settings first so you don’t run an empty table at midnight Monday
- Set failure notifications to “notify me immediately”, so you get an email the moment it fails rather than discovering an empty sheet at Monday’s meeting
- Clear the raw sheet’s target area before writing, so leftover last-week data doesn’t linger when this week has fewer rows
- Log a timestamp row on every run so troubleshooting can directly show which week didn’t run
The metric definitions themselves also need an owner. If key events aren’t configured clearly yet, sort out GA4 key events first before talking automation — otherwise the auto-pulled numbers are wrong too.
Add conditional formatting and anomaly alerts
Numbers piled in a sheet get read by nobody — make the sheet speak for itself. Add a color scale to the session column and mark cells red where engagement rate is below 40%; leave an alert cell at the top with a formula that checks whether weekly sessions dropped more than 15% WoW, showing “needs review” when it did.
When an alert fires, don’t stop at “found a decline”. Drill down three layers — traffic source, landing page, device — to locate whether the whole site dropped or a few posts did. For ranking-type fluctuations, cross-reference the distribution hidden by average positions in rank tracking; averages often mask the loss of head keywords.
Three easy-to-miss troubleshooting points
- Data latency: some GA4 dimensions have 24–48 hours of latency; pulling last week’s data on Monday morning is basically safe, but pulling “yesterday” can come in low
- Quota limits: requests with too many dimension combinations easily hit quotas; split one big request into two or three smaller ones for stability
- Parameters in paths: utm parameters in pagePath split the same page into several rows; strip parameters and merge before sorting
When it comes to execution, the rhythm is simple when you pace it by day: tonight, enable the Data API in Google Cloud, create the service account, and add the email as a GA4 viewer; tomorrow, run runReport once in Sheets and confirm all four metric groups return row data; then write results into the raw sheet with the dashboard page only referencing it, fully separating the data layer from the display layer; then attach the Monday 07:00 time trigger and turn on failure email notifications; finally, set up the color scales and the WoW alert formula. By next Monday you open the spreadsheet and read conclusions directly, no more manual export and paste — that half-hour of repetitive work, once built, is saved forever.
