I have created Powershell script which allows to set values in Jira issues using data from MS Excel spreadsheet.
Script is based on following Powershell modules:
Usage:
- Set Jira server address in “update_issues.ps1” file.
- Update data in “data_for_issues.xlsx”
- Set custom field configuration according to your needs in “update_issues.ps1”
- Launch “update_issues.bat”, enter your Jira credentials and wait for script to complete.
- Log file is created next to “update_issues.ps1” file.
Download (updated: 13.05.2019.): update_issues_PS_script_V2.zip
update_issues.ps1 (updated: 13.05.2019.):
$ErrorActionPreference="SilentlyContinue"
Stop-Transcript | out-null
$ErrorActionPreference = "Continue"
Start-Transcript -path .\script\update_issues.log -append
Import-Module .\script\JiraPS
Import-Module .\script\PSExcel
####### Define some variables below ########
#Excel data file
$path = ".\data_for_issues.xlsx"
#Jira adresss
Set-JiraConfigServer -Server "https://jira-server.com"
############################################
$issues = new-object System.Collections.ArrayList
foreach ($field in (Import-XLSX -Path $path -RowStart 1))
{
$issues.add($field) | out-null
}
$issuesfull = @()
foreach ($issue in $issues | Where { $_.key -and $_.key.Trim() })
{
$i++
$issuesfull += ($issue.key)
}
Write-Host "Pieteikumi: " $issuesfull -ForegroundColor yellow
Write-Host "Dati tiks rakstīti" $i "pieteikumos. Tiklīdz ievadīsies lietotāja datus, tā sāksies datu rakstīšana." -ForegroundColor green
Write-Host "--------------------------------------" -ForegroundColor green
$cred = Get-Credential
foreach ($issue in $issues | Where { $_.key -and $_.key.Trim() })
{
write-host "`n"
$a++
Write-Host "Izpildes statuss: " $a "/" $i -ForegroundColor gray
Write-Host "Raksta datus pieteikumā: " $issue.key -ForegroundColor green
Write-Host "Investīciju gads: " $issue.ig
Write-Host "Projekta uzsākšanas gads: " $issue.pug
Write-Host "Ranga datums: " $issue.rd.ToString('yyyy-MM-dd')
Write-Host "Ranga vieta: " $issue.vr
## Custom field configuration
$fields = @{
customfield_11758 = @{
value = [string]$issue.ig
}
customfield_12031 = @{
value = [string]$issue.pug
}
customfield_24240 = $issue.rd.ToString('yyyy-MM-dd')
customfield_24241 = [int]$issue.vr
}
Try
{
Set-JiraIssue -Issue $issue.key -Fields $fields -Credential $cred
Write-Host "Dati ierakstīti pieteikumā: " $issue.key -ForegroundColor green
}
Catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
Write-Host "Error: $ErrorMessage" -ForegroundColor red
}
}
Write-Host -NoNewLine "Datu rakstīšana pieteikumos beigusies. Nospiediet jebkuru taustiņu, lai izietu..." -ForegroundColor yellow
$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown');
Stop-Transcript