Truncating the Title in the HYPERLINK function in Google Spreadsheet

Question

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.

Answer 1

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\.)?([^\/]+)")))

Answer 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.