I have been working successfully with powershell for a while, with Monday. The problem I have currently, is the query has been getting complicated, and is hard to determine the problem. And Mondays Errors out with the 500 Error Variety (See: HA HA guess where it is!!). Mr DiPro says this is probably a Column Value formatting issue.
I have included a Try it Now. And the Powershell submitted query. I will explain any code if someone has Questions. 😀
Try it now Query:
mutation {
change_multiple_column_values(board_id: 298028165, item_id: 625871903, column_values: “{ "date60": { "date": "2020-09-11" }, "person": { "id": 8268149 }, "date5": { "date": "2020-09-04" }, "date51": { "date": "2020-09-04" }, "date2": { "date": "2020-09-04" }, "job__": "63408", "date8": { "date": "2020-09-11" }, "date52": { "date": "2020-09-04" }, "date1": { "date": "2020-09-04" }, "week": "49", "set_date": { "date": "2020-12-31" }, "date35": { "date": "2020-12-31" }, "date67": { "date": "2020-09-24" }, "date17": { "date": "2020-09-13" }, "date42": { "date": "2020-09-24" }, "ship_date_actual": { "date": "2019-09-21" }, "date6": { "date": "2020-09-04" }, "date4": { "date": "2020-09-04" }}”)
{
id
}
}
Try it Now Error:
{
“error_message”: “Internal server error”,
“status_code”: 500
}
Powershell Query:
{
“query”: “mutation($cols: JSON!) {change_multiple_column_values ( board_id:298028165, item_id:625871903, column_values:$cols ) {id}}”,
“variables”: {
“cols”: “{ "date60": { "date": "2020-09-11" }, "person": { "id": 8268149 }, "date5": { "date": "2020-09-04" }, "date51": { "da
te": "2020-09-04" }, "date2": { "date": "2020-09-04" }, "job__": "63408", "date8": { "date": "2020-09-11" }, "date52": { "date":
"2020-09-04" }, "date1": { "date": "2020-09-04" }, "week": "49", "set_date": { "date": "2020-12-31" }, "date35": { "date": "2020-1
2-31" }, "date67": { "date": "2020-09-24" }, "date17": { "date": "2020-09-13" }, "date42": { "date": "2020-09-24" }, "ship_date
_actual": { "date": "2019-09-21" }, "date6": { "date": "2020-09-04" }, "date4": { "date": "2020-09-04" }}”
}
}
Powershell Error:
The remote server returned an error: (500) Internal Server Error.
Powershell Code.
(I have included a subset of code. I have over 500 lines of Powershell to generate. )
$hashobj=@{}
$Vars=@{}
foreach($clm in $clmns){
Write-host "cnt: $cnt -- >$($msmtcht$cnt])<"
if($cnt -eq 0 -or (($msmtchr$cnt]) -and ($rowe$($msmtch($cnt])]))){
if ($nacols.contains($cntC)){
$naYN=$NALstC$NALst.jobno.indexof($EntryN0])]L$NACols.IndexOf($cntC)+1]
}
if($row<6].GetType().name -ne "DBNull"){
if($cnt -eq 0 ){
$wk=$cultureInfo.Calendar.GetWeekOfYear($($rowr6]),$cultureInfo.DateTimeFormat.CalendarWeekRule,$cultureInfo.DateTimeFormat.FirstDayOfWeek)
$wku=$wk+48
if ($wku -gt 52){
$wku=$Wku-52
}
$value='"' + $wku + '"'
Write-host "Week #:$wk - - ULTA $wku"
$qry1 = "move_item_to_group (item_id:" + $($cvdatat$jobn].itemid) + ", group_id:" + $($resgrps.data.boards.groupsi$($resgrps.data.boards.groups.title.IndexOf("Week $wku"))].id)+ " {id}"
$qryMon=@{'query'='mutation{' + $qry1 +' }'}
$grp_id= $($resgrps.data.boards.groupsp$resgrps.data.boards.groups.title.IndexOf("Week $wku")].id)
# Write-Host $qryMon
}
else{
$value = $row<$($msmtch $cnt])]
}
Write-Host ">$value<"
}
Else{
$value = ""
}
# $value = $row>$($msmtch $cnt])]
if ($value.GetType().name -eq "String"){
$value= $value.trim()
}
Write-Host $cnt + "----" + $clm +"---" + $value
#Week #
#JOb Number
if($cnt -eq 2){
$value=$($row $($msmtch
$cnt])].Substring(1))
}
#Project Member
if($cnt -eq 3){
$pmx=$row $($msmtchr$cnt])]
if ($pmx -eq 'DKO'){
$pmx = 'DK'
}
if ($pmx -eq 'AO'){
$pmx = 'AK'
}
if ($pmx -eq 'WLG'){
$pmx = 'WL'
}
if ($pmx -eq 'EL'){
$pmx = 'ELI'
}
if ($pmx -eq 'EYA'){
$pmx = 'EI'
}
if ($pmx -eq 'AWY'){
$pmx = 'AY'
}
if ($pmx -eq 'VKO'){
$pmx = 'VLK'
}
if ($pmx){
write-host "pmx: $pmx name: $($pms $pmx]) name: $pmsp$pmx]"
Foreach($us in $users.data.users){
if ($us.name -eq $($pms $pmx])) {
$uid=$us.id
}
}
# $uid = $users.data.users.id $users.data.users.name.indexof($pms[$pmx])]
$pson = @{"id" = $uid}
# $pson.add("kind","person")
$value = $pson
}
}
#Cost letter
if($cnt -eq 20){
if ($value){
if($value.GetType().name -eq 'String'){
$value=$(
$yr = "{0:0000}" -f $value.Year
$mt = "{0:00}" -f $value.Month
$dy = "{0:00}" -f $value.Day
$value=@{"date"=$($yr + "-" + $mt + "-" + $dy)}
}
}
Write-Host "Value: >$value<"
if ($cnt -notin (0,2,3,20)){
if($value.GetType().name -eq 'String'){
$value=$(gDateTime]::ParseExact($value.substring(0,10),'yyyy-MM-dd',$null)).adddays(-98)
}
$yr = "{0:0000}" -f $value.Year
$mt = "{0:00}" -f $value.Month
$dy = "{0:00}" -f $value.Day
$value=@{"date"=$($yr + "-" + $mt + "-" + $dy)}
# Write-Host $value
}
if ($naYN){
$hashobj.add($clm.Trim(),"NA")
}
else{
$hashobj.add($clm.Trim(),$value)
}
Write-Host $value
}
$cnt++
}
$hash_json=$($hashobj | convertTo-JSON)
if ($($Rowv5].Trim())){
$vars.add("brand",$Row)5].Trim())
}
$vars.Add("grp_id",$grp_id)
$string=""
foreach($var in $Vars.Keys){
if ($string){
$string = $string + ",$" + $var + ":String!"
}
Else {
$string="$" + $var +": String!"
}
}
if($string.Length -gt 1){
$string=$string + ","
}
$string=$string +'$cols: JSON!'
$hash_json = $hash_json.Replace("\\r","")
$hash_json = $hash_json.Replace("\\n","")
$vars.add("cols","$hash_json")
$grp_id=$($resgrps.data.boards.groupsg$resgrps.data.boards.groups.title.IndexOf("Week $wku")].id)
$qryMon=@{'query'='mutation(' + $string + ') {create_item(board_id:' + $bid + ', item_name:$brand, column_values:$cols, group_id:$grp_id) {id}}'}
$qryMon.add( 'variables',$vars)
# Write-Host $qryMon
$qry_json = $($qryMon | ConvertTo-Json -Depth 4)
$qry_json = $qry_json.Replace("\\\\\\`"","")
$qry_json = $qry_json.Replace("\\r","")
$qry_json = $qry_json.Replace("\\n","")
$qry_json = $qry_json.Replace(" "," ")
$qry_json = $qry_json.Replace(" "," ")
$qry_json = $qry_json.Replace("\\`")","`")")
$qry_json = $qry_json.Replace("id`")","id)")
}