1

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.

Current Attempts

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.