CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Omen
Omen

Reputation: 139

Create unique hyperlink for all the values in the cell

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: https://domain.atlassian.net/browse/FAP-137076

Jira sample cell content: FAP-137076,FAP-125251,FAP-125547,FAP-125550,FAP-126136,FAP-126828,FAP-127351,FAP-132693,SMART-46475,HELL-2666

Current data example::

enter image description here

Desired outcome:

enter image description here

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

How can I write a loop to go through each cell and create unique hyperlinks for each Jira?

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)
      
    }
  }
}

Upvotes: 0

Views: 259

Answers (2)

Tanaike
Tanaike

Reputation: 201378

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.

enter image description here

References:

Added:

About your following new issue, in which you would like to:

modify this code to skip if one of the cells is empty? I am getting an error Exception: Illegal argument as soon as first cell is empty.

From your showing sample image, I believed that the column has all values. So, for your new issue, how about the following modified script?

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 text = r.getText();
    if (text) {
      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()];
    }
    return [r];
  });
  range.setRichTextValues(richTextValues);
}

Upvotes: 2

johndee31415
johndee31415

Reputation: 321

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())

Upvotes: 1

Related Questions