Add a function to utilize Method: spreadsheets.values.append

[WJurecki]

I would like to be able to use the google API Method: spreadsheets.values.append to add rows to an existing table in a google sheet

Create a function Add-GSSheetValues

I attempted to use the existing Export-GSSheet but this requires me to determine and specify the exact range where the new values will be inserted. Using the google API Method: spreadsheets.values.append an entire sheet can be specified in the range and the API "finds" the end of an existing table and places the appended data in appropriate rows.

Either I have entirely missed how to add rows to the end of a table or this capability is not currently existing in PSGSuite.

I have taken the liberty to copy and modify the code for Export-GSSheet to create a new function Add-GSSheetValues and have tested it for my limited use case.

Please note that I used the API Enums rather than ValidateSet so options my be slightly different from the rest of PSGSuite. Obviously make these however you see fit and I will adapt to your released version (assuming you accept my Feature request.)

 function Add-GSSheetValues {
    <#
    .SYNOPSIS
    Append data after a table of data in a sheet.

    .DESCRIPTION
    Append data after a table of data in a sheet. Accepts either an Array of objects/strings/ints or a single value

    .PARAMETER SpreadsheetId
    The unique Id of the SpreadSheet to Append data to if updating an existing Sheet

    .PARAMETER NewSheetTitle
    The title of the new SpreadSheet to be created

    .PARAMETER Array
    Array of objects/strings/ints to append to the SpreadSheet

    .PARAMETER Value
    A single value to update 1 cell with. 

    .PARAMETER SheetName
    The name of the Sheet to add the data to. If excluded, defaults to Sheet Id '0'. If a new SpreadSheet is being created, this is set to 'Sheet1' to prevent error

    .PARAMETER Style
    The table style you would like to export the data as

    Available values are:
    * "Standard": headers are on Row 1, table rows are added as subsequent rows (Default)
    * "Horizontal": headers are on Column A, table rows are added as subsequent columns

    .PARAMETER Range
    The input range is used to search for existing data and find a "table" within that range. Values are appended to the next row of the table, starting with the first column of the table.

    .PARAMETER Append
    If $true, skips adding headers to the Sheet

    .PARAMETER User
    The primary email of the user that had at least Edit rights to the target Sheet

    Defaults to the AdminEmail user

    .PARAMETER ValueInputOption
    How the input data should be interpreted

    Available values are:
    * "INPUT_VALUE_OPTION_UNSPECIFIED"
    * "RAW"
    * "USER_ENTERED"

    .PARAMETER IncludeValuesInResponse
    Determines if the update response should include the values of the cells that were updated. By default, responses do not include the updated values

    .PARAMETER Launch
    If $true, opens the new SpreadSheet Url in your default browser

    .EXAMPLE
    $array | Add-GSSheetValues -NewSheetTitle "Finance Workbook" -Launch


    #>
    [OutputType('Google.Apis.Sheets.v4.Data.Spreadsheet')]
    [cmdletbinding(DefaultParameterSetName = "CreateNewSheetArray")]
    Param
    (
        [parameter(Mandatory = $true,Position = 0,ParameterSetName = "UseExistingArray")]
        [parameter(Mandatory = $true,Position = 0,ParameterSetName = "UseExistingValue")]
        [String]
        $SpreadsheetId,
        [parameter(Mandatory = $false,Position = 0,ParameterSetName = "CreateNewSheetArray")]
        [parameter(Mandatory = $false,Position = 0,ParameterSetName = "CreateNewSheetValue")]
        [String]
        $NewSheetTitle,
        [parameter(Mandatory = $true,Position = 1,ValueFromPipeline = $true,ParameterSetName = "UseExistingArray")]
        [parameter(Mandatory = $true,Position = 1,ValueFromPipeline = $true,ParameterSetName = "CreateNewSheetArray")]
        [object[]]
        $Array,
        [parameter(Mandatory = $true,Position = 1,ParameterSetName = "UseExistingValue")]
        [parameter(Mandatory = $true,Position = 1,ParameterSetName = "CreateNewSheetValue")]
        [string]
        $Value,
        [parameter(Mandatory = $false)]
        [String]
        $SheetName,
        [parameter(Mandatory = $false,ParameterSetName = "UseExistingArray")]
        [parameter(Mandatory = $false,ParameterSetName = "CreateNewSheetArray")]
        [ValidateSet('Standard','Horizontal')]
        [String]
        $Style = "Standard",
        [parameter(Mandatory = $false)]
        [ValidateNotNullOrEmpty()]
        [Alias('SpecifyRange')]
        [string]
        $Range,
        [parameter(Mandatory = $false)]
        [switch]
        $Append,
        [parameter(Mandatory = $false,ValueFromPipelineByPropertyName = $true)]
        [Alias('Owner','PrimaryEmail','UserKey','Mail')]
        [string]
        $User = $Script:PSGSuite.AdminEmail,
        [parameter(Mandatory = $false)]
        #[ValidateSet("INPUT_VALUE_OPTION_UNSPECIFIED","RAW","USER_ENTERED")]
        #[string]
        [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ValueInputOptionEnum]        
        $ValueInputOption = [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ValueInputOptionEnum]::RAW,
        [parameter(Mandatory = $false)]
        [Switch]
        $IncludeValuesInResponse,
        [parameter(Mandatory = $false)]
        [Alias('Open')]
        [Switch]
        $Launch,
        [parameter(Mandatory = $false)]
        [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+InsertDataOptionEnum]
        $InsertDataOption = [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+InsertDataOptionEnum]::OVERWRITE,
        [parameter(Mandatory = $false)]
        [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseValueRenderOptionEnum]
        $responseValueRenderOption = [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseValueRenderOptionEnum]::FORMATTEDVALUE,
        [parameter(Mandatory = $false)]
        [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseDateTimeRenderOptionEnum]
        $responseDateTimeRenderOption = [Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseDateTimeRenderOptionEnum]::FORMATTEDSTRING

    )
    Begin {
        $values = New-Object 'System.Collections.Generic.List[System.Collections.Generic.IList[Object]]'
    }
    Process {
        if ($User -ceq 'me') {
            $User = $Script:PSGSuite.AdminEmail
        }
        elseif ($User -notlike "*@*.*") {
            $User = "$($User)@$($Script:PSGSuite.Domain)"
        }
        $serviceParams = @{
            Scope       = 'https://www.googleapis.com/auth/drive'
            ServiceType = 'Google.Apis.Sheets.v4.SheetsService'
            User        = $User
        }
        $service = New-GoogleService @serviceParams
        try {
            if ($Value) {
                $finalArray = $([pscustomobject]@{Value = "$Value"})
                $Append = $true
            }
            else {
                if (!$contentType) {
                    $contentType = $Array[0].PSObject.TypeNames[0]
                }
                $finalArray = @()
                if ($contentType -eq 'System.String' -or $contentType -like "System.Int*") {
                    $Append = $true
                    foreach ($item in $Array) {
                        $finalArray += $([pscustomobject]@{Value = $item})
                    }
                }
                else {
                    foreach ($item in $Array) {
                        $finalArray += $item
                    }
                }
            }
            if (!$Append) {
                $propArray = New-Object 'System.Collections.Generic.List[Object]'
                $finalArray[0].PSObject.Properties.Name | ForEach-Object {
                    $propArray.Add($_)
                }
                $values.Add([System.Collections.Generic.IList[Object]]$propArray)
                $Append = $true
            }
            foreach ($object in $finalArray) {
                $valueArray = New-Object 'System.Collections.Generic.List[Object]'
                $object.PSobject.Properties.Value | ForEach-Object {
                    $valueArray.Add($_)
                }
                $values.Add([System.Collections.Generic.IList[Object]]$valueArray)
            }
        }
        catch {
            $PSCmdlet.ThrowTerminatingError($_)
        }
    }
    End {
        try {
            if ($PSCmdlet.ParameterSetName -like "CreateNewSheet*") {
                if ($NewSheetTitle) {
                    Write-Verbose "Creating new spreadsheet titled: $NewSheetTitle"
                }
                else {
                    Write-Verbose "Creating new untitled spreadsheet"
                }
                $sheet = New-GSSheet -Title $NewSheetTitle -User $User -Verbose:$false
                $SpreadsheetId = $sheet.SpreadsheetId
                $SpreadsheetUrl = $sheet.SpreadsheetUrl
                $SheetName = 'Sheet1'
                Write-Verbose "New spreadsheet ID: $SpreadsheetId"
            }
            else {
                $sheet = Get-GSSheetInfo -SpreadsheetId $SpreadsheetId -User $User -Verbose:$false
                $SpreadsheetUrl = $sheet.SpreadsheetUrl
            }
            if ($SheetName) {
                if ($Range -like "'*'!*") {
                    throw "SpecifyRange formatting error! When using the SheetName parameter, please exclude the SheetName when formatting the SpecifyRange value (i.e. 'A1:Z1000')"
                }
                elseif ($Range) {
                    $Range = "'$($SheetName)'!$Range"
                }
                else {
                    $Range = "$SheetName"
                }
            }
            $body = (New-Object 'Google.Apis.Sheets.v4.Data.ValueRange' -Property @{
                Range = $Range
                MajorDimension = "$(if($Style -eq 'Horizontal'){'COLUMNS'}else{'ROWS'})"
                Values = [System.Collections.Generic.IList[System.Collections.Generic.IList[Object]]]$values
            })

            $request = $service.Spreadsheets.Values.Append($body,$SpreadsheetId,$Range)
            $request.valueInputOption = $ValueInputOption; 
            $request.insertDataOption = $InsertDataOption;
            $request.IncludeValuesInResponse = $IncludeValuesInResponse;
            $request.responseValueRenderOption = $responseValueRenderOption;
            $request.responseDateTimeRenderOption = $responseDateTimeRenderOption;

            Write-Verbose "Appending to Range '$Range' on Spreadsheet '$SpreadsheetId' for user '$User'"
            $request.Execute() | Add-Member -MemberType NoteProperty -Name 'User' -Value $User -PassThru | Add-Member -MemberType NoteProperty -Name 'SpreadsheetUrl' -Value $SpreadsheetUrl -PassThru
            if ($Launch) {
                Write-Verbose "Launching new spreadsheet at $SpreadsheetUrl"
                Start-Process $SpreadsheetUrl
            }
        }
        catch {
            if ($ErrorActionPreference -eq 'Stop') {
                $PSCmdlet.ThrowTerminatingError($_)
            }
            else {
                Write-Error $_
            }
        }
    }
}

[scrthq]

Thanks for digging in, @WJurecki !! The approach that I'd want to ideally take is just to alter the functionality of Export-GSSheet so that passing the -Append switch uses the Append() method instead. Any reasoning around why that may not be desirable for your case? Genuinely curious as there's definite value in using the Append() method for that case, but I could also see it introducing breaking changes maybe if there is anyone (like myself tbh) that are using it with the range defined already? May be a non-issue if the range provided is empty though, which would be the case for anyone using it the same way I am and calculating the start row before submitting the Export-GSSheet call.

[WJurecki]

@scrthq, I can certainly see the desire to wrap it into Export-GSSheet.

The only downside that I see would be breaking anyone's existing use.

While I believe Append() method would give the same result within the sheet when applied against an empty range, Append() gives a different Response Body than BatchUpdate() and this very-well may be the cause of breakage.

I'm willing to accept any solution, I just didn't want to have to keep figuring out where to add data to the end of a table and that's why I created the solution that I proposed.

[scrthq]

@WJurecki I appreciate the solid feedback!! I'll need to do some testing. If another function is needed, I'll likely extract the Values builder logic into a private function because that's a lot of painful code to have replicated across.

[scrthq]

hey @WJurecki - I didn't forget about this, hoping to finally start testing things soon. I'll keep you updated!

[scrthq]

Alrighty, did some digging after testing a bit and cleaned up the function you provided so it would pass the CI tests (wasn't much needed, thank you for that!). Add-GSSheetValues has been deployed in v2.35.0!