Google Sheets URL – How to Copy URL from Cell and Reference in Script Formula

googlegoogle sheetsurl

I’ve been trying to get this script here to work for a while. Everything has been tested and functions as planned, only if I set the “text” variable to a fixed URL. (There are small kinks, like how it’ll reset the image each time I edit the row, but that isn’t the concern for now. I know how to iron that out later.)
For some reason I just cannot seem to retrieve a URL (no hyperlink formula) or any text for that matter, and have it be inserted with this formula. The text replace functions properly if I use a specific URL, for example, “var text = “put URL here”, but how can I have “var text” become a URL inside a cell and then inserted along with that formula? I have scoured the internet for hours and tried so many different variations on getValue, getLinkUrl, copying text, etc., as well as ways to set the value, but have had no luck.

The concept:
Easily adding hyperlinked images simply by pasting a URL into a cell.

My script:
[1]: https://i.stack.imgur.com/F1xkG.png

Best Answer

Your code will throw an error in sh.getRange(copycell).getLinkUrl() in line 7. Replace it with copycell.getRichTextValue().getLinkUrl() to solve the issue.

The code suggests that you are always pasting URLs in column F and always want to see the hyperlinked image in column H. It is unclear why you would need to use an onEdit(e) script for this in the first place. Try something like this in row 2 of a free column:

=arrayformula( iferror( hyperlink(F2:F, image(F2:F)) ) )

If the links you are pasting are not URLs but rich text links, use the RichTextLinks custom function and this formula:

=arrayformula( ( hyperlink( RichTextLinks("F2:F", F2:F), image(RichTextLinks("F2:F", F2:F)) ) ) )

Related Topic