From Sheets to GTM: automating a simple process about dataLayer

I recently had some fun with Google Sheets. My goal was to automate the creation of a code snippet for Google Tag Manager, based on two predefined columns. The first column had the name of the event, and the second column could contain several parameters on multiple lines. For instance:

window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
    event: 'my_event',
    page_category: '{{page_category}}',
    contact_subject: '{{contact_subject}}',
});

The first column should therefore contain the following code:

my_event

The second column should contain the following code:

page_category
contact_subject

Implementation

First column

I started by creating my base, which is just the cell containing the event. For this, I concatenate the beginning of the script with the name of my event.

=CONCATENATE("window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
        event: '";cellule;"',"
});")

cellule is the reference to the content of my first cell. I insert it in the middle of the two other strings using the CONCATENATE function.

Second column

Then, things got a bit more complicated.
I use a single cell to define my additional parameters. Right away, I realized that I needed to split the values at the new line, as that’s how I define a new value. I could have used another separator, such as a comma, but the convention we use means that, for now, it’s the line break that acts as the separator.

I started by trying to split this value into three distinct values, which I could then work with. The idea was that once I managed to extract a value from each line, it would be easier to apply a transformation to each one. I tried using the SPLIT function. The second argument is literally a line break.

=SPLIT(A1;"
")

This works, but I get the result in a single row with three columns. I’d prefer the result in a column to process them more easily. For this, I discovered the TRANSPOSE function, which transforms a row into a column. Applying it directly to the result of my SPLIT function, I get a column containing my values.

The next step is to retrieve each line’s value and generate the following text:

valeur: '{{ valeur }}',

For this, I discovered the MAP and LAMBDA functions. This immediately reminded me of programming, so I wasn’t too lost and quickly understood how these two functions work. They take an array as a parameter and apply a function to each element of that array. In the following code, event corresponds to each value in each array element.

=MAP(TRANSPOSE(SPLIT(A1;"
")); LAMBDA(event; CONCATENER("'";event;"': {{ ";event;" }},")))

Once I have this, I just need to join all the resulting lines from my MAP into one text block. I join them with a new line.

JOIN("
    "; 

Which gives:

=JOIN("
    "; MAP(TRANSPOSE(SPLIT(cellule; "
")); LAMBDA(event;CONCATENER("    ";event;": '{{'"; event;"}}',"))))

I now have my value that I just need to insert into my script.

Reusability with Named Functions

To facilitate understanding and reuse, I will create two named functions. Named functions are custom functions that you can create yourself, allowing you to reuse code snippets as if you were using a native Google Sheets function, like SUM for example.

I create the first function for my initial code snippet, which only contained the event. I call it DATA_LAYER. For the second one, as it generates additional parameters for the dataLayer, I will call it DATA_LAYER_OBJECT.

Below is a screenshot of the interface for creating a named function.

Creating the Named Function “DATA_LAYER” in Google Sheets.

Once these two functions are created, I need to modify the first function to take the result of the second one as a parameter and include it in the script:

=CONCATENER("window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
        event: '";cellule;"',
    ";object
;"
});")

Here object refers to the DATA_LAYER_OBJECT function. This way, when I want to use my script, I just have to run the following function:

=DATA_LAYER(A1; DATA_LAYER_OBJECT(B1))

To get the corresponding result:

window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
        event: 'my_event',
        page_category: '{{'page_category}}',
        contact_subject: '{{'contact_subject}}',
});

A final point: if one of my columns is empty, there will be an error in the generated content, and Google Sheets will not display the content of the cell. So, I need to handle this case by adding conditions if the cell content is empty.

For the DATA_LAYER function:

=CONCATENER("window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
        event: '";cellule;"',
    ";IF(ISBLANK(object);"";object)
;"
});")

For the DATA_LAYER_OBJECT function:

=IF(ISBLANK(cellule);"";
JOIN("
    "; MAP(TRANSPOSE(SPLIT(cellule; "
")); LAMBDA(event;CONCATENER("    ";event;": '{{'"; event;"}}',")))))

Testing with ChatGPT for better and faster solution

It took me about an hour to set up this script, between the trials, functions that didn’t work, semicolon and comma errors, reference errors, and empty cells. I still don’t have the reflex to use AI for this kind of task, and yet that’s usually where it shines.

After writing this article, I decided to test with ChatGPT to see if I could generate the same result in less than an hour. It turns out I managed to do it in 20 minutes.

Here’s the prompt I sent:

On Google Sheets, I have a column containing a text value. I have a second column containing multiple text values, each separated by a line break.

Given the following input:
"my_event" in the first column.
"page_category
contact_subject" in the second column,

Generate the code to insert into a third column to get the following result:

window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
    event: 'my_event',
    page_category: '{{'page_category}}',
    contact_subject: '{{'contact_subject}}',
});

The formatting of this final column is important; the quotes, braces, and parentheses must be kept.

Following this prompt, it generated code that I tested immediately. It didn’t work at first, but it seemed correct: it used the same ideas of SPLIT and JOIN that I had. Looking closer, I realized that it was using commas to separate the arguments. It probably didn’t account for the fact that Google Sheets uses semicolons as separators. Once I explained the problem, it corrected the code, and it worked. Here’s the final code generated by ChatGPT:

="window.dataLayer = window.dataLayer || [];" & CHAR(10) &
"window.dataLayer.push({" & CHAR(10) &
"    event: '" & A1 & "';" & CHAR(10) &
"    " & JOIN(";" & CHAR(10) & "    "; ARRAYFORMULA(SPLIT(B1; CHAR(10)) & ": '{{" & SPLIT(B1; CHAR(10)) & "}}'")) & CHAR(10) &
"});"

I think that if it hadn’t made the semicolon error, the first response would have been correct, and the result would have been implemented in 2 minutes. So, there’s a clear lesson for me to take for the future: take the time to properly break down the problem and send it to ChatGPT.

However, this exercise without AI allowed me to improve my knowledge of spreadsheets. I don’t think that would have been the case if I had just taken the code from ChatGPT. I would have seen the code and honestly wouldn’t have bothered to try and understand how it works. With the manual work I did, I now understand this code more easily, and I can see ways to improve my own code.

For example, I discovered the use of CHAR(10) thanks to ChatGPT. Instead of manually typing a line break, CHAR(10) makes it clearer that we want to use a line break. It’s also a benefit of LLMs: they make you think of different ways to approach a problem that you might not have considered.

Conclusion

I’m really impressed with what’s possible on Google Sheets. I know I’m only scratching the surface, but the fact that you can use functions and even create your own makes it feel a lot like programming. In this context, I easily find the programming logic, and I can work out my reasoning without being too slowed down by my lack of knowledge.

I know this knowledge can be transferred to Excel as well, as the syntax and functionality are very similar. So, it’s always interesting to manually test these kinds of approaches. I also think it could be a great asset to help my colleagues who use spreadsheets more often. I know many of them use spreadsheets, but not to their full potential. There’s a lot of copy-pasting, whereas knowing the right function can make things faster, better, and less tedious.