How to set a range to custom hyperlinks?

Question

Hello,

I need help with a script I have, I have automated my projects End of Day report but it was pointed out to me that our section that lists all the new bug reports today, does not hyperlink the bug numbers.
If there a way to script it so that the cells fill with variable hyperlinks?
Each bug has a URL that is basically, jira.com/ and after the slash is the bug number, so Proj-1000 or Proj-1234. So I wanted to take the bug number for the jira CSV and turn it into hyper links when the script fills the new bug section.

This is the code I had originally, sans hyperlinks.

function main(workbook: ExcelScript.Workbook) {

    let jiraSheet = workbook.getWorksheet("Jira");
    let outputSheet = workbook.getWorksheet("EoD");

    let bugNumber = [] = [];
    let severity = [] = [];
    let summary = [] = [];

	// sets up arrays that contain the bug number, severity and summary columns in Jira
    let bugNumberRange = jiraSheet.getRange("B2:B152").getValues();
    let severityRange = jiraSheet.getRange("F2:F152").getValues();
    let summaryRange = jiraSheet.getRange("D2:D152").getValues();

    // For loop to work through the bug number column in Jira
    for (let i = 0; i < bugNumberRange.length; i++) {

        let bugString = bugNumberRange[i][0] as string;
        let severityString = severityRange[i][0] as string;
        let summaryString = summaryRange[i][0] as string;
		
		let prefix = bugString.substring(0, 2);

        if (prefix == "XX" || "YY") {
            bugNumber.push(bugNumberRange[i]);
            severity.push(severityRange[i]);
            summary.push(summaryRange[i]);
		}
	}
    outputSheet.getRange("A94:A" + (94 + bugNumber.length - 1)).setValues(bugNumber);
    outputSheet.getRange("F94:F" + (94 + bugNumber.length - 1)).setValues(severity);
    outputSheet.getRange("B94:B" + (94 + bugNumber.length - 1)).setValues(summary);
}

This script currently works well, it successfully extracts the bug number, severity, and summery from the CSV export from our Jira filter.

I tried to incorporate the script found here, into the if section. I had added an array to try and store the hyperlink values, being "jira.com/" + bugString. Then added a outputSheet.getRange line that ".setHyperLink(LinkArray);" but setHyperLink does not accept an array.
https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.rangehyperlink?view=office-scripts

I am struggling to find information on how to propperly do this and woul greatly appreciate any help.

Answer 1

Hi Samantha, thanks for your question! Since setHyperlink doesn't take an array, you would have to set the hyperlink cell by cell. For example, you could add this code before the end of your for loop:

outputSheet.getRange("TODO: compute cell where you want to place the hyperlink").setHyperlink({
            address: "jira.com/" + bugString,
            screenTip: "Screen tip",
            textToDisplay: "Text to display"
});

Let me know if that helps or if you have any questions!