Powershell & API: Creating a Multiple Column_Value, multiple data type Query. 500 Error. :(

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. :smile:

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  -- >$($msmtch[$cnt])<"
    if($cnt -eq 0 -or (($msmtch[$cnt]) -and ($row[$($msmtch[$cnt])]))){
        if ($nacols.contains($cntC)){
             $naYN=$NALst[$NALst.jobno.indexof($Entry[0])][$NACols.IndexOf($cntC)+1]
        }


        if($row[6].GetType().name -ne "DBNull"){
            if($cnt -eq 0  ){
                $wk=$cultureInfo.Calendar.GetWeekOfYear($($row[6]),$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:" +  $($cvdata[$jobn].itemid) + ", group_id:" + $($resgrps.data.boards.groups[$($resgrps.data.boards.groups.title.IndexOf("Week $wku"))].id)+ " {id}"
                $qryMon=@{'query'='mutation{' + $qry1 +' }'}       
                $grp_id=  $($resgrps.data.boards.groups[$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[$($msmtch[$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: $pms[$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=$([DateTime]::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: >$value<"
        if ($cnt -notin (0,2,3,20)){
            if($value.GetType().name -eq 'String'){
                $value=$([DateTime]::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 ($($Row[5].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.groups[$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)")

  
}

Hey @stevemcmanus

That error usually has to do with the format of any JSON strings in your query. The first place I would check is that the right data structure is being used for each column value and if there any missing/extra commas and brackets. For example, this is what the query would look like:

mutation {
  change_multiple_column_values(board_id: 687276362, item_id: 687276376, column_values: "{\"date5\": {\"date\": \"2019-09-11\"}}") {
    id
  }
}

This being said, something we highly recommend is the use of variables in the query to make something like this a bit simple and easier to troubleshoot / update in the future. For example:

mutation ($datevalue1: JSON!){
  change_multiple_column_values(board_id: 687276362, item_id: 687276376, column_values: $datevalue1 ) {
    id
  }
}

Query Variables:

{
"datevalue1": "{\"date5\": {\"date\": \"2019-09-12\"}}"
}

Let me know if this helps!

-Danny

1 Like