kategorijā Skripti

Update Jira issues from MS Excel spreadsheet using Powershell script (updated: 13.05.2019.)

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:

  1. Set Jira server address in “update_issues.ps1” file.
  2. Update data in “data_for_issues.xlsx”
  3. Set custom field configuration according to your needs in “update_issues.ps1”
  4. Launch “update_issues.bat”, enter your Jira credentials and wait for script to complete.
  5. 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

Komentēt

Komentēt