Fetch User Data from Excel and fill into Custom field automatically when user Enter Emp iD only.

[Vikrant Yadav]

Hi Guys,

Need Help

I have an Excel field on Share Drive containing User details like EmpID , Email , Username etc... 

In JIRA i have created the all field with Email, empID , Username etc. In JIRa when User input EmpID only than all other details like username,email get filled automatically picking data from excel. Can you please suggest how can i achieve this. Any solution please

I have scriptrunner plugin installed. Please suggest me all possible solution. 

[DPKJ · Accepted solution]

@Vikrant Yadav  I can give you couple of suggestions,

1. Using ScriptRunner (@Nic_Brough__Adaptavist_ is authority here)
   • You can write post functions where you can do some database/file look-up, and fill-in data for remaining fields.
2. Using custom add-on
   • This require some Atlassian development experience
   • You can create special custom field type plugin,
   • Or you can simply add custom Javascript + API, to allow filling in data in field while editing/creating issue.

I hope this clarify some information.

[Vikrant Yadav]

Hi @DPKJ  Thanks for the response. 

I need this on Create Screen. 

Do i need any java script in field description ?

Can you please help me script syntax or any example of the script so that i can modify it accordingly and use. As i am not a developer not much knowledge of scripting. 

Thanks

Vikrant Yadav

[DPKJ]

No, you don't need any Javascript in field description (script doesn't work here).

I can help you with small add-on but might need 3-4 days for this.

Better way for you is to look for options in ScriptRunner using Post functions. I think that will be easier for time being.

[Martin Bayer [MoroSystems, s.r.o.]]

hi @Vikrant Yadav another solution would be to use Behaviours functionality (field conversion) https://scriptrunner.adaptavist.com/4.3.5/jira/behaviours-conversions.html

which can help you to convert simple text field to field which can be filled with values based on the REST API data.

I agree with @DPKJ that development experience is needed to do this as you need to

• read binary file from Share Drive
• parse data from XLSX
• use data in your custom fields
  • insert data to custom field options (and eventually dynamically update them based on the changes in XLSX)
  • OR
  • implement REST API which will provide values from XLSX file and implement field conversion

I don't know what the use case is. If the list of values in excel file is stable, you can write one time script which will insert values as options of your custom fields. It would not be so difficult, but if you need something "smarter", you will probably need some Atlassian / Java developer.

[Vikrant Yadav]

Hi @Martin Bayer [MoroSystems, s.r.o.]  Thanks for the solution. 

Can you please suggest, how can i call xlsx file using REST API, file is on sharepoint site. 

Thanks,

Vikrant

[Vikrant Yadav]

Hi @Martin Bayer [MoroSystems, s.r.o.]  excel is stable , but is having 4000 users data.  script is becoming large, due to large data.. for each value in need to define value. i have created script for 3 users only and used 3 fields only. Actual data if for 4000 users and 10 fields .

So according to this script, i have to create else if statement for each 4000 users.

Script will cross 100k Lines. 

Is it possible to modify this script or any other script in which field value will be picked according to sequence.

Like users enter 1st emp id so script will pick first value from Emp IDphone and Emp Name field.    

Please suggest!

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField
import com.onresolve.jira.groovy.user.FieldBehaviours
import com.atlassian.jira.issue.customfields.manager.OptionsManager

import com.onresolve.jira.groovy.user.FieldBehaviours
import groovy.transform.BaseScript
@BaseScript FieldBehaviours fieldBehaviours

def fieldA = getFieldById("customfield_35651")
def fieldB = getFieldById("customfield_35652")
def fieldC = getFieldByName("Employee Mobile No.")

def optionsManager = ComponentAccessor.getOptionsManager()

def customFieldB = ComponentAccessor.getCustomFieldManager().getCustomFieldObject("customfield_35652")
def fieldConfigB = customFieldB.getRelevantConfig(getIssueContext())
def optionsB = optionsManager.getOptions(fieldConfigB)
def optionBI = optionsB.find {it.value == "Vikrant Yadav"}
def optionBII = optionsB.find {it.value == "Happy Singh"}
def optionBIII = optionsB.find {it.value == "Nisha"}

def customFieldC = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Employee Mobile No.")
def fieldConfigC = customFieldC.getRelevantConfig(getIssueContext())
def optionsC = optionsManager.getOptions(fieldConfigC)
def optionC1 = optionsC.find {it.value == "123456789"}
def optionC2 = optionsC.find {it.value == "1235235435456"}
def optionC3 = optionsC.find {it.value == "None"}

if (fieldA.getValue() == "1190168") {
fieldB.setFormValue(optionBI.optionId)
fieldC.setFormValue(optionC1.optionId)
}
else if (fieldA.getValue() == "1190169") {
fieldB.setFormValue(optionBII.optionId)
fieldC.setFormValue(optionC2.optionId)
}
else if (fieldA.getValue() == "1190129") {
fieldB.setFormValue(optionBIII.optionId)
fieldC.setFormValue(optionC3.optionId)
}

[Vikrant Yadav]

@Martin Bayer [MoroSystems, s.r.o.]  Mate please suggest

[Martin Bayer [MoroSystems, s.r.o.]]

hi @Vikrant Yadav unfurtunatelly I never used to work on getting data from XLSX file places on SharePoint so I'm not familiar with libraries which can be used in groovy for this purpose.

But my suggestion to simplify your situation (if XLSX is stable and you don't have developers to complete this task) is to download XLSX file, save it as CSV file. Then you can use this groovy library to load data from CSV and use them in Behaviours or PostFunction

https://code-maven.com/groovy-read-csv-file

The problem might be that in Behaviours you will have to load all 4000 rows which can slow it down, so using postfunction would be better or you will have to program some kind of cache, but it is also more advanced development task.

So my suggestion in steps is:

1. save your XLSX file as CSV
2. copy CSV file to your server (directory which can be accessed by Jira process)
3. prepare one time script which will put all Emp ID values as values of some single value picker custom field
4. prepare form with Emp ID field
5. implement PostFunction which will
   1. read Emp ID from the form
   2. read CSV file and finds row (data) based on the selected Emp ID
   3. write searched data to required fields

I hope it will help you and I'm sorry we can't help you more, but this community forum is more about giving advices and not programming complete solutions :)

Let me know if these information were helpful.

[Vikrant Yadav]

Hey @Martin Bayer [MoroSystems, s.r.o.]  Thanks for the solutions :) 

Information is very very helpful, thanks a lot!

[Vikrant Yadav · Accepted solution]

Hi @Alexey Matveev @Ravi Sagar _Sparxsys_ @DPKJ @Kristian Walker _Adaptavist_ @Nic Brough -Adaptavist- @Martin Bayer [MoroSystems, s.r.o.]  

Can you please suggest.

[Julie d'Antin [Elements]]

Hello @Vikrant Yadav ,

I see you're looking to fetch user data from an Excel file and populate a custom field in Jira. This is a common need when integrating external data with Jira issues, and I’d love to help you achieve this smoothly!

Did you have a look at Elements Connect for Jira ? 

With our app, you can connect your Jira instance to external data sources, including Excel files, and dynamically populate custom fields based on that data.  It's worth a try if you want to achieve your goal with less work and an easier maintenance (no scripting)...

Cheers, 

Julie