Create unique hyperlink for all the values in the cell-CodePudding

I have a Google Sheet report of Jira's that I would like to update and add a hyperlink. Each cell can contain multiple Jira numbers.

I want to generate a unique hyperlink for each of the Jira numbers in the cell.

Jira number: FAP-137076

Jira link format:

Desired outcome:

I have the following code, but I am stuck on the first Jira number link generating step with the following error.

Exception: Illegal argument.
buildRichLink   @ Code.gs:22

Please help me with the loop to go through each cell and create unique hyperlinks for each Jira. Thanks

Current code:

function buildJiraLink() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Sheet1")

  var LABEL = "JIRA#"

  const casesData = ws.getDataRange().getValues()
  var indexNo = casesData[0].indexOf(LABEL)

  for (var i = 1; i < casesData.length; i  ) { // offset the header row
    var cellValue = casesData[i][indexNo]
    var cellArray = cellValue.split(",")
    var startRow = i 1

    var offsetValue = 0
    for (var c=0; c < cellArray.length; c  ) {
      var tempCell = cellArray[c]   
      var RichTextValue = SpreadsheetApp.newRichTextValue()
        .setText(tempCell   ", ")
        .setLinkUrl(offsetValue,tempCell.length, "https://domain.atlassian.net/browse/"   tempCell)
        .build()

      ws.getRange(startRow,indexNo 1).setRichTextValue(RichTextValue)
      offsetValue = offsetValue   tempCell.length

      Logger.log(c   1   " "   tempCell)
      
    }
  }
}

CodePudding user response：

Modification points:

In your script, the object of RichTextValue is created to one hyperlink. From your showing expected situation, it is required to build the object of RichTextValue including multiple hyperlinks.
From your showing your expected result, it seems that you wanted to change the delimiter from , to , . In this case, it is also required to consider it.
And, in your script, setRichTextValue is used in a loop. In this case, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

function buildJiraLink() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Sheet1");
  var LABEL = "JIRA#";
  const casesData = ws.getDataRange().getValues();
  var indexNo = casesData[0].indexOf(LABEL);

  // I modified below script.
  const delimiter = ", "; // Please set your expected delimiter of outout situation.
  const range = ws.getRange(2, indexNo   1, ws.getLastRow() - 1);
  const richTextValues = range.getRichTextValues().map(([r]) => {
    const ar = r.getText().split(",").map(e => e.trim());
    const copied = r.copy().setText(ar.join(delimiter));
    let start = 0;
    ar.forEach((e, i) => {
      copied.setLinkUrl(start, start   e.length, `https://domain.atlassian.net/browse/${e}`);
      start  = e.length   delimiter.length;
    });
    return [copied.build()];
  });
  range.setRichTextValues(richTextValues);
}

Testing:

When this modified script is run, the following result is obtained.

References:

CodePudding user response：

You need to move the creation of the RichTextValue outside of the loop and only call setLinkUrl inside the loop. Something like:

var richTextValueBuilder = SpreadsheetApp.newRichTextValue().setText(cellArray.join(", "));
for (var c=0; c < cellArray.length; c  ) {
  var tempCell = cellArray[c]   
  
  richTextValueBuilder.setLinkUrl(offsetValue,tempCell.length, "https://domain.atlassian.net/browse/"   tempCell);

  offsetValue = offsetValue   tempCell.length   2 //account for comma and space
  Logger.log(c   1   " "   tempCell)
}
ws.getRange(startRow,indexNo 1).
    setRichTextValue(richTextValueBuilder.build())