Public/Sheets/Export-GSSheet.ps1
|
function Export-GSSheet { <# .SYNOPSIS Updates a Sheet's values .DESCRIPTION Updates a Sheet's values. Accepts either an Array of objects/strings/ints or a single value .PARAMETER SpreadsheetId The unique Id of the SpreadSheet to update if updating an existing Sheet .PARAMETER NewSheetTitle The title of the new SpreadSheet to be created .PARAMETER Array Array of objects/strings/ints to add to the SpreadSheet .PARAMETER Value A single value to update 1 cell with. Useful if you are tracking the last time updated in a specific cell during a job that updates Sheets .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 specific range to add the value(s) to. If using the -Value parameter, set this to the specific cell you would like to set the value of .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 | Export-GSSheet -NewSheetTitle "Finance Workbook" -Launch #> [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] $ValueInputOption = "RAW", [parameter(Mandatory = $false)] [Switch] $IncludeValuesInResponse, [parameter(Mandatory = $false)] [Alias('Open')] [Switch] $Launch ) Begin { 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 $values = New-Object 'System.Collections.Generic.List[System.Collections.Generic.IList[Object]]' } Process { 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" } } $bodyData = (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 }) $body = New-Object 'Google.Apis.Sheets.v4.Data.BatchUpdateValuesRequest' $body.ValueInputOption = $ValueInputOption $body.IncludeValuesInResponse = $IncludeValuesInResponse $body.Data = [Google.Apis.Sheets.v4.Data.ValueRange[]]$bodyData $request = $service.Spreadsheets.Values.BatchUpdate($body,$SpreadsheetId) Write-Verbose "Updating 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 $_ } } } } |