How can I insert formatted links in Google Sheet concatenations?

Question

I am using Google Sheets to run mail merges using the script editor; it references a cell with an email address and a cell with the body of the email. I am able to concatenate a number of other columns to generate the merge field values.

I am only able to insert plain text URLs, and in most cases they render as links in browsers.

However, I have recipient-specific URLs that are pretty long, and I'd like to alias them, in the body of the email. Basically, I want to get what would be the resulting links for the inputs below:

<a href=https://www.recipient-site.com>Your Site</a> VIA =(concatenate("<a href=",G2,">",F2,"</a>"))

or

=HYPERLINK("https://www.recipient-site.com","Your Site") VIA =CONCATENATE("=hyperlink(",char(34),G2,char(34),",",char(34),F2,char(34),")")

I have tried =hyperlink(G4,F4) where the values are from cells, and it renders correctly in the cell, but just shows the alias as plain text in the email.

Answer 1

As the OP already realized, getValue() and getValues() return a string object for cells containing an hyperlink and the value returned when the object is concatenated is the link text that the OP referred as the alias.

We could use getFormula() and getFormulas() to get the cells formulas and to parse the URL from the them. See Extract the link text and URL from a hyperlinked cell for answers showing how to do this.