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!
A great tool but I have run into a related problem to what Jim had. My imported table’s dates are not formatted as dates but as numbers for some reason and when converting, sheets fails to translate the number value to the correlating date value and instead chooses the last date in the built-in date table as the value. If I schedule the report, it overwrites the formatting of the column itself. Is there any way to solve this?
Hi Freddy,
a solution to this inconvenience could be to recall the data in another tab of the Sheet and apply the formatting and formulas there. In this way, the report Sheet will be updated on the next execution and your tab will automatically take the new data.
Hi Michele,
All the other GA4 gsheet plugins format the date correctly (2023-05-16) so there must be a way (like this one: https://workspace.google.com/u/0/marketplace/app/ga4_reporting_for_google_analytics_4/126881055683). I really appreciate your work, but this is the missing piece. Would you mind having another look? Would be appreciated.
Hi balint,
the result that the add-on shows is exactly what Google provides. I also performed a test with the Google tool (https://ga-dev-tools.google/ga4/query-explorer/) and the result is the same.
Anyway, this may be an interesting issue to evaluate as I am avoiding making custom changes so as not to risk invalidating the API result. In this case it could be an exception to be put into evaluation in the near future.
In the meantime, I’ll try to find out whether or not it might be something expected with the next Google releases.
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
Thanks Michele!
Can’t we get data from the day (“today”)? Even the updating could be set for “every 2 hours”, the last date is the yesterday date. I’d like to understand. Thank you.
Hi Jessica,
GA4 can take 24-48 hours to process data in platform. This is the time in which the data will be available. Consequently you can pull they after that time (note: data can be considered stable after 72h).
It’s not the add-on’s problem, this is normal GA4 operation..
The 2 hours schedule is a feature that does not currently have a specific effect on the current extracted data (it was inherited from the schedule in Universal Analytics). I don’t exclude that with the next updates of the GA4 platform or the GA4 API it could be useful in the future.
What if I wanted to create a report that has three pages and shows their monthly views, but makes each day a column so I can create a line graph?
I tried using an array of dates, but it looks like I can only compare two days.
Hi Ian,
now you can indicate up to 4 dates to compare (that the Google API limit for GA4). One date in the fields “Start date*” and “End date*” and up to 3 dates in the fields “Compare start date” and “Compare end date”. Separate them with a comma in this field like this (the following values are for example purposes only):
Compare start date: 2023-02-01,2023-01-15,2023-01-01
Compare end date: 2023-02-11,30daysAgo,yesterday
I create a new custom dimension on an existing custom event. How can load all data for the event by this add-on? Currently, I can only load event record after this new dimension defined. I want to load all old and new event record no matter if it has this new dimension value.
Hi ccmoon, a new custom dimensions in Google Analytics do not take effect retroactively. It starts collecting data the moment it is created. Have you tried querying that event without specifying the custom dimension?
Ciao Michele, innanzitutto complimenti per questa implementazione, voglio chiederti, dopo aver impostato le metriche che mi servono e inserendo come “order by” il giorno, per i giorni in cui le metriche hanno valore pari a 0 questi giorni non vengono mostrati. Come posso mostrare comunque quei giorni con valore 0?
Ciao Nico,
grazie per il feedback e per la segnalazione. Quella che hai menzionato è una questione aperta, ho ricevuto altre segnalazioni su questo punto, tuttavia si tratta di un problema che deve essere integrato lato API di Google e che su Universal Analytics funzionava invece come atteso.
Il risultato che mostra il componente aggiuntivo è esattamente ciò che fornisce l’API di Google, senza alcuna elaborazione. Le API di Google in questo momento sono in versione alpha e beta, mi aspetto con i prossimi aggiornamenti che vengano migliorate, ad esempio proprio con l’integrazione che hai indicato.
L’integrazione ad hoc di questa funzionalità invece richiederebbe un workaround che potrebbe minare la solidità della risposta dell’API, senza contare che nel frattempo Google potrebbe rilasciare la funzionalità spontaneamente.
Ho visto in rete che potrebbero esserci delle soluzioni da applicare manualmente ai report per aggiungere date con valore 0, ad esempio qualcosa del genere (non l’ho provato): https://stackoverflow.com/questions/58117628/adding-missing-dates- a-fogli-google
Spero di averti dato un suggerimento utile:)
Grazie!
Hi Michele, thank you so much for making this tool! Is there a way to append new data to the end of the existing data when re-running a report? In other words, opt not to replace data that has already been pulled, and just add new lines to the end instead? If not, do you have any plans to add this functionality in the future? For reference, this is a link to SyncWith’s documentation for a similar feature on their Google Sheets add-on: https://syncwith.com/gs/support/replace-append-synchronize-h8XRQPnhYszSGPgXsK8ZCQ
Thanks!
Hi Claire, thanks for the feedback! 🙂
This functionality is not expected, the user experience of the add-on has tried to keep the pattern of the Universal Analytics add-on. It could be a good starting point for a future integration since the APIs currently have big query/quota limits. For now I suggest creating dynamic named reports and using one sheet to collect the data of all the various reports that will be created 🙂
Sono un fan giapponese del GA4 Magic Reports che hai sviluppato.
Grazie per il fantastico add-on.
Permettimi di farti una domanda.
Configurazione dei report GA4
Vorrei impostare i report su GA4 Reports Configuration e aggiornarli automaticamente secondo una programmazione, ma
c’è un limite al numero di report che posso impostare?
Inoltre, questo limite dipende anche dalla complessità dei filtri, dal numero di metriche e dimensioni?
Grazie in anticipo.
Hi Nakata, thanks for the feedback! 🙂
There is no real limit to the number of reports however, as was also the case with the official Universal Analytics add-on, generating too many reports in a single Spreadsheet may not work. In this case I suggest splitting the reports across multiple Google Sheets 🙂
Is it possible to select “past 7 days” date range?
Hi Shay, you can use “7daysAgo” notation in the date field.
Hi Michele, thanks for creating this great tool. I am trying to recreate a report I had from the UA plugin which had metric = ga:goal7Completions and dimensions = ga:date,ga:Hour,ga:Minute,ga:sourceMedium but keep getting incompatibility issues when i select the relevant custom conversion metric with these dimension, i also tried total users plus a filter for that metric but no joy. Any ideas how I can make this work?
Hi Michele, I have half solved this issue the only outstanding problem is getting the minute metric
Hi Cliodhna, Analytics stores some combinations of dimensions and metrics separately that can’t be queried together. You can check compatibile fields with this tool: https://ga-dev-tools.google/ga4/dimensions-metrics-explorer/
I dont see all my GA4 accounts I see about 12 of them but I dont see the one I am looking for. I have access to the account in GA4 and all under same email. What do i have to do to see my Account? Thanks
Hi jamal,
are you using many Google accounts in the browser?
Very often problems with add-ons (as well as in the official one of Universal Analytics) are related to the use of multiple accounts.
If so, try logging into your browser with a single account.
Let me know!
Hi. This add-on is great, thanks. Is there a way to set the date range to something like “past 7 days” until “yesterday”?
Hi Bryan, thanks for your feedback!
You can use “7daysAgo” and “uesterday” notation in the date field.