What is wrong with the formular?

Question

I am bloody new to this forum - so if I am doing something wrong, please let me know.

Here is my question: I want to build a URL in Google Sheets where you have to fill out some parts (it for tracking) and then get a URL which is fine for us to track.

Here is the formular:

=WENN(B6="","",VERKETTEN(A6 ,"?utm_source=", B6 ,"&utm_medium=", C6 ,"&utm_term=", E6 ,"&utm_content=", H6 ,"&utm_campaign=", D6 ,"&utm_ad_group=", F6 ,"&utm_creative=", G6))

(I am from Germany so "WENN" is "IF" and "VERKETTEN" is "CONCATENATE")

But I always get an error message and cant understand why..

The field A6 is the normal URL like "https://www.x.com" and the other fields are UTM Parameter like this:
"?utm_source=ActiveCampaign&utm_medium=email&utm_content"
and these can be should be filled out individually in the sheet and the you can use the URL.

I hope you can understand what I mean!

Answer 1

As Daniele noted in the comments to your original post, Germany requires semicolons as parameter separators where the US uses commas. So if you change every comma to a semicolon, your formula should work.

However, in case you don't know this, you can process an entire range of data with such a formula instead of needing to drag it down, if the formula is restructured as an array formula. Some functions that work in individual formulas (e.g. CONCATENATE) don't work the same way in an array, but the ampersand character & does work in arrays.

So suppose you want the fully built URLs to appear in Col I. Delete everything from Col I (including the header), and place the following formula in I1:

=ArrayFormula({"Your Header"; IF(B2:B="";; A2:A & "?utm_source=" & B2:B & "&utm_medium=" & C2:C & "&utm_term=" & E2:E & "&utm_content=" & H2:H & "&utm_campaign=" & D2:D & "&utm_ad_group=" & F2:F & "&utm_creative=" & G2:G)})

This will produce the header text (which you can change inside the formula as you like) and results for all non-null rows.

If you use an array formula such as this, the formula then "owns" the range you asked it to process (in this case, all of Col I). This means that you can no longer enter data or other formulas by hand (or by script) into that column, or you will "break the array"; in that case all formula results will disappear, I1 will show an error, and all that will remain is the one entry you just entered by hand (or script).