How to translate the result of a function into text while keeping the hyperlink?

Question

Google Sheets

Context . I have a large formula in the catalog that, when you insert the name of an item from the catalog, finds and displays a link to it. After finding the link, there is no need to re-search / update the link, and the formulas heavily load the file, so I wanted to replace the formulas with values.

The problem is that after pasting as link text, the cell loses its hyperlink, it can no longer be conveniently clicked to follow the link. You can click on a cell and then press Enter twice and the hyperlink will be created again, but for hundreds of lines this is not the best solution.

In this regard, the question is, how can you quickly replace the formulas with the result, while maintaining hyperlinks?

Answer 1

It can be like this, for example:

function createClickableUrls(sheetName="Лист1", rangeName="A:A"){
  let urlsRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(rangeName);
  let urls = urlsRange.getValues();
  let formulas = urls.map(row=>row.map(url=>url?`=HYPERLINK("${url}";"${url}")`:""));
  urlsRange.setValues(formulas);
};