How to refresh a GA4 report in Google Sheets automatically
By Jake Marsh · Updated
To refresh a GA4 report in Google Sheets automatically, save it as a Trafficledger report, open the Schedule pane, tick Refresh all reports automatically, choose How often (every hour, day, week or month) and the hour, and click Save schedule. Every saved report then reruns on that schedule and rewrites its tab, whether or not anyone has the spreadsheet open.
Steps
- Open Trafficledger from Extensions, Trafficledger, Open Trafficledger.
- Save at least one report. Build it under Build and click Save report. Use a rolling date preset such as Last 28 days or Last month, so each refresh moves the dates forward on its own.
- Open Schedule and tick Refresh all reports automatically.
- Pick How often. For Every day, choose the hour under After. For a weekly schedule, choose the day of the week under On; for a monthly one, the Day of month (1 to 28).
- Click Save schedule. If Google asks, allow Trafficledger to run while you're away. That's what lets it refresh with the spreadsheet closed.
- Check Last scheduled run later. It shows when the schedule last ran and what happened, and each report's Last Run and Status rows in the Trafficledger Reports sheet say how many rows it wrote.
Times are in the spreadsheet's time zone, set in Google Sheets under File, Settings.
Picking dates that roll forward
A schedule reruns the same report, so the dates decide what you get each time.
| You want | Set Dates to |
|---|---|
| Yesterday's numbers every morning | Yesterday, refreshed every day |
| A rolling four weeks | Last 28 days |
| Last week, Monday to Sunday, every Monday | Last week (Mon to Sun), weekly on Monday |
| Last month on the 1st | Last month, monthly on day 1 |
| A fixed custom range | Type the start and end. They stay the same every run |
In the Trafficledger Reports sheet, Start Date and End Date also take a formula, such as =EOMONTH(TODAY(),-1) for the last day of last month.
Running only some reports on the schedule
The schedule runs every saved report. To leave one out, set its Skip Report row to TRUE. A formula works too, for example to skip a campaign report after the campaign has ended. Skipped reports still run when you click Run next to them.
When a run doesn't finish
Apps Script gives each run a few minutes. A spreadsheet with many large reports may not finish in one go; the reports that didn't run are kept and the next hourly check carries on with them. If Google Analytics reports that the property has used most of its hourly allowance, the scheduled run waits for the next hour instead of failing halfway.
What it costs
Trafficledger is $55 a month or $528 a year per Google account. Free trial: 30 uses or 14 days, whichever comes first. No card needed. Every plan and the install link are on the Trafficledger page.
Trafficledger: GA4 Reports in Sheets
Google Analytics 4 reports in Google Sheets: saved reports, comparisons and scheduled refresh $55 a month or $528 a year per Google account.
Questions
Who does the schedule run as?
The person who saved it. Only that person can change it; anyone else who tries is told whose schedule it is and asked to have them turn it off first.
Can it refresh more often than every hour?
Not on a schedule. Google lets editor add-ons run a timed trigger at most once an hour. For minute-by-minute numbers, use a Realtime report with Refresh every minute while this sidebar is open.
Does the schedule use up my trial?
During the free trial, a day of scheduled refreshes counts as one use. Paid plans aren't metered.
How do I stop it?
Untick Refresh all reports automatically and save, or click Turn off the schedule and clear saved settings at the bottom of the Schedule pane.
More Trafficledger guides
- How to move GA4 Reports Builder reports to Trafficledger
- How to build a monthly GA4 report in Google Sheets
More Google guides
- How to compare two Google Sheets for differences (Tabmeld)
- How to make a copy of a template tab for each name in a list (Tabherd)
- How to email each person only their rows from a Google Sheet (Scheduled Sheet Reports)