Convert a list of URL pairs to a list of hyperlinks in Google Sheets cell using Apps Script-CodePudding

This is a refinement of my question How to add a list of hyperlinks in a CSV field to a cell in Google Sheets?. Since it turns out this can only be done through Google Sheets>Extensions Apps Script, I definitely do not have enough knowledge of javascript.

I do know:
a) I do NOT need to have Developer Credentials
b) I can apply the custom script to any directly-related Google Sheets
c) Most of the formatting can be done on my local CSV file through Python. That means I'm only concerned about the particular column where each cell is a list of URL/text pairs

I've gone with a simple formatting where each url/text pair is separated by "; " and the url and text are separated by ", ". The data in each cell is a list in the format:

http://goodreads.com/group/bookshelf?shelf=read, read; http://goodreads.com/group/bookshelf?shelf=genre-fairytale-folklore, genre-fairytale-f...; http://goodreads.com/group/bookshelf?shelf=genre-romance, genre-romance; http://goodreads.com/group/bookshelf?shelf=theme-humor, theme-humor

I would like my results to be a cell containing a list of hyperlinks read, genre-fairytale-folklore, genre-romance, theme-humor.

I've gotten as far as

function myFunction() {
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  for(n=0;n<values.length;n  ) {
    var cell = values[n][3];
    var cell_split = cell.split("; ");

It works to that point but cell_split.split(", ") doesn't work and cell_split.getLinkUrl() isn't a function. I'm not sure where to go from there. Thank you!

Other sources:
Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell by Tanaike

Add links to cell in Google Sheets

CodePudding user response：

I believe your current situation and your goal are as follows.

You have the values like http://goodreads.com/group/bookshelf?shelf=read, read; http://goodreads.com/group/bookshelf?shelf=genre-fairytale-folklore, genre-fairytale-f...; http://goodreads.com/group/bookshelf?shelf=genre-romance, genre-romance; http://goodreads.com/group/bookshelf?shelf=theme-humor, theme-humor each cell of the column "D".
You want to convert these values to read, genre-fairytale-folklore, genre-romance, theme-humor including URLs.
You want to achieve this using Google Apps Script.

When I saw your showing script, in this case, you split the cell value by ;. But, unfortunately, your script is not set the URLs. So, in this case, how about the following sample script?

Sample script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("D1:D"   sheet.getLastRow());
  var richTextValues = range.getRichTextValues().map(([d]) => {
    if ([";", ","].every(e => !d.getText().includes(e))) {
      return [d];
    }
    var { texts, urls } = d.getText().split(";").reduce((o, e) => {
      var [url, text] = e.trim().split(",");
      o.texts.push(text.trim());
      o.urls.push(url.trim());
      return o;
    }, { texts: [], urls: [] });
    var offset = 0;
    var delimiter = ", ";
    var r = d.copy().setText(texts.join(delimiter));
    urls.forEach((u, i) => {
      var start = offset;
      var end = offset   texts[i].length;
      r.setLinkUrl(start, end, u);
      offset = end   delimiter.length;
    });
    return [r.build()];
  });
  range.setRichTextValues(richTextValues);
}

When this script is run, the values are retrieved from the column "D" (this is from var cell = values[n][3]; of your script). And, each cell value is parsed to text and URL. And then, your expected result is obtained.

Sample script:

References: