Add-GSSheetValues
SYNOPSIS
Append data after a table of data in a sheet. This uses the native `Spreadsheets.Values.Append()` method instead of `BatchUpdate()`.
SYNTAX
CreateNewSheetArray (Default)
Add-GSSheetValues [[-NewSheetTitle] <String>] [-Array] <Object[]> [-SheetName <String>] [-Style <String>]
[-Range <String>] [-Append] [-User <String>] [-ValueInputOption <ValueInputOptionEnum>]
[-InsertDataOption <InsertDataOptionEnum>] [-ResponseValueRenderOption <ResponseValueRenderOptionEnum>]
[-ResponseDateTimeRenderOption <ResponseDateTimeRenderOptionEnum>] [-IncludeValuesInResponse] [-Launch]
[<CommonParameters>]
UseExistingValue
Add-GSSheetValues [-SpreadsheetId] <String> [-Value] <String> [-SheetName <String>] [-Range <String>] [-Append]
[-User <String>] [-ValueInputOption <ValueInputOptionEnum>] [-InsertDataOption <InsertDataOptionEnum>]
[-ResponseValueRenderOption <ResponseValueRenderOptionEnum>]
[-ResponseDateTimeRenderOption <ResponseDateTimeRenderOptionEnum>] [-IncludeValuesInResponse] [-Launch]
[<CommonParameters>]
UseExistingArray
Add-GSSheetValues [-SpreadsheetId] <String> [-Array] <Object[]> [-SheetName <String>] [-Style <String>]
[-Range <String>] [-Append] [-User <String>] [-ValueInputOption <ValueInputOptionEnum>]
[-InsertDataOption <InsertDataOptionEnum>] [-ResponseValueRenderOption <ResponseValueRenderOptionEnum>]
[-ResponseDateTimeRenderOption <ResponseDateTimeRenderOptionEnum>] [-IncludeValuesInResponse] [-Launch]
[<CommonParameters>]
CreateNewSheetValue
Add-GSSheetValues [[-NewSheetTitle] <String>] [-Value] <String> [-SheetName <String>] [-Range <String>]
[-Append] [-User <String>] [-ValueInputOption <ValueInputOptionEnum>]
[-InsertDataOption <InsertDataOptionEnum>] [-ResponseValueRenderOption <ResponseValueRenderOptionEnum>]
[-ResponseDateTimeRenderOption <ResponseDateTimeRenderOptionEnum>] [-IncludeValuesInResponse] [-Launch]
[<CommonParameters>]
DESCRIPTION
Append data after a table of data in a sheet. This uses the native `Spreadsheets.Values.Append()` method instead of `BatchUpdate()`. See the following link for more information: https://github.com/scrthq/PSGSuite/issues/216
EXAMPLES
EXAMPLE 1
Add-GSSheetValues -SpreadsheetId $sheetId -Array $items -Range 'A:Z'
Finds the first empty row on the Sheet and appends the $items array (including header row) to it starting at that row.
EXAMPLE 2
Add-GSSheetValues -SpreadsheetId $sheetId -Array $items -Range 'A:Z' -Append
Finds the first empty row on the Sheet and appends the $items array (excludes header row due to -Append switch) to it starting at that row.
PARAMETERS
-SpreadsheetId
The unique Id of the SpreadSheet to Append data to if updating an existing Sheet
Type: System.String
Parameter Sets: UseExistingValue, UseExistingArray
Aliases:
Required: True
Position: 1
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-NewSheetTitle
The title of the new SpreadSheet to be created
Type: System.String
Parameter Sets: CreateNewSheetArray, CreateNewSheetValue
Aliases:
Required: False
Position: 1
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-Array
Array of objects/strings/ints to append to the SpreadSheet
Type: System.Object[]
Parameter Sets: CreateNewSheetArray, UseExistingArray
Aliases:
Required: True
Position: 2
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False
-Value
A single value to update 1 cell with.
Type: System.String
Parameter Sets: UseExistingValue, CreateNewSheetValue
Aliases:
Required: True
Position: 2
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-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
Type: System.String
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-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
Type: System.String
Parameter Sets: CreateNewSheetArray, UseExistingArray
Aliases:
Required: False
Position: Named
Default value: Standard
Accept pipeline input: False
Accept wildcard characters: False
-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.
Type: System.String
Parameter Sets: (All)
Aliases: SpecifyRange
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False
-Append
If $true, skips adding headers to the Sheet
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False
-User
The primary email of the user that had at least Edit rights to the target Sheet
Defaults to the AdminEmail user
Type: System.String
Parameter Sets: (All)
Aliases: Owner, PrimaryEmail, UserKey, Mail
Required: False
Position: Named
Default value: $Script:PSGSuite.AdminEmail
Accept pipeline input: True (ByPropertyName)
Accept wildcard characters: False
-ValueInputOption
How the input data should be interpreted
Available values are: * "INPUT_VALUE_OPTION_UNSPECIFIED" * "RAW" * "USER_ENTERED"
Type: Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ValueInputOptionEnum
Parameter Sets: (All)
Aliases:
Accepted values: INPUTVALUEOPTIONUNSPECIFIED, RAW, USERENTERED
Required: False
Position: Named
Default value: RAW
Accept pipeline input: False
Accept wildcard characters: False
-InsertDataOption
How the input data should be inserted.
Available values are: * "OVERWRITE" * "INSERTROWS"
Type: Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+InsertDataOptionEnum
Parameter Sets: (All)
Aliases:
Accepted values: OVERWRITE, INSERTROWS
Required: False
Position: Named
Default value: OVERWRITE
Accept pipeline input: False
Accept wildcard characters: False
-ResponseValueRenderOption
Determines how values in the response should be rendered. The default render option is FORMATTEDVALUE.
Available values are: * "FORMATTEDVALUE" * "UNFORMATTEDVALUE" * "FORMULA"
Type: Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseValueRenderOptionEnum
Parameter Sets: (All)
Aliases:
Accepted values: FORMATTEDVALUE, UNFORMATTEDVALUE, FORMULA
Required: False
Position: Named
Default value: FORMATTEDVALUE
Accept pipeline input: False
Accept wildcard characters: False
-ResponseDateTimeRenderOption
Determines how dates, times, and durations in the response should be rendered. This is ignored if responseValueRenderOption is FORMATTEDVALUE. The default dateTime render option is SERIALNUMBER.
Available values are: * "SERIALNUMBER" * "FORMATTEDSTRING"
Type: Google.Apis.Sheets.v4.SpreadsheetsResource+ValuesResource+AppendRequest+ResponseDateTimeRenderOptionEnum
Parameter Sets: (All)
Aliases:
Accepted values: SERIALNUMBER, FORMATTEDSTRING
Required: False
Position: Named
Default value: FORMATTEDSTRING
Accept pipeline input: False
Accept wildcard characters: False
-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
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False
-Launch
If $true, opens the new SpreadSheet Url in your default browser
Type: System.Management.Automation.SwitchParameter
Parameter Sets: (All)
Aliases: Open
Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.