How can I create a hyperlink in the middle of cell text using a formula?

user_7608812 0
Viewed 391

Is there any way to programmatically concatenate text (e.g. using formulas) that includes hyperlinked text?

I am looking for a way to automate creation of documents that have links within long text descriptions in cells, e.g. invoices.

In Google Sheets, the HYPERLINK function in Google Sheets returns hyperlink enabled text and the CONCATENATE function allows stringing together or appending various texts.

Formula Result
=HYPERLINK("https://stackoverflow.com", "Stack Overflow") Stack Overflow
=CONCATENATE("SO: ", "https://", "stackoverflow.com") SO: https://stackoverflow.com (hyperlink not enabled)

When HYPERLINK is used within CONCATENATE, however, the hyperlinked text shows up as plain text.

Formula Result
=CONCATENATE("SO: ", hyperlink("https://stackoverflow.com", "Stack Overflow")) SO: Stack Overflow

Use of CONCATENATE as arg in HYPERLINK works as expected.

Formula Result
=HYPERLINK(CONCATENATE("https://", "stackoverflow.com"), "Stack Overflow") Stack Overflow
google-sheets-formula google-apps-script google-sheets
3 Answers

You can set multiple hyperlinks (as many as you wish) with this script (not by formula)

function multipleHyperLinks() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').setRichTextValue(SpreadsheetApp.newRichTextValue()
    .setText("go to ... stackoverflow")
    .setLinkUrl(10, 23, "https://stackoverflow.com/search?tab=newest&q=google%20sheets")
  .build());
};

RichTextValueBuilder

setLinkUrl(startOffset, endOffset, linkUrl)

enter image description here

By the way, if you want to retrieve all the links, use

function getMultipleLinks() {
  var sh = SpreadsheetApp.getActive()
  var rng = sh.getRange('A1')
  var richTexts = rng.getRichTextValue().getRuns()
  richTexts.forEach(function(richText){
    Logger.log('|'+richText.getText() + '| = ' + richText.getLinkUrl())
  })
}

edit

Here is an automatic solution based on onEdit(e) function :

Type in a cell : foo <link1|text1> foo <link2|text2> foo

for instance : go to ... <https://stackoverflow.com/|stackoverflow>

add 'Edit:' at the beginning to reverse

// mike.steelson
// example
// go to <https://stackoverflow.com/|stackoverflow> and <https://www.amazon.fr/|amazon> here
// add Edit: at the beginning to reverse
function onEdit(event){
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  var value = rng.getValue()

  if (value.substring(0,5).toLowerCase()=='edit:'){
    var richTexts = rng.getRichTextValue().getRuns()
    var txt=''
    richTexts.forEach(function(richText){
      if (richText.getLinkUrl()==null){
        txt+=richText.getText()
      }else{
        txt+= '<' +  richText.getLinkUrl() + '|' + richText.getText() + '>'
      }
    })
    rng.setValue(txt)
  } else {
    var occ1 = ExtractAllRegex(value,'<([^>]+)>',0)
    var occ2 = ExtractAllRegex(value,'>([^<]+)<',0)
    occ2.push('')
    if (sh.getName()=='mySheet' && occ1.length>0){
      var richText = SpreadsheetApp.newRichTextValue()

      var txt = value.split('<')[0]
      for (var i=0; i<occ1.length; i++){
        var arr = occ1[i].replace('<','').replace('>','').split('|')
        txt += arr[1]
        txt += occ2[i].replace('<','').replace('>','')
      }
      txt += value.split('>')[value.split('>').length-1]
      richText.setText(txt)

      var start = value.split('<')[0].length
      for (var i=0; i<occ1.length; i++){
        var arr = occ1[i].replace('<','').replace('>','').split('|')
        var len = arr[1].length
        richText.setLinkUrl(start,start+len,arr[0])
        start += len + occ2[i].replace('<','').replace('>','').length
      }

      rng.setRichTextValue(richText.build())

    }
  }
}
function ExtractAllRegex(input, pattern,groupId) {
  return Array.from(input.matchAll(new RegExp(pattern,'g')), x=>x[groupId]);
}
user_150193800

if this is your goal:

enter image description here

then this is possible to achieve only manually editing the cell (or with script). usage of any internal formula you think of will render it as "plain text" (eg. removing the hyperlink)

user_56326290

Unfortunately, not in that way. Hyperlink (via formula) will always include the whole text in the cell, but I recommend you, if possible, to arrange the whole text in the invoice to leave the hyperlink in a cell, with all the paragraphs around.

it's an idea of how I would try it.

user_116071270
Related