Digital Analytics Expert
HTML, CSS & JS Lover
Apps Script Developer
Problem-Solving Oriented
GA4 Magic Reports is a Google Sheets add-on that helps you to view and edit Google Analytics 4 data more easily in Google Sheets.
Install the Google Sheets add-on
To use the GA4 Magic Reports add-on, you must first add it to your Spreadsheet.
You can go directly to the add-on in the Google Workspace Marketplace and install the add-on: https://workspace.google.com/marketplace/app/ga4_magic_reports/6532356327
The add-on is then available in every Google Spreadsheet under “Extensions” menu.
Create GA4 reports
Select “Extensions” > “GA4 Magic Reports” > “Create new report” in the menu bar. This will display a sidebar on the right side with which you can easily create a report configuration selecting:
- Google Analytics 4 account and property among those linked to your account;
- Metics and Dimensions;
- Metric filters;
- Dimension filters;
- Sorting information.
Then, click on the button “Create Report”, your report configuration will appear in the sheet called “GA4 Reports Configuration” (if this sheet does not already exist, it will be created).
You can go through these steps again to add another report or copy the information in the sheet to a new column and edit it in the sheet.
Not all cells are filled by the sidebar. The fields that are not filled by the sidebar can be entered in the sheet.
Run reports
Go to “Extensions” > “GA4 Magic Reports” > “Run reports” in the menu to run all the reports you have created.
Schedule reports to run automatically
Reports can be scheduled to run automatically. To do this, go to “Extensions” > “GA4 Magic Reports” > “Schedule reports”.
A dialog will open in which you can indicate three things:
- Turning a schedule on and off.
- Choose the frequency, day and time.
The schedule uses the time zone of the spreadsheet.
Quotas
This Google Sheets add-on uses the Google Analytics data API. The use of this API is limited by Google. Here you can see which quotas apply: https://developers.google.com/analytics/devguides/reporting/data/v1/quotas
Report configuration options
Each field in the configuration file contains a note explaining what that feature is and how it can be used.
Terms
You can use this Google Sheets add-on for free. For more information please visit our terms and conditions and privacy policy.
Hi there
Thank you for your work
May I suggest an improvement?
As a date range to have the possibility to select “last 30 days” to “yesterday”
Thanks
Hi Claudiu,
thanks for the suggestion!
Now the format NdaysAgo, yesterday or today is also accepted.
Currently you can enter it manually in the date fields. Soon I will implement the possibility to select these values from the sidebar.
Thanks!
Hello, please help.
I am trying to create a report from 01/01/2023 till yesterday
and it’s doesn’t works. A error msg on screen. If i use a real yesterday date or today for ex. 02/27/2023 – it’s works. How i can set it up ?
Thanks.
Hi Artem,
thanks for the report 🙂
I analyzed the code and indeed the mixed date format was not an expected condition, so it was generating an error.
I have now adjusted the code and it works properly.
Thank you!
Hi Michele.
Great product.
I have trouble sorting the output by date. I select the vies, sessions and users as my metrics and date as a dimension. Also i add sort by date descending by i get this JSON error.
What am i doing wrong? Here are some screenshots:
https://imgsh.net/a/X3lJdtE.png
https://imgsh.net/a/aSh99U7.png
Hi Marko,
thanks for the report.
I fixed the problem a few days ago, let me know if everything works fine on your side too 🙂 Thanks!
Working fine,
But I’ve also noticed even if i set the “include empty rows” to TRUE, i still don’t get the values with 0.
Any advise?
Hi Marko,
the result that the add-on shows is exactly what Google provides. Google API right now is in alpha and beta version, I expect that with the next updates they will be improved, for example with rows to 0 as it was on Universal and as you need.
Ciao Michele, grazie per la condivisione!
Non è possibile utilizzare le custom dimension?
Ciao marco,
dovrebbero comparire nell’elenco delle dimensioni della Property selezionata con il nome che hai definito.
Fammi sapere se sei riuscito a trovarle 🙂
Purtroppo no, non le vedo tra le dimensioni! 🙁 Ho provato a inserire anche il customEvent:parametro_nome manualmente nella riga “dimensioni” ma ho errori nella generazione dei report…
Che tipo di errore ti viene restituito durante la generazione del report?
Love the tool! I am having problems using the OR function with the Dimension filters. I am getting a “No rows returned.” message instead of any data. I have tested and can run two dimension filters separately but not together with the OR function.
Hi Eric,
I also quote here the answer I gave you in Google Group.
I was able to replicate the error and I confirm that there was indeed a bug in the code.
Your report was really helpful!
Now the problem is fixed.
Thank you!
Hello,
On GA4 it’s possible, in the explorer, to apply segments. Could we add this field in the report of the add-on to improve it ?
Hi Maud,
creating segments inside the report query is currently not supported in the Google Analytics Data API, so it is necessary to use the UI.
Hi, Michele!
I appreciate your great work! It really saves me HUGE time.
I fount that the scheduled reports are generated in some European time. It would be an even better product if it followed the user’s own time zone, in the same way as the Google Analytics add-on.
Hi nao,
I have made a change to refer to the sheet settings.
Let me know if the schedule execution is now consistent with your timezone. Thanks!
Hi, Michele!
My scheduled reports now run exactly in accordance with my timezone. Fascinating! Thanks again!
Thank you for the feedback! 🙂
My date output is NOT formatted correctly. Instead of expected output of 2023-01-01 I’m getting 20230101 – is this a known issue or does someone have a workaround? I mean I can manually fix after import – but that could get painful.
Suggestions appreciated
Thanks
Hi Jim,
thank you for the report, I have checked and confirm that it is an expected behavior.
The result that the add-on shows is exactly what Google provides.
However, the date format of Google Analytics does not contain the “-“, the same result from GA4 is also found on UA.
Therefore, if the desired format of the date is different, it is necessary to manage it manually on the output.
I hope I took your doubt away 🙂
Thanks!
Hi Michele.
It’s a great add-on.
Can I set different [Schedule reports] dates for different files?
Currently, if you set a schedule in one file, the same settings are automatically displayed even if you open the schedule in another file.
(For example, I want to set like this)
Spreadsheet A: every month 1st 6 a.m. – 7 a.m.
Spreadsheet B: every week Thursday 8 a.m. – 9 a.m.
Thanks.
Hi Shin,
thank you for the report, I have checked and confirm that the issue should now be solved.
Let me know!
Hello Michel,
Thanks for this plugin!
I have a question about this plugin because I noticed a difference when importing the cost of my google ads campaigns in GA4 with the import on the spreadsheet and I would like to know where this could come from?
Hi Valentin,
if there is a discrepancy, it may be interesting to investigate, but it cannot be resolved by the add-on because the result that the add-on shows is exactly what Google API provides.
If there is an error in the API, I expect it to be fixed by Google with the next releases.
Hello! Is there a way to edit reports after they have been created? Thanks
Hi Kaitlin,
for the time being, I have not foreseen this functionality. Changes can be made manually in the configuration sheet.
I will however take the request into consideration because it may be something actually useful 🙂
Hi Michele
I’ve also noticed even if i set the “include empty rows” to TRUE, i still don’t get the values with 0.
Please advise
Hi Marko,
the result that the add-on shows is exactly what Google provides. Google API right now is in alpha and beta version, I expect that with the next updates they will be improved, for example with rows to 0 as it was on Universal and as you need.
Hi Michele!
Thank you so much for this awesome extension. I am running into problems with the Search Console data. I get the error “GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: The dimensions and metrics are incompatible.” I am setting the Metrics to “organicGoogleSearchAveragePosition, organicGoogleSearchClicks, organicGoogleSearchClickThroughRate, organicGoogleSearchImpressions”. Is the extension not compatible with GSC through GA4?
Hi Ellie,
you can check the compatibility between GA4 dimensions and metrics in the following official online tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
You can select them by clicking the checkbox next to their name. Incompatible dimensions & metrics will be grayed out.
Hi Michele,
Is that possible to use in one dimension filters several OR and AND and NOT__AND please ?
Hi Caroline, as the official API documentation currently says, we can specify one type of filter expression. The expr can be only one of: AND, OR, NOT: https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/FilterExpression?hl=en
I don’t exclude that with API updates from Google something could change in the future 🙂
Hi Michael,
At first, thank you so much for this add-on. It’s really valuable.
However i face an permission problem. If i run a report with ecommerce metrics, e.g. purchaseRevenue, I get this error: “GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: User does not have sufficient permissions for this property. To learn more about Property ID, see https://developers.google.com/analytics/devguides/reporting/data/v1/property-id.”
I’ve granted all the permissions when installing this add-on. How to deal with this?
Hi Willy,
that error is provided by Google answer so it is not recognizing your privileges. This can happen when there are multiple linked Google accounts on a single browser.
Try using a browser where you have connected only the account that contains the Analytics Properties of your interest.
Hi Michael,
Thanks for replying. I understand that’s not recognizing my privileges. However i use 1 account on a single browser. I deleted my cookies and used private modus and re-installed the add-on. Unfortunately this did’t work.
> How to proceed?
FYI
I can pull other metrics and dimensions via this add-on such as sessions, transactions, sessionCampaignName.
Hi Willy,
unfortunately it’s not a problem with the add-on, it’s the authentication process by Google that isn’t managed on the code side.
I don’t know if it can fix, anyway you could try uninstalling and reinstalling it by giving permissions again at install time. Let me know!
Hi Michael,
Thanks for checking. Hmm, i already tried that several times. I will test different things and if its fixed I let you know.
Cheers.
Thanks Willy, let me know, I’m curious. It could also be useful to try with another Google account and see if anything changes. Thx!
Hi Michael,
Is there a way to use last quarter or this quarter for the date range?
Hi Diana,
from the documentation the accepted formats appear to be NdaysAgo, yesterday, or today (where N is a positive integer) in addition to the format YYYY-MM-DD.
You can find it here: https://developers.google.com/analytics/devguides/reporting/data/v1/rest/v1beta/DateRange?hl=en