[NEED HELP] Update-GSSheetValue: Value formatting issues for Body.Data in 2.0.0

[scrthq]

What's happening:

PSGSuite 1.2.1 uses REST API calls and JSON to build the Body payload, which allows pretty loose type constraints for values; a simple array of arrays in standard PowerShell works great (i.e. @(@('Name','Age'),@('Nate','30')). The issue with doing this via the Sheets .NET SDK is that it's reliant on exact type match and the Values property of the ValueRange object is looking for the following type:

System.Collections.Generic.IList < System.Collections.Generic.IList < object > >

Link to ValueRange documentation from Google: https://developers.google.com/resources/api-libraries/documentation/sheets/v4/csharp/latest/classGoogle_1_1Apis_1_1Sheets_1_1v4_1_1Data_1_1ValueRange.html

Excerpt from documentation describing the Values property:

The data that was read or to be written. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension. Each item in the inner array corresponds with one cell.

For output, empty trailing rows and columns will not be included.

For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string.

Sample code to replicate the issue and refactor to get working:

This is where I need help!

#region: Set sample variables
$Append = $false
$Array = @()
$Array += [PSCustomObject]@{
    Name = 'Nate'
    Age = '30'
    Location = 'Texas'
}
$Array += [PSCustomObject]@{
    Name = 'John'
    Age = '25'
    Location = 'California'
}
#endregion: Set sample variables


$values = New-Object System.Collections.Generic.List``1[System.Object]

if (!$Append) {
    $propArray = New-Object System.Collections.Generic.List``1[System.Object]
    $Array[0].PSObject.Properties.Name | ForEach-Object {
        $propArray.Add($_)
    }
    $values.Add([System.Collections.Generic.List``1[System.Object]]$propArray)
}
foreach ($object in $Array) {
    $valueArray = New-Object System.Collections.Generic.List``1[System.Object]
    $object.PSobject.Properties.Value | ForEach-Object {
        $valueArray.Add($_)
    }
    $values.Add([System.Collections.Generic.List``1[System.Object]]$valueArray)
}
$bodyData = (New-Object 'Google.Apis.Sheets.v4.Data.ValueRange' -Property @{
    Range = $Range
    MajorDimension = 'ROWS'
})

$bodyData.Values = $values

One of the errors when attempting to build the object:

New-Object : The value supplied is not valid, or the property is read-only. Change the value, and
then try again.
At line:1 char:14
+ ... bodyData = (New-Object 'Google.Apis.Sheets.v4.Data.ValueRange' -Prope ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [New-Object], Exception
    + FullyQualifiedErrorId : SetValueException,Microsoft.PowerShell.Commands.NewObjectCommand

Another attempt and another error:

#region: Set sample variables
$Append = $false
$Array = @()
$Array += [PSCustomObject]@{
    Name = 'Nate'
    Age = '30'
    Location = 'Texas'
}
$Array += [PSCustomObject]@{
    Name = 'John'
    Age = '25'
    Location = 'California'
}
#endregion: Set sample variables

$Values = [System.Collections.Generic.List[System.Collections.Generic.List[Object]]]::new()
if (!$Append) {
    $propArray = [System.Collections.Generic.List[Object]]::new()
    $Array[0].PSObject.Properties.Name | ForEach-Object {
        $propArray.Add($_)
    }
    $values.Add($propArray)
}
foreach ($object in $Array) {
    $valueArray = [System.Collections.Generic.List[Object]]::new()
    $object.PSobject.Properties.Value | ForEach-Object {
        $valueArray.Add($_)
    }
    $values.Add($valueArray)
}

$bodyData = (New-Object 'Google.Apis.Sheets.v4.Data.ValueRange' -Property @{
    Range = $Range
    MajorDimension = 'ROWS'
})

$bodyData.Values = $values




Exception setting "Values": "Cannot convert the "System.Collections.Generic.List`1[System.Collections.Generic.List`1[System.Object]]" value of type
"System.Collections.Generic.List`1[[System.Collections.Generic.List`1[[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089]]" to type "System.Collections.Generic.IList`1[System.Collections.Generic.IList`1[System.Object]]"."
At line:37 char:1
+ $bodyData.Values = $values
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

[scrthq]

Still working on this, but any help would be greatly appreciated!

[indented-automation]

You're getting that when passing values to the constructor. What do you have in Range? No value supplied in the example.

Can't reproduce at this time.

[scrthq]

@indented-automation - An empty $Range wouldn't cause a false positive, it just won't store a value. Just got it sorted though actually! Working code thanks to @markekraus:

#region: Set sample variables
$Append = $false
$Array = @()
$Array += [PSCustomObject]@{
    Name = 'Nate'
    Age = '30'
    Location = 'Texas'
}
$Array += [PSCustomObject]@{
    Name = 'John'
    Age = '25'
    Location = 'California'
}
$Range = "Sheet1"
#endregion: Set sample variables

$Values = [System.Collections.Generic.List[System.Collections.Generic.IList[Object]]]::new()
if (!$Append) {
    $propArray = [System.Collections.Generic.List[Object]]::new()
    $Array[0].PSObject.Properties.Name | ForEach-Object {
        $propArray.Add($_)
    }
    $values.Add([System.Collections.Generic.IList[Object]]$propArray)
}
foreach ($object in $Array) {
    $valueArray = [System.Collections.Generic.List[Object]]::new()
    $object.PSobject.Properties.Value | ForEach-Object {
        $valueArray.Add($_)
    }
    $values.Add([System.Collections.Generic.IList[Object]]$valueArray)
}

$bodyData = (New-Object 'Google.Apis.Sheets.v4.Data.ValueRange' -Property @{
    Range = $Range
    MajorDimension = 'ROWS'
})

$bodyData.Values = [System.Collections.Generic.IList[System.Collections.Generic.IList[Object]]]$values