Link JIra issue based on Summary in Google sheets

[Randi.Rogers]

I have a list of issues in a google document is there plugin or method to automatically insert a hyperlink to the issue? I would rather not go this process by hand, if anyone has tips please let me know. 

[Earl McCutcheon · Accepted solution]

Hi @Randi.Rogers ,

You flagged this post as unknown hosting type and the automated solutions for generating hyperlinks back to the issues will be different depending if you are on Cloud or Server.

If you are using Jira cloud you can use the  "Jira Cloud for Sheets" add-on (this is a free app made by Atlassian for integration to google sheets) to import the data directly to a sheet the applications side bar to import the issue via the "Get issues from Jira" action, will automatically import the issue keys with hyperlink formatting pre populated in the fields for the issue keys like this:

Alternatively If you are on Jira Server the Tool mentioned above is currently a Cloud only option, so you would need to look into third party paid apps like "Google Sheets Integration" OR "Google Drive Exporter" that are Jira Server compatible app add-ons with a similar feature set for importing issues to the Google sheets application.

The above options would be methods to get these sheets populated with the hyperlinks on new data imports.  However, for your preexisting sheets with all the issue keys in the sheet directly you would still need to do some manual work but this can be done really quick with a few functions via a concatenate or hyperlink formulas to combine the base URL and path of the instance to quickly update and generate the hyperlink for all the issue keys with a formula like the following example showing 3 different approaches to generate the hyperlinks depending on how you want the link to come out, where I have Column C containing a list of issue keys, I added a example Jira Cloud URL for the site to link back to in A1 and ran the formulas in column B.  Noting you can get as elaborate with this as needed for the desired final product to make this look a lot better for reporting purposes:

B2's is really simple and would be a good approach for a one time run as the formula has the URL string contained in the formula and only references the issue Key in column C as the variable input, if you needed to update the formula you would need to reapply the change to every formula and the output is the Full URL to the issue:

=CONCATENATE("https://exampleURL.atlassian.net/browse/",C2)

B3's formula is referencing the static Site URL value in A1, and the issue key in Cell C3, so if you need to modify the URL you only need to do it in a single location, and all formulas will update at the same time.  The output value is the full URL path to the issue.

=CONCATENATE($A$1,"/browse/",C3)

And B4 is a variation on the above formula but is uses the text value of the issue key from column C to replace the Full URL path but maintains the hyperlink, then I just duplicated the formula from B4 to the bottom of the column by double clicking the square in the bottom right of a cell when it is highlighted, that was done in the exe for B4 to the end of the list, NOTE: $A$1 will be an absolute referance A1 as a static Value the other fields will iterate as variable per line.

=HYPERLINK(CONCATENATE($A$1,"/browse/",C4),C4)

And you can also replace the second C value in the one above with any text you would like if you did not want the Issue key repeated, something like:

=HYPERLINK(CONCATENATE($A$1,"/browse/",C4),"Click here to go to the Issue")

OR

=HYPERLINK(CONCATENATE($A$1,"/browse/",C4),concatenate("Click here to go to issue: ",C4))

Again you could get really elaborate on this , linking formulas from other sheets, or outputting values to graphs and piviot tables etc... depending on what the final look needs to be if your going use this as a hand off report, or just keep it super simple if the look is not important. 

Also let me know if this does the trick for you of if you have any additional questions about the approaches I noted.

Regards,
Earl

[Mogsdad]

I wrote a small Google Apps Script helper for this purpose several years ago, you can copy it from this gist, paste it into the Project Editor (Tools - Script Editor), and change the jiraUrl for your environment.

Once you've saved the script and return to your spreadsheet tab, you'll find that any bare text you enter in any cell will be replaced with a HYPERLINK function if the text looks like a JIRA issue ID.

[Olga Rusnak]

hi @Randi.Rogers 

I have seen that you have accepted another answer as correct, but I guess you can do it a bit easier with Coupler.io Google Sheets add-on. So, yes, since it's not clear where you want to pull your data from, I would suggest you either use this guide for Jira server to Google Sheets, or this one to connect Jira Cloud to Google Sheets.

And re inserting a hyperlink to an issue, I described it in my recent article here on Atlassian Community (you can check out the spreadsheet template here => see the H column called "Links"). 

Here is an extract from it:

To create a link, I have just merged the URL to my Atlassian environment and the Issue Key using this formula:

=REPLACE("https://coupler-demo.atlassian.net/browse/",43,6,B4)

Let me know if you are eager to try out this option!