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

Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 30, 2020

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. 

3 answers

2 accepted

0 votes
Answer accepted
DPKJ
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 30, 2020

@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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 30, 2020

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 31, 2020

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__
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 31, 2020

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 1, 2020

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 2, 2020

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 2, 2020

@Martin Bayer _MoroSystems_ s_r_o__  Mate please suggest

Martin Bayer _MoroSystems_ s_r_o__
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 3, 2020

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.

Like Vikrant Yadav likes this
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 3, 2020

Hey @Martin Bayer _MoroSystems_ s_r_o__  Thanks for the solutions :) 

Information is very very helpful, thanks a lot!

0 votes
Answer accepted
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 30, 2020
0 votes
Julie d'Antin [Elements]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 18, 2020

Hello @Vikrant Yadav , did you have a look at Elements Connect for Jira ? 
Maybe you could achieve your goal with less code and an easier maintenance (no script)... 

Cheers, 

Julie from Elements 

Suggest an answer

Log in or Sign up to answer