GA4 Sheets Add-on

 With UA being sunset at the end of June 2023, here is a Google Sheets replacement for the UA Google Analytics Sheets Add-on that runs for GA4:

https://docs.google.com/spreadsheets/d/1vlO5XhrYolG6prDA5cpKZhZJY7EcE-Kv0Zr_X5VAmZ0/copy

All the code is open source in the Apps Script of the sheet and doesn't share any information to other people (unless you give other people access to the sheet). It uses the Google Analytics Data API, Google Analytics Admin API and Google Sheets API.

The 'Config' tab is designed to be similar to the official sheets add-on for UA. The values entered need to use GA4 API values which can be most easily found using https://ga-dev-tools.google/ga4/query-explorer/

To use the sheet:

1. Make a copy of the sheet above
2. Read the 'Config Notes' tab
3. Fill in col B of the 'Config' tab with details for your first report. 
• Fill from Col C onwards to get additional reports in the same spreadsheet
4. Click the 'Run Reports' menu > 'Refresh Data'. Alternatively, go to 'Extensions' > 'Apps Script' and run 'myFunction' in main.gs
• You will need to give the requested permissions from Google on first run of any new sheet
5. Set a trigger for 'myFunction' to automatically run as frequently as desired

Things to be aware of:

1. On first run, it is recommended to just add your View ID to B3 of the 'Config' tab and run to check your sheet is set up correctly and returning data
2. Limited error handling. A short list is provided below in the 'Known Errors with this Sheet' section, though this is not exhaustive
3. This sheet uses the runReport request in the Google Analytics Data API. Other requests from this API aren't supported
• Note: The Analytics Admin API is called to return the Property name for the output tab (cell C3)
4. Google restricts each call to 100,000 rows of data. If you have more rows of data than the limit set in the sheet (max 100,000), the script will create multiple additional sheets to house the additional data
• There is a threshold of 5 sheets max to ensure the script doesn't get stuck in a loop if the data range is accidentally too large. If you have this error, see point 2 of the 'Known Errors' section below
5. For accurate Google Ads keyword level impressions, clicks and cost, it's recommended to use Google Ads Scripts to get the data (see points 2, 3 in 'General GA4 limitations' below) and merge in your google sheet
6. Metric Filters, Dimension Filters and Order are the three most likely inputs to cause issues. It's recommended to get a query working in the Google Dev Guide and copy over the inputs for these three sections, as the error messaging for failures there is more robust
7. Dates. If you use date as a dimension, it is returned in the format 'yyyyMMdd' as text

General GA4 limitations:

1. Data Freshness
• Data generally takes longer to appear in GA4 than UA and data from the past 24 hours may be incomplete
2. Google Ads Metrics
• Data only appears for a dimension which has events. 
• E.g. for one campaign with two keywords, if each keyword gets 1 impression and one keyword gets one click, reporting at campaign level will show 2 impressions and 1 click, but reporting at keyword level will only show 1 impression and 1 click for one keyword and no data for the other keyword
3. Google Ads Dimensions
• Keyword Match Type is not a supported dimension in GA4

Known Errors with this Sheet:

1. If you created a new sheet and added the code in Google Apps Scripts manually instead of making a copy of the sheet linked at the top of the page, you will need to enable the following services from your Apps Script:
• Google Analytics Data API
• Google Analytics Admin API
• Google Sheets API
2. Error response: Error: Number of sheets required is greater than 5, stopping script. Increase threshold variable to run
• You have more data than can be output
• If you expect your data to be lower than 5x the limit value you set in row 12, check your settings
• If you expect your data to be lower than 5x the limit value you set in row 12, increase the threshold variable in runReport1.gs under the function stopIfGreaterThanThreshold() to allow the spreadsheet to create more sheets. 
• Alternatively, if your limit is less than 100,000, you can increase the limit in the 'Config' sheet
3. Error response: HttpResponseException: Response Code: 404. Message: <!DOCTYPE html>
• Check you have entered a Property ID accurately (row 3 of 'Config')
4. Error response: Exception: Invalid argument: url
• If you are running the script from inside the Apps Script (instead of the menu in your spreadsheet), check you are on the main.gs file and running 'myFunction'
5. Error response: TypeError: Cannot read properties of undefined (reading 'map')
• No data returned by query. Check your date range and filters to ensure data will be returned
6. Error response: TypeError: Cannot read properties of null (reading 'getDataRange')
• Incorrect Sheet Name. Create a new sheet with the name in row 2 of config
7. Error response: Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate
• Additional rows/columns have inputs but are not used for the query. Clear all columns and rows outside of your desired reports in 'config' sheet

Final notes

If you have any questions/suggestions/issues about this sheet, feel free to add comments below. 

If you are interested in a bespoke report using the Google Analytics Data API or Google Analytics Admin API, reach out to ppcandanalyticsconsultant@gmail.com for a quote. 

If you are interested in any PPC automation or Google Stack automation support, reach out to ppcandanalyticsconsultant@gmail.com for a quote.