0

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?

  • For anyone who might have already looked at the question, I added a link to a demo sheet. – Julian Drago Sep 21 at 17:27
  • Just hide/lock/protect/move col A and B ? – pjmg Sep 21 at 21:43
  • Wish I could do that, but I need it not to be pulled into something downstream. – Julian Drago Sep 22 at 5:22
  • If your downstream code only pulls data from Col A, perhaps create the combined link+name 1 column as an inserted column left of A (=HYPERLINK(C2, B2)) rather than col C – Nick Sep 22 at 20:17
  • Welcome. Refer Custom Google Spreadsheet Function Returning a Hyperlink or an Existing Formula and the reference to the Google Feature Request. There are a range of workarounds and alternatives, though no promises whether any will suit you. – Tedinoz Oct 2 at 11:51
0

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+"\")");
  } 
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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