Google Analytics 4 (GA4) is a Event based tool, and with Event based analytics tools, a measurement plan/documentation is important. This because you need to document which Events & Parameters are used, and what they are measuring. Another important part is to document which Parameters are allocated to the different Events.
There are many ways to do this documentation, but unless you are using a “special tool” for this, the documentation can be time consuming, and the possibility of errors in the documentation is a potential problem.
In addition, this is just documentation, it has no real influence of the GA4 Events & Parameters that are being collected.
I decided to see if could do something about this, so I made a Google Sheet powered with Google Apps Script with Google Cloud Firestore integration. Firestore again can be integrated with Server-side Google Tag Manager (SGTM), which means the GA4 documentation in Google Sheet is now integrated with the data collection. Here are some possible options:
- Flag GA4 Events that are being collected and not documented.
- Route GA4 Events to a different GA4 Property if Events aren’t documented.
- Block GA4 Events that aren’t documented.
These examples aren’t randomly selected. This functionality can be found in some other Event based analytics tools, and also in third party solutions that handles Event documentation.
Getting started with the Google Sheet

- First, open the Google Sheet, and make a copy.
- Wait for the Parameter & Firestore Menu to appear, then click on Add/Edit Parameters. This will start the activation of the Apps Script called GA4 Event & Parameter functions.
- Beware, Google will now throw all kinds of warnings at you since this isn’t a registered appliation. There isn’t anything shady in the Apps Script, so go ahead an allow the app. You find the code at the end of this blog post.
The Google Sheet explained
The Google Sheet contains the following Sheets:
- Events
- Parameters
- Item Parameters
- Conversion Events
- This Sheet is listing Events from the Events Sheet where the Event is marked as a Conversion Event.
- User Properties
- This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to User.
- Event Parameters
- This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to Event.
- Session Parameters
- This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to Session.
- This Sheet is hidden since Session Parameters don’t exist. Just trying to predict the future.
- Settings
- Helper
- This Sheet is hidden, and has some helper functionality
Events Sheet
Here you document your Events. The most common GA4 Event Names are prefilled and described. The different columns in the Sheet are described below.
Column Name | Description |
---|---|
Event Group | Grouping Events together with a event_group parameter is a good thing to do. It can make it easier to understand your Events, and do queries in BigQuery. The Event Group parameter will also be used in the Firestore to Server-side GTM integration. How to group the Events is up to you. |
Event Name | Type the Event Name here. If the Event Name is a duplicate, the cell will be highlighted with a red color. If the Event Name is written in a format that isn’t snake_case, input will be rejected. Enforcing snake_case can be turned off in the Settings Sheet. |
Method | Select from a drop-down list how this Event is generated. This is just for your own documentation. |
Event Type | Select from a drop-down list what kind of Event this is. This is just for your own documentation. |
Conversion Event | Conversion Event documentation. ✔ = Conversion Event. |
Event Description | Write a description for the Event here. |
Parameters | Parameters from the Parameters Sheet are added here. Do not Edit cells manually, since this may break some functionality. Only add Parameters to a cell by using the Apps Script application, or copy + paste from another Parameter cell. |
Item Parameters | Parameters from the Item Parameters Sheet are added here. Do not Edit cells manually, since this may break some functionality. Only add Parameters to a cell by using the Apps Script application, or copy + paste from another Item Parameter cell. If Item Parameters isn’t relevant for your documentation, just hide the Item Parameters column. |
Comment | Write a comment/description about your parameters here. |
Autofilled Time | If either Event Name, Parameters or Item Parameters are changed/added, this column will be autofilled with the time the change was made. |
Platform | Documentation of which platform this Event should be generated on. If you have more platforms, just add more columns. |
Info related to Google Analytics & GTM | Documentation related to GA (Universal Analytics) and Google Tag Manager. Ex. what data in GA is similar to this Event, or which Tag in GTM is sending this Event. |
Adding new Events
- The quickest method for adding a new Event, is to copy a similar Event, and edit the information afterwords.
Editing/adding Parameters to a Parameter Cell
This is the most timesaving functionality in this Google Sheet.
- Select the Parameter Cell that you want to Edit.
- If the Apps Script application isn’t active, select Add/Edit Parameters from the Parameter & Firestore Menu.
- If the Apps Script application is active, click on the Get Parameters from Cell button.
- Select relevant Parameters in the Apps Script application, click the Fill Current Cell button.
- Searching for parameters is also possible.
Parameters & Item Parameters Sheets
The most common Parameters and Item Parameters are prefilled. The different columns are described below. If you need more columns in your documentation, just add the columns. It will not break any functionality.
If Item Parameters aren’t relevant for your documentation, just hide the Item Parameters Sheet.
Column Name | Description |
---|---|
Group Name | This is just for internal grouping of Parameters in the Sheet. How to group Parameters is up to you. |
Parameter Name | The Parameter Name |
Parameter | Type the Parameter here. If the Parameter is a duplicate, the cell will be highlighted with a red color. If the Parameter is written in a format that isn’t snake_case, input will be rejected. This can be turned off in the Settings Tab. |
Scope | Can be selected from dropdown. Available choises: Event, User & Session (session doesn’t exist yet in GA4, so I try to predict the future here). Scope isn’t relevant in the Item Parameters Tab. |
Type | Select Parameter Type from dropdown (Event Standard Parameter, Custom Dimension etc.) |
Format | Select Format from dropdown (Array, Boolean, Number or String) |
Info related to Google Analytics & GTM | Documentation related to GA (Universal Analytics) and Google Tag Manager. Ex. what data in GA is similar to this parameter, or what is the Variable in GTM called. |
Settings Sheet
Settings for the Google Sheet and the Firestore integration can be set here. Firestore settings is explained in the Firestore section.
There is only one setting for the Google Sheet, and that is to enforce snake_case for Event & Parameters.
Syncing GA4 Events & Parameters to Firestore
Google Cloud Firestore is a NoSQL document database built for automatic scaling, high performance, and ease of application development.
To access Firestore with Apps Script, the Firestore library has been installed in this Google Sheet.
Firestore free quota per day is 50,000 document Reads, 20,000 document Writes and 20,000 document Deletes. See Firestore pricing for more information.
Syncing GA4 Events & Parameters to Firestore works like this:
- If the Event Name isn’t in Firestore, the Event Name & Parameters will be added.
- If the Event Name is in Firestore & Google Sheet, the Event Name & Parameters will be updated.
- If the Event Name is in Firestore, but not Google Sheet, the Event Name & Parameters will be deleted from Firestore.
The following data is synchronized with Firestore:
Parameter | Description |
---|---|
change_status | added or updated (or deleted if you activate logging of data synchronized to Firestore) |
date_edited | Date event_name, parameters or items was edited. Date comes from Autofilled Time column. |
event_group | Event Group from Events Sheet. |
event_name | Event Name from Events Sheet. |
parameters | Array. Parameters from Events Sheet. |
items | Array. Item Parameters from Events Sheet. |

Google Cloud & Firestore Setup
I recommend that you create a new Google Cloud Project for the Firestore setup.
Firestore Setup
Follow the steps below to set up Firestore.
- Select a Cloud Firestore mode
- Select Native Mode
- Choose where to store your data
- Create Database
Create a Google Service Account
To connect this Google Sheet to Firestore, the easiest way is to create a Google Service Account with read/write access to your Firestore database. Giving a service account access to your datastore is like giving access to a user’s account, but this account is strictly used by your script, not by a person.
- Open the Google Service Accounts page.
- Select the Firestore project, and then click “Create Service Account“.
- For your service account’s role, choose Datastore > Cloud Datastore Owner.
- Check the “Furnish a new private key” box and select JSON as your key type.
- When you press “Create“, your browser will download a .json file with your private key.
- Save the .json file locally.
Firestore Setup in Google Sheet
- Go to the Settings Sheet and Firestore settings.
- Insert the following values from the .json file:
- Client Email -> client_email from the .json file
- Project ID -> project_id from the .json file
- Private Key -> private_key from the .json file
- First Collection -> Name of first collection. Suggested name event_data.
- Date Format -> Date format stored in Firestore. Dropdown menu.
- Date stored comes from Autofilled Time in the Event Sheet.
- Log data submitted to Firestore -> Logs all data synched to Firestore in a different collection (event_data_history).
- Yes/No dropdown menu.
Firestore Settings will be stored as Apps Script Script Properties, since I think it’s better to reference a Script Property in the Apps Script, than a Google Sheet Cell or Named Range.
Server-side Google Tag Manager Setup
The last part of the puzzle is to either flag, block or route undocumented Events in Server-side GTM. In the example setup here, I’m going to flag undocumented Events.
Firestore Lookup Variable
To do that we are going to use a Firestore Lookup Variable. For detailed information about this variable, see the Enrich Server-side Data With Cloud Firestore by Simo Ahava. Do especially read the Override Project ID part, because most likely Server-side GTM and Firestore are in 2 different Google Cloud projects.
Settings | Value | Description |
---|---|---|
Lookup type | Document path | Help text from the Firestore Lookup Variable: “Look up a document by specifying its components (collection, document, subcollection)“. |
Document path | event_data/{{Event Name}} | If you have chosen a different First Collection in the Firestore Setting in Google Sheet, replace event_data with your choice. |
Key Path | event_group | Will return Event Group from the Google Sheet. |
Override Project ID | your-firestore-project-id | If Firestore lives in a different Google Cloud Project, you have to override Project ID. |
Convert undefined | missing | Undocumented Events will be flagged as missing Event Group. See settings in the Firestore Lookup image below. |

Now edit your GA4 Tag in SGTM. In the Parameters to Add / Edit section, add event_group as a parameter, and your Firestore Lookup Variable as Value.
Then add Event Group as a Parameter to the GA4 Tag, and the Firestore Lookup Variable as Value.

The image from the GA4 Explore report shows the result of this setup. The Events scroll and clipboard haven’t been documented, and are flagged as missing.
Do notice that session_start and first_visit has a (not set) Event Group. This is because these Events don’t pass throug Server-side GTM.
Some final words
In this blog post I have shared how Event and Parameter documentation in Google Sheet can be integrated with Server-side GTM data collection, by synchronizing the documentation to Firestore. There could be several other use cases for a Google Sheet + Firestore + SGTM integration, ex. rewriting utm_id campaign tagging to regular campaign tagging instead of uploading the data to Google Analytics etc.
Note that this (for the moment) only works with GA4 Web Data Streams. Apps (Firebase) can’t send data to Server-side GTM. I hope Google will fix this.
Apps Script Privacy Policy & Terms of Service
To be able to read and edit data in the Google Sheet, showing the Apps Script application, and syncing data to Firestore, the Apps Script needs the following authorizations:
- See, edit, create, and delete all your Google Sheets spreadsheets
- Display and run third-party web content in prompts and sidebars inside Google applications
- Connect to an external service
- See, edit, share, and permanently delete all the calendars you can access using Google Calendar
You can at any time withdraw access given to this application.
You have full overview over the code and what the code is doing. The code is documented below, and you can also see it in the Google Sheet by through the menu Extensions -> Apps Script.
Google Apps Script
Below are the scripts used in this Google Sheet. If I make any changes, I will update this blog post.
Code.gs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 |
// **** MENU **** function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Parameter & Firestore Menu') .addItem('Add/Edit Parameters', 'showDialog') .addItem('Send Event Documentation to Firestore', 'updateFirestore') .addToUi(); } function showDialog() { const html = HtmlService.createTemplateFromFile('Page').evaluate(); SpreadsheetApp.getUi() .showSidebar(html); } // **** END MENU **** // **** GLOBAL SETTINGS **** const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName('Settings'); // Settings Sheet // Save Firestore settings to PropertiesService const scriptProperties = PropertiesService.getScriptProperties(); scriptProperties.setProperty('CloudClientEmail',sheet.getRange('SettingsCloudClientEmail').getValue()); scriptProperties.setProperty('CloudProjectID',sheet.getRange('SettingsCloudProjectID').getValue()); scriptProperties.setProperty('CloudKey',sheet.getRange('SettingsCloudKey').getValue()); scriptProperties.setProperty('FirestoreFirstCollection',sheet.getRange('SettingsFirestoreFirstCollection').getValue()); scriptProperties.setProperty('FirestoreLogData',sheet.getRange('SettingsFirestoreLogData').getValue()); scriptProperties.setProperty('FirestoreDateFormat',sheet.getRange('SettingsFirestoreDateFormat').getValue()); const cloudClientEmail = scriptProperties.getProperty('CloudClientEmail'); const cloudProjectId = scriptProperties.getProperty('CloudProjectID'); const cloudKey = scriptProperties.getProperty('CloudKey').replace(/\\n/g, '\n'); const firestoreFirstCollection = scriptProperties.getProperty('FirestoreFirstCollection'); const firestoreLogData = scriptProperties.getProperty('FirestoreLogData'); const firestoreDateFormat = scriptProperties.getProperty('FirestoreDateFormat'); // **** END GLOBAL SETTINGS **** // **** READ DATA FROM SHEET **** // Creates a Timestamp if a column is edited. function onEdit() { try { // The column you want to check if something is entered. const checkEvent = 2; const checkParameter = 7; const checkItemParameter = 9; const sheetName = 'Events' // Where you want the date time stamp offset from the input location. [row, column] const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getActiveSheet(); //checks that we're on the correct sheet. if(sheet.getSheetName() === sheetName) { const selectedCell = ss.getActiveCell(); const row = ss.getActiveRange().getRow() //checks the column to ensure it is on the one we want to cause the date to appear. if(row > 8) { if(selectedCell.getColumn() === checkEvent || selectedCell.getColumn() === checkParameter|| selectedCell.getColumn() === checkItemParameter) { let dateTimeLocation; if (selectedCell.getColumn() === checkEvent) { dateTimeLocation = [0,10]; } else if (selectedCell.getColumn() === checkParameter) { dateTimeLocation = [0,5]; } else if (selectedCell.getColumn() === checkItemParameter) { dateTimeLocation = [0,3]; } if(dateTimeLocation) { let dateTimeCell = selectedCell.offset(dateTimeLocation[0],dateTimeLocation[1]); dateTimeCell.setValue(new Date()); } } } } } catch (err) { Logger.log('Timestamp: '+err) } } // Get Parameters from Sheet const getParameters = function(){ try{ // Get value from the adjacent cell const range = SpreadsheetApp.getActiveRange(); const col = range.getColumn(); const row = range.getRow(); const range2 = SpreadsheetApp.getActiveSheet().getRange(row,col+1); return range2.getDataValidation().getCriteriaValues()[0].getValues(); }catch(err){ Logger.log('Get Parameters from Sheet: '+err) return null } } // Get Parameters from Active Cell const getCurrentCellValue = function(){ try{ const cell = SpreadsheetApp.getActiveSheet().getActiveCell(); return cell.getValue(); }catch(err){ Logger.log('Get Parameters from Active Cell: '+err) } } // Fill Active Cell with Parameters function fillCell(e){ try{ let s = []; for(let i in e){ if(i.substr(0, 2) == 'ch') s.sort().push(e[i]); } if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join("\r\n")); // New Line join onEdit(); }catch(err){ Logger.log('Fill Cell: '+err) } } // Function for comparing arrays function inArray(item, array) { for (let i in array) { if (array[i] === item) return true; } return false; } let arrData, arr1 = [], arr2 = []; // Array 1 from list of parameters try{ var parameters = getParameters(); if(Object.prototype.toString.call(parameters) === '[object Array]') { for (let m = 0; m < parameters.length; m++) { for (let n = 0; n < parameters[m].length; n++) { if(parameters[m][n]){ arr1.push(parameters[m][n]); } } } } }catch(err){ Logger.log('Array 1: '+err) } // Array 2 from parameters in cell & do some trimming try{ arr2 = getCurrentCellValue().split("\r\n"); // New Line split const trimmer = () => arr2.map(el => el.trim()); arr2 = trimmer(arr2); }catch(err){ Logger.log('Array 2: '+err) } // **** END READ DATA FROM SHEET **** // **** FIRESTORE **** function updateFirestore() { /* See https://github.com/grahamearley/FirestoreGoogleAppsScript and https://levelup.gitconnected.com/import-data-from-google-sheets-to-firestore-using-google-apps-script-b6f857f82a2 */ if (!(cloudClientEmail && cloudProjectId && cloudKey)) { SpreadsheetApp.getUi().alert('Cloud Client Email, Project ID & Key are not set. No data is sent to Firestore.'); } else { const email = cloudClientEmail.toString(); const projectId = cloudProjectId.toString(); const key = cloudKey.toString(); const firestore = FirestoreApp.getFirestore (email, key, projectId); // Read data from Firestore const firestorePath = firestoreFirstCollection+"/"; const firestoreDocuments = firestore.getDocuments(firestorePath); const firestoreData = []; for (let a = 0; a < firestoreDocuments.length; a++) { firestoreData.push(firestoreDocuments[a].path); } // get document data from ther spreadsheet const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheetname = "Events"; const sheet = ss.getSheetByName(sheetname); // get the last row and column in order to define range const sheetLR = sheet.getLastRow(); // get the last row const sheetLC = sheet.getLastColumn(); // get the last column const dataSR = 9; // the first row of data // define the data range const sourceRange = sheet.getRange(dataSR,1,sheetLR-dataSR+1,sheetLC); // get the data const sourceData = sourceRange.getValues(); // get the number of length of the object in order to establish a loop value const sourceLen = sourceData.length; const timezone = CalendarApp.getDefaultCalendar().getTimeZone(); const date = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd HH:mm:ss"); const sheetData = []; // Loop through the rows for (let i=0;i<sourceLen;i++){ if(sourceData[i][1] !== '') { const data = {}; data.event_group = sourceData[i][0]; data.event_name = sourceData[i][1]; if(sourceData[i][6]){ data.parameters = sourceData[i][6].split("\r\n"); JSON.stringify(data.parameters); } if(sourceData[i][8]){ data.items = sourceData[i][8].split("\r\n"); JSON.stringify(data.items); } if(sourceData[i][11]) { data.date_edited = JSON.stringify(Utilities.formatDate(sourceData[i][11], timezone, firestoreDateFormat+" HH:mm")); } // UPDATE OR ADD DATA IN FIRESTORE try { if (inArray(firestorePath+data.event_name, firestoreData)) { // Events are both in Sheet & Firestore, update content in Firestore data.change_status = "updated"; firestore.updateDocument(firestorePath+data.event_name, data, true); } else { // Events are only in Sheet, add content to Firestore data.change_status = "added"; firestore.createDocument(firestorePath+data.event_name,data); }; } catch (err) { Logger.log('Update or Add data in Firestore: '+err) }; // Store history of changes in it's own Firestore Collection if(firestoreLogData==='Yes') { data.date = JSON.stringify(date); firestore.createDocument(firestoreFirstCollection+"_history/"+date+"_"+data.event_name+"_"+data.change_status,data); } sheetData.push(firestorePath+data.event_name); } } // DELETE DATA IN FIRESTORE // Delete Events that are in Firestore, but not in Sheet try { let deleteFirestoreData = firestoreData.filter(e => sheetData.indexOf(e) === -1); if(deleteFirestoreData && deleteFirestoreData !="") { const data = {}; data.change_status = "deleted"; data.date = JSON.stringify(date); for(let d = 0; d < deleteFirestoreData.length; d++) { const deleteFirestorePath = deleteFirestoreData[d]; // Store history of changes in it's own Firestore Collection if(firestoreLogData==='Yes' && deleteFirestorePath) { const event_name = deleteFirestorePath.split('/').pop(); firestore.createDocument(firestoreFirstCollection+"_history/"+date+"_"+event_name+"_"+data.change_status,data); } firestore.deleteDocument(deleteFirestorePath); } } } catch (err) { Logger.log('Delete data in Firestore:'+err) } } } |
Page.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
<!doctype html> <html> <head> <meta charset="utf-8"> <title>Select Parameters</title> <style> html, body{ font-family: Arial, Helvetica, sans-serif; font-size: 0.8em; } h1{ font-size: 1.2em; margin: 5px 0 5px 0; } .btn{ font-weight: bold; font-size: 1.1em; } .btn:nth-child(1) { margin-right: 8px; } #search{ width: 95%; font-size: 1.1em; padding: 3px; } input { vertical-align: -3px; } .info{ font-size: 1.2em; } </style> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <script> $(document).ready(function() { // Parameter search $("#search").keyup(function(){ const filter = $("#search").val().toLowerCase(); const checkbox = $("input[type='checkbox']"); const span = $("span"); for (let i = 0; i < checkbox.length; i++) { txtValue = checkbox[i].value.toLowerCase(); if (txtValue && txtValue.indexOf(filter) === -1) { span[i].style.display = "none"; } else { span[i].style.display = ""; } } }); }); </script> </head> <body> <div> <h1>Select Parameters</h1> <form id="form" name="form"> <input type="button" class="btn" value="Fill Current Cell" onclick="google.script.run.fillCell(this.parentNode)" /> <input type="button" class="btn" value="Get Parameters from Cell" onclick="google.script.run.showDialog()" /> <br /> <br /> <? if(Object.prototype.toString.call(parameters) === '[object Array]') { ?> <input type="text" id="search" placeholder="Search for parameter.." title="Type in a parameter" /> <br /> <br /> <? } ?> <? if(Object.prototype.toString.call(parameters) === '[object Array]') { if(arr1) { for (let i = 0; i < arr1.length; i++) { arrData = arr1[i]; ?> <span><input type="checkbox" value="<?= arrData ?>" name="ch<?= arrData ?>" id="ch<?= arrData ?>"<? if (inArray(arrData, arr2)) { ?>checked<? } ?>><label for="ch<?= arrData ?>"><?= arrData ?></label><br /></span> <? } } ?> <? } else { ?> <p class="info">Select a <strong>Cell</strong> in either the column <strong>Parameters</strong> or <strong>Item Parameters</strong>.</p> <? } ?> <br> <input type="button" class="btn" value="Fill Current Cell" onclick="google.script.run.fillCell(this.parentNode)" /> <input type="button" class="btn" value="Get Parameters from Cell" onclick="google.script.run.showDialog()" /> </form> </div> </body> </html> |
Be the first to comment on "GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration"