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!