IMPORTRANGE: multiple hyperlinks within one cell, importing this cell data into another sheet and re-CodePudding

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:

Sheet Name: "Imported" & Sheet URL:

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

CodePudding user response：

You can extract the urls by a custom function in addition of the IMPORTRANGE

function extractUrls(range) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var rngAddress = activeformula.match(/\((.*)\)/).pop().trim(); 
  var urls = activeSht
    .getRange(rngAddress)
    .getRichTextValue()
    .getRuns()
    .reduce((array, e) => {
      var url = e.getLinkUrl();
      if (url) array.push(url);
      return array;
    }, []);
  return ([urls])
}

CodePudding user response：

I've been working on this for quite a while, but luckily Mike was able to provide a partial solution of extracting the URL.

This one sets the URL that was extracted from the custom function that Mike created into the value of the destination sheet.

function setHyperlink() {

  var ss = SpreadsheetApp.openById("Spreadsheet ID");
  var ds = SpreadsheetApp.getActiveSpreadsheet(); 
  var ssVal = ss.getSheetByName("Sheet Name").getRange("A2").getValue();
  var link1 = ss.getSheetByName("Sheet Name").getRange("B2").getValue();
  var link2 = ss.getSheetByName("Sheet Name").getRange("C2").getValue();
  
  var setdest = ds.getRange("A2");
  var link = SpreadsheetApp.newRichTextValue().setText(ssVal).setLinkUrl(15,19,link1).setLinkUrl(37,41,link2).build();
  
  setdest.setRichTextValue(link);
}

Source:

Destination: