IMPORTRANGE: multiple hyperlinks within one cell, importing this cell data into another sheet and retaining separate clickable links

Question

I currently use a Google sheet to hold data x1 row per item. I use one particular cell to hold two separate hyperlinks within (x2 website addresses), both links are relevant/in relation to this item.

I want to keep the structure of having two separate clickable hyperlinks in one cell, but I also need to import this cell data (these two links) into another Google sheet, is there a way of using IMPORTRANGE and retaining these two separate hyperlinks (ensuring clickable & still x2 separate links within one cell), or converting them into hyperlinks when importing into another sheet?

Thank you in advance

I've created two dummy sheets with data for testing & to help visualise

Sheet Name: "Static" & Sheet URL: https://docs.google.com/spreadsheets/d/1JS40eNGUAmBqQJqmdX4PhWtm6GEVQP64CoxO_DooZYM/edit#gid=0

Sheet Name: "Imported" & Sheet URL: https://docs.google.com/spreadsheets/d/1rhnULIcbkSMCp8AONa7UwTQz77uHn443VuwYjty2xFs/edit#gid=0

I've used =IMPORTRANGE("1JS40eNGUAmBqQJqmdX4PhWtm6GEVQP64CoxO_DooZYM","Static1!A1:D")

To pull data from 'Static' sheet (tab: 'Static1') into 'Imported' (tab: 'Imported1')

I'm hoping to get clickable links in column 'D' of the 'Imported' sheet

I've added different variations i.e. the hyperlinks are renamed in 'Static' sheet as "Link 1" & "Link 2", I've added a few rows with full URLs addresses (no re-naming), and a couple with full URLs and with an empty line in between - I'm not too fussed with how they look to be honest (ideally it would be nice to have 'Link 1' & 'Link 2') but mainly just looking to have x2 imported URLs within same cell that remain/become clickable after importing

This is because I'll also be iframe/embedding the 'Imported' sheet afterwards.

Thank you

Answer 1

UPDATE:

Option 1: This is fairly straightforward. It just copies the Static1 sheet unto a destination sheet and names it Imported.

function createCopy2() {
  var ss = SpreadsheetApp.openById("1vM-0nBBlhVvRQ3vvXwkWlecENM7CVuv8iei3cl0uRQI"); 
  var ds = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Static1');
  sheet.copyTo(ds).setName('Imported');

}

Option 2 (incomplete):

This is what I have done so far, but this still throws an error Exception: Illegal Argument at this line var text = SpreadsheetApp.newRichTextValue().setText(linkval[i]).setLinkUrl(0,7,urls1[i]).setLinkUrl(7,13,urls2[i]).build();.

(P.S: Just wanted to throw this out here if in case someone would find this logic useful/not to waste my efforts practicing JS lol).

Kudos to Mike Steelson for providing a working answer.

 function createCopy() {

  //Creates a full copy of the source sheet without Hyperlinks (AKA importrange in script form)
  var ss = SpreadsheetApp.openById("1vM-0nBBlhVvRQ3vvXwkWlecENM7CVuv8iei3cl0uRQI"); 
  var sheet = ss.getSheets()[0].getSheetValues(1,1, ss.getLastRow(), ss.getLastColumn());
  var ds = SpreadsheetApp.getActiveSheet();
  ds.getRange(1,1, ss.getLastRow(), ss.getLastColumn()).setValues(sheet);
  Logger.log(sheet);

  var link = ds.getRange(2,4, ss.getLastRow(), 1);
  var linkval = link.getValues();
  var urls1 = ds.getRange(2,5, ss.getLastRow(), 1).getValues();
  var urls2 = ds.getRange(2,6, ss.getLastRow(), 1).getValues();

for(i=0; i < ss.getLastRow(); i++){
 var text = SpreadsheetApp.newRichTextValue().setText(linkval[i]).setLinkUrl(0,7,urls1[i]).setLinkUrl(7,13,urls2[i]).build();  
} 
}