Import Jira User Attributes to Assets in JSM Cloud
Platform Notice: Cloud Only - This article only applies to Atlassian products on the cloud platform.
Summary
When importing Jira User Attribute Values into Asset Objects, using a User's Display Name or Email Address will not help locate their User Account.
Solution
Currently, the Asset Importer expects Atlassian Account IDs or Customer IDs instead.
In the meantime, it is possible to export Atlassian Account IDs or Customer IDs and then fill a CSV with them for the Importer.
The process to acquire Atlassian Account IDs for your Site's Users is the following:
With this information, there are many ways to fill the CSV to be imported to Assets with the IDs in place of the Emails or Display Names. Excel, Google Sheets, or any other spreadsheet software with functionality similar to a VLOOKUP can achieve this.
For our purposes, we will use Google Sheets with Atlassian Account IDs as an example.
Let's look at VLOOKUP first, after moving the Column to the right of the Email Address column, as we'll be using that in our example.
The same process can be used for the Customer IDs.
After loading our Exported Users CSV to Google Sheets on a sheet named Sheet1, we will create a new Sheet (named Sheet2) within the same Workbook. We will fill that with our Assets Import CSV.
We will then enter the following formula into the Jira User Attribute column for Sheet2:
Search Right of the Column Formula
1
=VLOOKUP(B2, 'Sheet 1'!A2:B, 2, FALSE)B2 is the Cell in Sheet2 containing the User's Email Address.
'Sheet1'!A2:B is the range
VLOOKUPthat will search using the contents of Sheet2's B2.2 is the Column Number for the value to be returned, in this case, User ID from the Exported Users CSV, as we have moved it to the right of the email column.
FALSE is a boolean for whether the 1st column has been sorted.
The alternative, if moving the columns is not desired, would be this formula:
Searching Left of the Column Formula
1
=INDEX('Sheet1'!$A$1:$A, MATCH(B2, 'Sheet1'!$B$1:$B, 0))INDEX & MATCH are combined here to allow for searching to the left of a column.
'Sheet1'!$A$1:$A is the range for the value to be returned. In this case, the User ID.
B2 is the same as before. Sheet2's Email Address Cell to be searched for.
'Sheet2'!$B$1:$B is the range for the values to search. In this case, the Email Addresses.
0 is the type of search, which is an exact match search for this value.
Finally, once your formula is entered, simply fill Sheet2's User ID column with this formula by dragging down on the Fill Down square at the bottom right of the Cell containing it.
So long as the User exists in the Exported Users Sheet, the User ID should be filled.
Then, the Import to Asset should work as desired with the proper User Accounts being connected to the Objects via their Jira User Attribute being set.
Further Reading
Was this helpful?