{"id":451,"date":"2019-04-16T09:12:26","date_gmt":"2019-04-16T07:12:26","guid":{"rendered":"http:\/\/drinkits.optimisti.lv\/?p=451"},"modified":"2019-04-16T09:12:26","modified_gmt":"2019-04-16T07:12:26","slug":"update-jira-issues-from-ms-excel-spreadsheet-using-powershell-script","status":"publish","type":"post","link":"https:\/\/www.drinkits.lv\/en\/2019\/04\/16\/update-jira-issues-from-ms-excel-spreadsheet-using-powershell-script\/","title":{"rendered":"Update Jira issues from MS Excel spreadsheet using Powershell script (updated: 13.05.2019.)"},"content":{"rendered":"<p><a href=\"https:\/\/www.drinkits.lv\/wp-content\/uploads\/2019\/04\/updated_issues.jpg\" data-lbwps-width=\"431\" data-lbwps-height=\"422\" data-lbwps-srcsmall=\"https:\/\/www.drinkits.lv\/wp-content\/uploads\/2019\/04\/updated_issues.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright wp-image-452\" src=\"http:\/\/drinkits.optimisti.lv\/wp-content\/uploads\/2019\/04\/updated_issues-300x294.jpg\" alt=\"\" width=\"253\" height=\"248\" srcset=\"https:\/\/www.drinkits.lv\/wp-content\/uploads\/2019\/04\/updated_issues-300x294.jpg 300w, https:\/\/www.drinkits.lv\/wp-content\/uploads\/2019\/04\/updated_issues.jpg 431w\" sizes=\"auto, (max-width: 253px) 100vw, 253px\" \/><\/a>I have created Powershell script which allows to set values in Jira issues using data from MS Excel spreadsheet.<\/p>\n<p>Script is based on following Powershell modules:<\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/RamblingCookieMonster\/PSExcel\">PSExcel<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/AtlassianPS\/JiraPS\">JiraPS<\/a><\/li>\n<\/ul>\n<p>Usage:<\/p>\n<ol>\n<li>Set Jira server address in &#8220;update_issues.ps1&#8221; file.<\/li>\n<li>Update data in &#8220;data_for_issues.xlsx&#8221;<\/li>\n<li>Set custom field configuration according to your needs in &#8220;update_issues.ps1&#8221;<\/li>\n<li>Launch &#8220;update_issues.bat&#8221;, enter your Jira credentials and wait for script to complete.<\/li>\n<li>Log file is created next to &#8220;update_issues.ps1&#8221; file.<\/li>\n<\/ol>\n<p>Download (<em>updated: 13.05.2019.<\/em>): <a href=\"https:\/\/www.drinkits.lv\/wp-content\/uploads\/2019\/04\/update_issues_PS_script_V2.zip\">update_issues_PS_script_V2.zip<\/a><\/p>\n<p><em>update_issues.ps1 (updated: 13.05.2019.):<\/em><\/p>\n<pre class=\"EnlighterJSRAW\">$ErrorActionPreference=\"SilentlyContinue\"\nStop-Transcript | out-null\n$ErrorActionPreference = \"Continue\"\nStart-Transcript -path .\\script\\update_issues.log -append\n\nImport-Module .\\script\\JiraPS\nImport-Module .\\script\\PSExcel\n\n####### Define some variables below ########\n\n#Excel data file\n$path = \".\\data_for_issues.xlsx\"\n\n#Jira adresss\nSet-JiraConfigServer -Server \"https:\/\/jira-server.com\"\n\n############################################\n\n$issues = new-object System.Collections.ArrayList\n\nforeach ($field in (Import-XLSX -Path $path -RowStart 1))\n \n{\n    $issues.add($field) | out-null\n}\n\n$issuesfull = @()\n\nforeach ($issue in $issues | Where { $_.key -and $_.key.Trim() })\n{\n    $i++\n    $issuesfull += ($issue.key)\n}\n\nWrite-Host \"Pieteikumi: \" $issuesfull  -ForegroundColor yellow\nWrite-Host \"Dati tiks rakst\u012bti\" $i \"pieteikumos. Tikl\u012bdz ievad\u012bsies lietot\u0101ja datus, t\u0101 s\u0101ksies datu rakst\u012b\u0161ana.\" -ForegroundColor green\nWrite-Host \"--------------------------------------\" -ForegroundColor green\n\n$cred = Get-Credential\n\nforeach ($issue in $issues | Where { $_.key -and $_.key.Trim() })\n{\n    write-host \"`n\"\n    $a++\n    Write-Host \"Izpildes statuss: \" $a \"\/\" $i -ForegroundColor gray\n    Write-Host \"Raksta datus pieteikum\u0101: \" $issue.key -ForegroundColor green\n    Write-Host \"Invest\u012bciju gads: \" $issue.ig\n    Write-Host \"Projekta uzs\u0101k\u0161anas gads: \" $issue.pug\n    Write-Host \"Ranga datums: \" $issue.rd.ToString('yyyy-MM-dd')\n    Write-Host \"Ranga vieta: \" $issue.vr\n\n## Custom field configuration\n\n    $fields = @{\n        customfield_11758 = @{\n            value = [string]$issue.ig\n        }\n        customfield_12031 = @{\n            value = [string]$issue.pug\n        }\n        customfield_24240 = $issue.rd.ToString('yyyy-MM-dd')\n        customfield_24241 = [int]$issue.vr\n    }\n\n    Try\n    {\n        Set-JiraIssue -Issue $issue.key -Fields $fields -Credential $cred\n        Write-Host \"Dati ierakst\u012bti pieteikum\u0101: \" $issue.key -ForegroundColor green\n    }\n    Catch\n    {\n        $ErrorMessage = $_.Exception.Message\n        $FailedItem = $_.Exception.ItemName\n        Write-Host \"Error: $ErrorMessage\" -ForegroundColor red\n    }\n    \n}\n\nWrite-Host -NoNewLine \"Datu rakst\u012b\u0161ana pieteikumos beigusies. Nospiediet jebkuru tausti\u0146u, lai izietu...\" -ForegroundColor yellow\n$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown');\nStop-Transcript<\/pre>","protected":false},"excerpt":{"rendered":"<a href=\"https:\/\/www.drinkits.lv\/en\/2019\/04\/16\/update-jira-issues-from-ms-excel-spreadsheet-using-powershell-script\/\" rel=\"bookmark\" title=\"Permalink to Update Jira issues from MS Excel spreadsheet using Powershell script (updated: 13.05.2019.)\"><p>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: PSExcel JiraPS Usage: Set Jira server address in &#8220;update_issues.ps1&#8221; file. Update data in &#8220;data_for_issues.xlsx&#8221; Set custom field configuration according to your needs in &#8220;update_issues.ps1&#8221; Launch &#8220;update_issues.bat&#8221;, enter your Jira [&hellip;]<\/p>\n<\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8],"tags":[47,48,52,79,80,90],"class_list":{"0":"post-451","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-skripti","7":"tag-jira","8":"tag-jira-api","9":"tag-jiraps","10":"tag-powershell","11":"tag-psexcel","12":"tag-script","13":"h-entry","14":"hentry"},"_links":{"self":[{"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/posts\/451","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/comments?post=451"}],"version-history":[{"count":0,"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/posts\/451\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/media?parent=451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/categories?post=451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.drinkits.lv\/en\/wp-json\/wp\/v2\/tags?post=451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}