Google-sheets – Google sheets, how to combine data from two cells, where there is a URL (web-link) in the cells, without loosing the link(url)

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-custom-function

I have tried to write a custom function in google sheets.

I tried to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (URL). The link is removed.

The code

function combine(...theArgs) {
  var result ="";
  var is_first = true;
  theArgs.forEach( function (value, index){
    if (! is_first) {
      result += "\n\n";
    }
    is_first=false;
    result += value;
  });
  return result;
}

Usage =combine(ref, ref) e.g. =combine(a1,b1)

I tried to use =hyperlink(url,text), this can combine the text and link from different cells, so I then tried =hyperlink(url1, text1) & hyperlink(url2, text2). The & is demoting the link to text (removes the url).

Acceptable answers:
– How to get a custom function to work.
– Use of built in function.
– Something else that has the same effect.

Best Answer

It don't think that it's possible to do what you are looking to do with a custom function, as in order to keep the hyperlinks, you will need to set the cell content by using Class RichText which, as far as I know, is not a supported data type for custom functions.

The supported data types for custom function output are string, number, boolean, Date and Array of any of the mentioned data types.

Related Solutions

Google-sheets – Circular dependency detected even while using IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Google-sheets – Making all cells in a column links from the values within the cell

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

enter image description here

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.