Formula-Generated URL Not Appearing in Mail Automation

[InfaReQt]

Hi everyone😄

I’m running into an issue with a formula-generated URL in my email automation. The goal is to create a clickable Google Calendar invite link based on date columns in my table (e.g., {Wanneer} for start time and {Opruimen} for end time), and include some predefined body text.

Here’s the formula I’m using in a {URL} column:

"https://calendar.google.com/calendar/render?action=TEMPLATE&text=" & substitute({Ruimte} & " with " & {Wanneer}, " ", "%20") & "&dates=" &
(year(dateAdd({Wanneer}, -1, "hours")) & if(month(dateAdd({Wanneer}, -1, "hours"))<10,"0","") & month(dateAdd({Wanneer}, -1, "hours")) & if(day(dateAdd({Wanneer}, -1, "hours"))<10,"0","") & day(dateAdd({Wanneer}, -1, "hours")) & "T" & if(hour(dateAdd({Wanneer}, -1, "hours"))<10,"0","") & hour(dateAdd({Wanneer}, -1, "hours")) & if(minute(dateAdd({Wanneer}, -1, "hours"))<10,"0","") & minute(dateAdd({Wanneer}, -1, "hours")) & "00Z") &
"/" &
(year(dateAdd({Opruimen}, -1, "hours")) & if(month(dateAdd({Opruimen}, -1, "hours"))<10,"0","") & month(dateAdd({Opruimen}, -1, "hours")) & if(day(dateAdd({Opruimen}, -1, "hours"))<10,"0","") & day(dateAdd({Opruimen}, -1, "hours")) & "T" & if(hour(dateAdd({Opruimen}, -1, "hours"))<10,"0","") & hour(dateAdd({Opruimen}, -1, "hours")) & if(minute(dateAdd({Opruimen}, -1, "hours"))<10,"0","") & minute(dateAdd({Opruimen}, -1, "hours")) & "00Z") &
"&details=" & substitute(substitute({BodyText}, "\n", "%0A"), " ", "%20") & "&location=&ctz=Europe/Amsterdam"

This builds a working Google Calendar template URL with encoded title, start/end times (adjusted back 1 hour), and details from {BodyText}.

https://calendar.google.com/calendar/render?action=TEMPLATE&text=Aula%20Bovenbouw%20with%2007/10/2025%2015:00&dates=20251007T140000Z/20251007T160000Z&details=Beeld%20op%20de%20beamer:%20Ja%0AGeluid%20vanuit%20de%20laptop:%20Nee%0AMicrofoon:%202%0AHeadset:%20Nee%0AWensen:%20%0AKlaarstaan%20vanaf:%2007/10/2025%2015:00%0AOpruimen%20vanaf:%2007/10/2025%2017:00&location=&ctz=Europe/Amsterdam

I have a mail automation triggered when a single-select column is set to “Goedgekeurd” The email template includes the {URL} field.

The {URL} doesn’t appear in the received email at all. No link, no text, just blank where it should be.

Any ideas on why this might be happening? Is there a length limit on URLs in automations, or something with special characters/encoding? Maybe a workaround like shortening the formula or using a different approach?

[Soonimproveduser]

What format is the URL field? Have you used a text column instead?

[bha]

Hi @InfaReQt,
After few tests, the problematic part of your formula seems to be substitute({BodyText}, "\n", "%0A")

Looking at this post, I found this sentence from Christoph:

@cdb

Last hint: the only special char that is not supported, is the backslash \. This can never be returned by a formula column and will always result in an error.

It’s not exactly the same case, but I think the problem is related. To get rid of this issue, you will have to replace this part of your formula with

substitute({BodyText}, "
", "%0A")`

The character between the two first double quotes being a new line character. The format you’ll have to use for your BodyText column actually depends on the type of the column:

• if BodyText is a Long text column, you don’t have to do anything special, the line breaks in the Long text editor will be correctly replaced. However, other special formatting (bold, italic, etc.) won’t be rendered as you might expect (you’ll actually see the markdown formatting in your event description such as **Lorem Ipsum** instead of Lorem Ipsum
• if BodyText is a text column, you will have to insert new line characters in your text field, as discussed in the recent thread Insert a new-line into a (short) text field?. If you define the content of the BodyText column yourself (and you alone), you can also decide to replace the \n new line symbol by a special less problematic character you never use anywhere else in your message such as ¤ and adapt the substitute formula consequently

PS: I noticed that your formula contains &location=`: either you forgot to specify a location, or you can get rid of this part as it doesn’t contains any information.

Hope this helps!

Bests,
Benjamin