Auto hyperlink in multiple line/multiple links cell

Question

Big file (+1k rows) imported from a CSV file. file has a column with file addresses like:

http://www.whatever.whatever/file1.jpg

some rows have more than one file address in said column, separated by "line breaks", so some rows are

http://www.whatever.whatever/file2.jpg
http://www.whatever.whatever/file3.jpg
http://www.whatever.whatever/file4.jpg

and so on...

The rows with 1 address are auto-transformed into a hyperlink by google sheets, which is exactly what I want. The rows with more than one file address are left as is, which is the problem. How can I auto-link the rows with more than one file address without having to go 1 by 1? Thanks.

Answer 1

You can refer to this sample code:

function addHyperlink(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var links = sheet.getRange(1,1,sheet.getLastRow(),1).getValues().flat();

  links.forEach((cellLink,index) => {
    Logger.log(cellLink);
    Logger.log(cellLink.includes('\n'));
    
    //Check if cell value has multiple links (contains line break)
    if(cellLink.includes('\n')){
      var tmpUrl = cellLink.split('\n');
      var rText = SpreadsheetApp.newRichTextValue().setText(cellLink);
      var startOffset = 0;
      tmpUrl.forEach(url=>{
        Logger.log(url);
        Logger.log(url.length);
        Logger.log(startOffset);
        rText.setLinkUrl(startOffset,(startOffset+url.length),url);
        startOffset = startOffset + url.length + 1;
      });
      var value = rText.build();
      sheet.getRange(index + 1,1).setRichTextValue(value);
    }
  });

}

What it does?

1. Get all the cell values in Column A. Change 2-d array into 1-d array values using array.flat()
2. Check each values if it contains a line break using array.includes()
3. If multiple url found, split each url into a temporary array variable.
4. Create a RichTextValueBuilder using SpreadsheetApp.newRichTextValue(). Set the text based on the original cell's value using setText(text)
5. Loop each individual url to get its url length to be used to set the hyperlink's start offset and end offset using setLinkUrl(startOffset, endOffset, linkUrl). Adjust the start offset every time a url was processed.
6. Use build() to generate a RichTextValue. Set the cell range rich text value using setRichTextValue(value)

Output:

Before:

After: