How to set a range to custom hyperlinks?

Samantha Gomes 40 Reputation points


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, 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") {
    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 "" + bugString. Then added a outputSheet.getRange line that ".setHyperLink(LinkArray);" but setHyperLink does not accept an array.

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

A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.
814 questions
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
925 questions
JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
691 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,850 questions
0 comments No comments
{count} votes

Accepted answer
  1. Michelle Ran 256 Reputation points Microsoft Employee

    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: "" + bugString,
                screenTip: "Screen tip",
                textToDisplay: "Text to display"

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

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful