GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration

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.

Table of Contents

Getting started with the Google Sheet
The Google Sheet explained
Syncing GA4 Events & Parameters to Firestore
- Google Cloud & Firestore Setup
  - Firestore Setup
  - Create a Google Service Account
- Firestore Setup in Google Sheet
Server-side Google Tag Manager Setup
- Firestore Lookup Variable
Some final words
Apps Script Privacy Policy & Terms of Service
Google Apps Script
- Code.gs
- Page.html

Getting started with the Google Sheet

GA4 Events & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration

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.

Google Sheet Select Parameter Apps Script Application

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.

GA4 Events & Parameters stored in Firestore — Firestore Event Data

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
1. Select Native Mode
Choose where to store your data
1. 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.
- See Firestore Collections help text.
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.

Server-side GTM Firestore Lookup Variable — Firestore Lookup Variable

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.

GA4 Tag in Server-side GTM with "event_group" & Firestore Lookup Variable setup — GA4 Tag in Server-side GTM with “event_group” & Firestore Lookup Variable setup

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.

GA4 Event Group example — **GA4 Explore report** showing **Event Group** & **Event Name**

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

// **** 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)
  }
}
}

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

<!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>

<!doctype html>

<html>

<head>

<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>

$(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>

<br />

if(Object.prototype.toString.call(parameters) === '[object Array]') { ?>

<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>

</form>

</div>

</body>

</html>

Savio.no

Digital Analytics & Internet Marketing

GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration

Getting started with the Google Sheet

The Google Sheet explained

Events Sheet

Adding new Events

Editing/adding Parameters to a Parameter Cell

Parameters & Item Parameters Sheets

Settings Sheet

Syncing GA4 Events & Parameters to Firestore

Google Cloud & Firestore Setup

Firestore Setup

Create a Google Service Account

Firestore Setup in Google Sheet

Server-side Google Tag Manager Setup

Firestore Lookup Variable

Some final words

Apps Script Privacy Policy & Terms of Service

Google Apps Script

Code.gs

Page.html

Related Posts:

Be the first to comment on "GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration"

Leave a comment Cancel reply