Create a hyperlink from two columns, one with title and one with links, then retain only the hyperlink without losing the references

Question

Demo sheet here.

Say I have two columns, one with string literal names, and one with links (in text form)

A               B
Restaurant 1    http://www.link1.com
Restaurant 2    http://www.link2.com

I want to combine these, which I can easily do via HYPERLINK within another column, say C:

=HYPERLINK(B2, A2)

A               B                       C
Restaurant 1    http://www.link1.com    Combined Link+Name 1
Restaurant 2    http://www.link2.com    Combined Link+Name 2

My issue though, is I want to replace column A with my new combined hyperlink + name field - my ultimate goal is to combine my name and link columns into one and no longer need either.

A                           B
Combined Link+Name 1      http://www.link1.com
Combined Link+Name 2      http://www.link2.com

If I paste the combined column, or delete the existing columns, it results in removing at least one of the references in the link, resulting in either a REF error or the loss of the either the text or the link. In most cases like this, I could just paste values, but pasting values loses the link, which I need to keep. Is anyone aware of a way around this?

Answer 1

setFormula is not available within a custom function.

One of the workarounds suggested (by Google) is to use onEdit(e) instead of a custom function. Unbelievably, they don't regard this workaround as "crude"!

Anyway, this is a working example of some onEdit code to get a taste for things. Presumably you could use this, then cut/paste the results to your desired location. The sky is the limit ;-)

How to use:

• Copy the code into the Script Editor. In order to turn the function "Off" and "On", change the function name before and after you use it.
• Enter any character in any cell to the right of the Column C on the row containing the relevant title/url.
• The character will be deleted and a static HYPERLINK will be inserted in its place.

---

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);
  // Logger.log("DEBUG: the range is "+e.range.getA1Notation())

  // get the sheet details
  var thissheet = e.range.getSheet().getName();

  // make sure that you're on the correct sheet
  if (thissheet != sheetname ){
    // wrong sheet; do nothing
    Logger.log("DEBUG: wrong sheet; do nothing");
  }
  else{
    // this sheet = sheetname - proceed
    Logger.log("DEBUG: this sheet("+thissheet+") = sheetname("+sheetname+") - proceed");

    // get the row
    var row = e.range.getRow();
    // Logger.log("DEBUG: the row is "+row)

    // use the row to get the title and url
    var title = sheet.getRange(row,1).getValue();
    var url = sheet.getRange(row,2).getValue();
    // Logger.log("DEBUG: the title is "+title+", and the url is "+url)

    // setFormula in the range using the url and title from Columns A and B of the same row
    e.range.setFormula("=HYPERLINK(\""+url+"\", \""+title+"\")");
  } 
}