2

I have a number of URLs that I first convert from text to the HYPERLINK function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. Spreadsheet

First I run "Convert Hyperlink", which produces the HYPERLINK function but when I run "Convert Title" then I'm back at my old text URLs. I want the result to look like =HYPERLINK("url";"www.example.com"). Also the script doesn't differentiate between different top-level-domains, which I wanted to bypass by editing the script after a successful run, but is there another way?

I will include examples in case the Spreadsheet is not available in the future:
The sheet include various URLs from different websites in column A

http://100-jahre-musikverein.de/
http://145789.guestbook.onetwomax.de/?sn=12
http://165870.forumromanum.com/member/forum/entry_ubb.user_165870.2.1108698443.1108698443.1.viele_fotos_aus_indien-indienforum.html
http://2015.mercedescup.de/ENG/Media/Videos/Day6MercedesCup2015/
etc

The result should look like this:

=HYPERLINK("http://165870.forumromanum.com/member/forum/entry_ubb.user_165870.2.1108698443.1108698443.1.viele_fotos_aus_indien-indienforum.html";"http://165870.forumromanum.com")
=HYPERLINK("http://145789.guestbook.onetwomax.de/?sn=12";"http://145789.guestbook.onetwomax.de")

So that it looks in Spreadsheet just like this:

http://165870.forumromanum.com
http://145789.guestbook.onetwomax.de

But still leads to the original link when clicked on.

The script I used to convert the unclickable text-links to the =HYPERLINK-function is:

function converttolink() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  var row   = 1;  
  var val   = sheet.getRange(row, 1).getValue();  
  while (val != "!")  
    if (val == "")  
    {  
      row++;  
      val = sheet.getRange(row, 1).getValue();  
    }  
    else  
    {  
      var val   = sheet.getRange(row, 1).getValue();  
      var h_string = '=HYPERLINK("'+val+'";"'+val+'")';  
      cell = sheet.getRange(row, 1).setValue(h_string);  
      row++  
    }  
}

With the second script I try to edit the part after the ; in the HYPERLINK function with this script:

function converttitle() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  var row   = 1;  
  var url   = sheet.getRange(row, 1).getValue();  
  while (url != "!")  
    if (url == "")  
    {  
      row++;  
      url = sheet.getRange(row, 1).getValue();  
    }  
    else  
    {  
      var url = sheet.getRange(row, 1).getValue();  
      myurl= url.replace("'.de'\\?.*?html", ".de");  
      cell = sheet.getRange(row, 1).setValue(myurl);  
      row++  
    }  
}

I realized while writing, that the getValue function will only get the actual URL text and not the Hyperlink function and therefore it only writes url-text back into the cell, just like it was before the conversion into the HYPERLINK function.

I guess it would be easier to truncate the URLs first and write it in a second column and then change the first script to use the full and the truncated URL to form the HYPERLINK function.

  • 1
    As the linked file could be inaccessible in the future for different reasons, please add the code to the question. Also add the a couple of examples for which your scripts works correctly and couple that don't. In the case of the last, include the expected result. – Rubén Feb 10 '16 at 15:24
2

I added an example on your sheet but you could do this with a single formula and not have to run a script at all:

=iferror(arrayformula(REGEXEXTRACT(A1:A;"^(?:https?:\/\/)?(?:www\.)?([^\/]+)")))
  • The OP just want to change the display text, not the URL of the link. – Rubén Feb 11 '16 at 10:23
  • I used your formula in a second column, just like you did in my Spreadsheet, and created a second variable to use this column as the title display text in the =HYPERLINK-function. I don't know if I should edit my post to show the new script? – GaRe Feb 11 '16 at 11:47
  • @GaRe: Once you get helpful answers, OPs should minimize the edits of their questions, especially those edits will make the answers to be completely obsolete. Sometimes instead of doing major changes to a question, will be better to post a new question. – Rubén Feb 15 '16 at 19:26
2

Short answer

As the OP already realized, instead of using getValue() and setValue, getFormula() and setFormula() should be used to get/set the formula of a cell.

Alternative

As was mentioned by Aurielle Perlmann in her answer, there's no need for a script.

If you don't feel comfortable with regular expressions, use the following formula

=ArrayFormula(
  IFERROR(
    HYPERLINK(
      A1:A,
      MID(A1:A,
        FIND("//",A1:A)+2,
        FIND("/",A1:A&"/",11)-FIND("//",A1:A)-2
      )
    )
  )
)

Explanation

  • Assuming that new URLs will be added in the future, the whole column is used as the the reference.
  • FIND() will return an error if "/" isn't in the string. Assuming that could be URL including only the domain, a an "/" was added to the end.
  • MID() takes the substring between the first character after "//" and before the first "/" after the 11 position. 11 is used as all valid web resources should have a length equal or greater than eleven characters (=LEN("http://")+LEN("a.de") returns 11).
  • IFERROR() will prevent to fill the empty rows to be filled with errors.
  • ARRAYFORMULA() makes that the formula be calculated using all the cells in the references.

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.