0
votes

I have a column of values in a spreadsheet that I want to create hyperlinks for (sometimes, 2 hyperlinks per cell). I manage to successfully do that with the following code:

function makeJiraLinks() {
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName('testing');
  let data = sheet.getDataRange().getValues();

  // Get 1-indexed column
  let col = 1 + data[0].indexOf('Jira ids');
  if (col == 0) {
    return;
  }

  for (let row = 1; row <= sheet.getMaxRows(); ++row) {
    let range = sheet.getRange(row, col);
    let text = '' + range.getValue();

    if (text.indexOf("TTSD-") === -1) {
      continue;
    }

    let builder = SpreadsheetApp.newRichTextValue();
    builder.setText(text);

    let re = /TTSD-\d+/g;
    let result;
    while (result = re.exec(text)) {
      let startIndex = result.index;
      let endIndex = startIndex + result[0].length;
      builder.setLinkUrl(startIndex, endIndex, 'https://jira.tools/browse/' + result[0]);
    }

    range.setRichTextValue(builder.build());
  }
}

What I don't like about this code is that it is inefficient in the sense that it's advancing the range one by one and running getValue() as many times as rows in the column.

What I want: Is there any way where I can get the whole column as an array and apply richtext directly to it so I just call getValues() once and setValues() once?

Thanks!

1

1 Answers

2
votes

I believe your goal as follows.

  • You want to retrieve a column which has the header of Jira ids at the 1st row.
  • You want to give the hyperlinks to the text of TTSD-\d+ in the retrieved row.
  • You want to reduce the process cost of your script.

Modification points:

  • In order to achieve your goal, I would like to propose to use getRichTextValues() and setRichTextValues(values). When these methods are used, I think that the process cost will be reduced.
  • Class RichTextValue has the method of getText(). By this, the cell value can be also retrieved. So, in this case, getValues and setValues are not required to be used.
  • Class RichTextValue has the method of copy(). By this, the original text style is not changed and the hyperlink can be added.

When above points are reflected to your script, it becomes as follows.

Modified script:

function makeJiraLinks() {
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName('testing');
  let [header, ...data] = sheet.getDataRange().getRichTextValues();
  let col = header.map(h => h.getText()).indexOf('Jira ids');
  let builders = data.map(r => {
    let text = r[col].getText();
    if (text.includes("TTSD-")) {
      let builder = r[col].copy();
      let re = /TTSD-\d+/g;
      let result;
      while (result = re.exec(text)) {
        let startIndex = result.index;
        let endIndex = startIndex + result[0].length;
        builder.setLinkUrl(startIndex, endIndex, 'https://jira.tools/browse/' + result[0]);
      }
      return [builder.build()];
    }
    return [r[col]];
  });
  sheet.getRange(2, col + 1, builders.length, 1).setRichTextValues(builders);
}

Note:

  • In this modified script, please use with enabling V8.

References: