UMN-Google

1.2.7

Remove-GSheetSheetRowColumnV2.ps1

                                <#

    Stashing this work, could never really get it to work because google removed lines and re-indexed too fast.  Also if the endIndex of one array was the same as the startIndex of another

    google's api would ignore it.

#>

#region Remove-GSheetSheetRowColumn

function Remove-GSheetSheetRowColumn

{

    <#

        .Synopsis

            Remove row(s) or column(s)

        .DESCRIPTION

            Remove row(s) or column(s)

        .PARAMETER accessToken

            access token used for authentication.  Get from Get-GOAuthTokenUser or Get-GOAuthTokenService

        .PARAMETER indexes

            Array of Arrays containing Index of row or column to start deleting

            .PARAMETER startIndex

            Index of row or column to start deleting

        .PARAMETER endIndex

            Index of row or column to stop deleting

        .PARAMETER dimension

            Remove Rows or Columns

        .PARAMETER sheetName

            Name of sheet in spreadSheet

        .PARAMETER spreadSheetID

            ID for the target Spreadsheet.  This is returned when a new sheet is created or use Get-GSheetSpreadSheetID

        .EXAMPLE  Remove-GSheetSheetRowColumn -accessToken $accessToken -sheetName "Sheet1" -spreadSheetID $spreadSheetID -dimension ROWS -startIndex 5 -endIndex 10            

    #>

    [CmdletBinding()]

    Param

    (

        [Parameter(Mandatory)]

        [string]$accessToken,

        [Parameter(Mandatory,ParameterSetName='Single')]

        [int]$startIndex,

        [Parameter(Mandatory,ParameterSetName='Single')]

        [int]$endIndex,

        [Parameter(Mandatory,ParameterSetName='Multiple')]

        [array]$indexes,

        [Parameter(Mandatory)]

        [ValidateSet("COLUMNS", "ROWS")]

        [string]$dimension,

        [Parameter(Mandatory)]

        [string]$sheetName,

        [Parameter(Mandatory)]

        [string]$spreadSheetID

    )

    Begin

    {

        $sheetID = Get-GSheetSheetID -accessToken $accessToken -spreadSheetID $spreadSheetID -sheetName $sheetName            

    }

    Process

    {

        [System.Collections.ArrayList]$requestArray = @()

        if ($indexes)

        {

            foreach ($index in $indexes)

            {

                if ($index[0] -eq $index[1]){$index[1]++}

                [void]$requestArray.add(@{"deleteDimension" = @{"range" = @{"sheetId" = $sheetID; "dimension" = $dimension; "startIndex" = $index[0]; "endIndex" = $index[1]}}})

            }

        }

        else

        {

            if ($startIndex -eq $endIndex){$endIndex++}

            [void]$requestArray.add(@{"deleteDimension" = @{"range" = @{"sheetId" = $sheetID; "dimension" = $dimension; "startIndex" = $startIndex; "endIndex" = $endIndex}}})

        }

        $json = @{requests=$requestArray} | ConvertTo-Json -Depth 20

        $suffix = "$spreadSheetID" + ":batchUpdate"

        $uri = "https://sheets.googleapis.com/v4/spreadsheets/$suffix"

        write-verbose -Message $json

        Invoke-RestMethod -Method Post -Uri $uri -Body $json -ContentType "application/json" -Headers @{"Authorization"="Bearer $accessToken"}

    }

    End{}

}

#endregion

# SIG # Begin signature block

# MIIVSwYJKoZIhvcNAQcCoIIVPDCCFTgCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB

# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR

# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUPjxqtPiQDa7Et7Q5ImNCtUop

# FvmgghA8MIIEmTCCA4GgAwIBAgIPFojwOSVeY45pFDkH5jMLMA0GCSqGSIb3DQEB

# BQUAMIGVMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQg

# TGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNV

# BAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVROLVVTRVJG

# aXJzdC1PYmplY3QwHhcNMTUxMjMxMDAwMDAwWhcNMTkwNzA5MTg0MDM2WjCBhDEL

# MAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UE

# BxMHU2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxKjAoBgNVBAMT

# IUNPTU9ETyBTSEEtMSBUaW1lIFN0YW1waW5nIFNpZ25lcjCCASIwDQYJKoZIhvcN

# AQEBBQADggEPADCCAQoCggEBAOnpPd/XNwjJHjiyUlNCbSLxscQGBGue/YJ0UEN9

# xqC7H075AnEmse9D2IOMSPznD5d6muuc3qajDjscRBh1jnilF2n+SRik4rtcTv6O

# KlR6UPDV9syR55l51955lNeWM/4Og74iv2MWLKPdKBuvPavql9LxvwQQ5z1IRf0f

# aGXBf1mZacAiMQxibqdcZQEhsGPEIhgn7ub80gA9Ry6ouIZWXQTcExclbhzfRA8V

# zbfbpVd2Qm8AaIKZ0uPB3vCLlFdM7AiQIiHOIiuYDELmQpOUmJPv/QbZP7xbm1Q8

# ILHuatZHesWrgOkwmt7xpD9VTQoJNIp1KdJprZcPUL/4ygkCAwEAAaOB9DCB8TAf

# BgNVHSMEGDAWgBTa7WR0FJwUPKvdmam9WyhNizzJ2DAdBgNVHQ4EFgQUjmstM2v0

# M6eTsxOapeAK9xI1aogwDgYDVR0PAQH/BAQDAgbAMAwGA1UdEwEB/wQCMAAwFgYD

# VR0lAQH/BAwwCgYIKwYBBQUHAwgwQgYDVR0fBDswOTA3oDWgM4YxaHR0cDovL2Ny

# bC51c2VydHJ1c3QuY29tL1VUTi1VU0VSRmlyc3QtT2JqZWN0LmNybDA1BggrBgEF

# BQcBAQQpMCcwJQYIKwYBBQUHMAGGGWh0dHA6Ly9vY3NwLnVzZXJ0cnVzdC5jb20w

# DQYJKoZIhvcNAQEFBQADggEBALozJEBAjHzbWJ+zYJiy9cAx/usfblD2CuDk5oGt

# Joei3/2z2vRz8wD7KRuJGxU+22tSkyvErDmB1zxnV5o5NuAoCJrjOU+biQl/e8Vh

# f1mJMiUKaq4aPvCiJ6i2w7iH9xYESEE9XNjsn00gMQTZZaHtzWkHUxY93TYCCojr

# QOUGMAu4Fkvc77xVCf/GPhIudrPczkLv+XZX4bcKBUCYWJpdcRaTcYxlgepv84n3

# +3OttOe/2Y5vqgtPJfO44dXddZhogfiqwNGAwsTEOYnB9smebNd0+dmX+E/CmgrN

# Xo/4GengpZ/E8JIh5i15Jcki+cPwOoRXrToW9GOUEB1d0MYwggWsMIIElKADAgEC

# AhByTV4gE9XCkUBV83xUiVRxMA0GCSqGSIb3DQEBCwUAMHwxCzAJBgNVBAYTAlVT

# MQswCQYDVQQIEwJNSTESMBAGA1UEBxMJQW5uIEFyYm9yMRIwEAYDVQQKEwlJbnRl

# cm5ldDIxETAPBgNVBAsTCEluQ29tbW9uMSUwIwYDVQQDExxJbkNvbW1vbiBSU0Eg

# Q29kZSBTaWduaW5nIENBMB4XDTE3MTIxNDAwMDAwMFoXDTIwMTIxMzIzNTk1OVow

# gcsxCzAJBgNVBAYTAlVTMQ4wDAYDVQQRDAU1NTQ1NTESMBAGA1UECAwJTWlubmVz

# b3RhMRQwEgYDVQQHDAtNaW5uZWFwb2xpczEYMBYGA1UECQwPMTAwIFVuaW9uIFN0

# IFNFMSAwHgYDVQQKDBdVbml2ZXJzaXR5IG9mIE1pbm5lc290YTEkMCIGA1UECwwb

# Q29tcHV0ZXIgYW5kIERldmljZSBTdXBwb3J0MSAwHgYDVQQDDBdVbml2ZXJzaXR5

# IG9mIE1pbm5lc290YTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMJO

# pCxPbvrVr9CVJCCUp+aVn2tPYOnKhS7nAl6JjY/TWI/LfiXz52t4Auix79zkAFAx

# zqAuR/vDd3mu6lRmCIsD0R81zhqhv7VWolObOYRt7wYX0V/Ftf9oOP4aYHl1M68W

# J/9nOUjhhstDFNWSclEhRHbI1xxFex1yuKphRlfUScbYi2R9alJXQoJcXI+88NEJ

# 1HRuqAMacEwnladOe/1l25yEfGtID7Hvsv53sOfggVBkwL68OitgayUFbHSCFZ/6

# vgW3GcYfT6XmX07bIupYJL8tG1FrZebEqXe+fob8OEpdxwGlmbaFjeyz5mw221UT

# tgK7+HaIeAoaHeMCecUCAwEAAaOCAdgwggHUMB8GA1UdIwQYMBaAFK41Ixf//wY9

# nFDgjCRlMx5wEIiiMB0GA1UdDgQWBBReCxIRJVVL0/J+bcTiUjQHM6KEgDAOBgNV

# HQ8BAf8EBAMCB4AwDAYDVR0TAQH/BAIwADATBgNVHSUEDDAKBggrBgEFBQcDAzAR

# BglghkgBhvhCAQEEBAMCBBAwZgYDVR0gBF8wXTBbBgwrBgEEAa4jAQQDAgEwSzBJ

# BggrBgEFBQcCARY9aHR0cHM6Ly93d3cuaW5jb21tb24ub3JnL2NlcnQvcmVwb3Np

# dG9yeS9jcHNfY29kZV9zaWduaW5nLnBkZjBJBgNVHR8EQjBAMD6gPKA6hjhodHRw

# Oi8vY3JsLmluY29tbW9uLXJzYS5vcmcvSW5Db21tb25SU0FDb2RlU2lnbmluZ0NB

# LmNybDB+BggrBgEFBQcBAQRyMHAwRAYIKwYBBQUHMAKGOGh0dHA6Ly9jcnQuaW5j

# b21tb24tcnNhLm9yZy9JbkNvbW1vblJTQUNvZGVTaWduaW5nQ0EuY3J0MCgGCCsG

# AQUFBzABhhxodHRwOi8vb2NzcC5pbmNvbW1vbi1yc2Eub3JnMBkGA1UdEQQSMBCB

# Dm9pdG1wdEB1bW4uZWR1MA0GCSqGSIb3DQEBCwUAA4IBAQBDUZXrDCpgWmdIOvJb

# bWBDLWj4s/g2jaTpVwbNYB+umoIQdWj+ookSXBVWrBi1FRZ3HRtcW8OdNy06wVHO

# TxAjFQ1ReyignxwNTnhVfbzlsr3VYG3t6Wg8tbI+AgAztg9IJ8XBomaBYRGYgZE7

# 0rI+Etxl2RL6BRycSS8NpL3S588tIB/2/2/8m5cANXyl8OJaHFKQzOqk8DO0oioJ

# EOVu/9vNBGLvASOnPK6YiZqjNbWS3KbMLmrwdNE+dmPmWZs97TDFYBR5/aRRZbWK

# 97N2xHpOyOqVPLpyEm4skM8aoSFCmgYvIcvC4KGOVJxo1+Yx7CLyDc5rPiV+SLct

# NrC9MIIF6zCCA9OgAwIBAgIQZeHi49XeUEWF8yYkgAXi1DANBgkqhkiG9w0BAQ0F

# ADCBiDELMAkGA1UEBhMCVVMxEzARBgNVBAgTCk5ldyBKZXJzZXkxFDASBgNVBAcT

# C0plcnNleSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxLjAs

# BgNVBAMTJVVTRVJUcnVzdCBSU0EgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkwHhcN

# MTQwOTE5MDAwMDAwWhcNMjQwOTE4MjM1OTU5WjB8MQswCQYDVQQGEwJVUzELMAkG

# A1UECBMCTUkxEjAQBgNVBAcTCUFubiBBcmJvcjESMBAGA1UEChMJSW50ZXJuZXQy

# MREwDwYDVQQLEwhJbkNvbW1vbjElMCMGA1UEAxMcSW5Db21tb24gUlNBIENvZGUg

# U2lnbmluZyBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMCgL4se

# ertqdaz4PtyjujkiyvOjduS/fTAn5rrTmDJWI1wGhpcNgOjtooE16wv2Xn6pPmhz

# /Z3UZ3nOqupotxnbHHY6WYddXpnHobK4qYRzDMyrh0YcasfvOSW+p93aLDVwNh0i

# LiA73eMcDj80n+V9/lWAWwZ8gleEVfM4+/IMNqm5XrLFgUcjfRKBoMABKD4D+TiX

# o60C8gJo/dUBq/XVUU1Q0xciRuVzGOA65Dd3UciefVKKT4DcJrnATMr8UfoQCRF6

# VypzxOAhKmzCVL0cPoP4W6ks8frbeM/ZiZpto/8Npz9+TFYj1gm+4aUdiwfFv+Pf

# WKrvpK+CywX4CgkCAwEAAaOCAVowggFWMB8GA1UdIwQYMBaAFFN5v1qqK0rPVIDh

# 2JvAnfKyA2bLMB0GA1UdDgQWBBSuNSMX//8GPZxQ4IwkZTMecBCIojAOBgNVHQ8B

# Af8EBAMCAYYwEgYDVR0TAQH/BAgwBgEB/wIBADATBgNVHSUEDDAKBggrBgEFBQcD

# AzARBgNVHSAECjAIMAYGBFUdIAAwUAYDVR0fBEkwRzBFoEOgQYY/aHR0cDovL2Ny

# bC51c2VydHJ1c3QuY29tL1VTRVJUcnVzdFJTQUNlcnRpZmljYXRpb25BdXRob3Jp

# dHkuY3JsMHYGCCsGAQUFBwEBBGowaDA/BggrBgEFBQcwAoYzaHR0cDovL2NydC51

# c2VydHJ1c3QuY29tL1VTRVJUcnVzdFJTQUFkZFRydXN0Q0EuY3J0MCUGCCsGAQUF

# BzABhhlodHRwOi8vb2NzcC51c2VydHJ1c3QuY29tMA0GCSqGSIb3DQEBDQUAA4IC

# AQBGLLZ/ak4lZr2caqaq0J69D65ONfzwOCfBx50EyYI024bhE/fBlo0wRBPSNe15

# 91dck6YSV22reZfBJmTfyVzLwzaibZMjoduqMAJr6rjAhdaSokFsrgw5ZcUfTBAq

# esReMJx9THLOFnizq0D8vguZFhOYIP+yunPRtVTcC5Jf6aPTkT5Y8SinhYT4Pfk4

# tycxyMVuy3cpY333HForjRUedfwSRwGSKlA8Ny7K3WFs4IOMdOrYDLzhH9JyE3pa

# RU8albzLSYZzn2W6XV2UOaNU7KcX0xFTkALKdOR1DQl8oc55VS69CWjZDO3nYJOf

# c5nU20hnTKvGbbrulcq4rzpTEj1pmsuTI78E87jaK28Ab9Ay/u3MmQaezWGaLvg6

# BndZRWTdI1OSLECoJt/tNKZ5yeu3K3RcH8//G6tzIU4ijlhG9OBU9zmVafo872go

# R1i0PIGwjkYApWmatR92qiOyXkZFhBBKek7+FgFbK/4uy6F1O9oDm/AgMzxasCOB

# MXHa8adCODl2xAh5Q6lOLEyJ6sJTMKH5sXjuLveNfeqiKiUJfvEspJdOlZLajLsf

# OCMN2UCx9PCfC2iflg1MnHODo2OtSOxRsQg5G0kH956V3kRZtCAZ/Bolvk0Q5Oid

# lyRS1hLVWZoW6BZQS6FJah1AirtEDoVP/gBDqp2PfI9s0TGCBHkwggR1AgEBMIGQ

# MHwxCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJNSTESMBAGA1UEBxMJQW5uIEFyYm9y

# MRIwEAYDVQQKEwlJbnRlcm5ldDIxETAPBgNVBAsTCEluQ29tbW9uMSUwIwYDVQQD

# ExxJbkNvbW1vbiBSU0EgQ29kZSBTaWduaW5nIENBAhByTV4gE9XCkUBV83xUiVRx

# MAkGBSsOAwIaBQCgeDAYBgorBgEEAYI3AgEMMQowCKACgAChAoAAMBkGCSqGSIb3

# DQEJAzEMBgorBgEEAYI3AgEEMBwGCisGAQQBgjcCAQsxDjAMBgorBgEEAYI3AgEV

# MCMGCSqGSIb3DQEJBDEWBBQvZvTxjpd9YVcJgbNkKSq2AsiffDANBgkqhkiG9w0B

# AQEFAASCAQC3p4ciuwtt3usuVXSxi/J+J3FEa38SqioyqOLA2tG2V2OiAde3K7lf

# 1ZeHqGDfDf9cHEnrZzMt2iXQ+jfMbh+gjyU/uivoC5yHmg4CRF4FXtMySJIFocXx

# fQpONCVVJImvZJrdBHFf02/lU59GVhn8bOQilAGlEdTj54RmjZBARKJE+mPRGCyq

# Oopp1eRoo9BKyzDYhYUFBbVXMGCdn9IJEKTycHtbQW6OomM7BHMu/CorPAMCyEEB

# vsGYnH5xnVxUAshe1QKsFIU/AMp2VA5DoRSQCRFrsGeNSxxgEZIpVUv95qeDfrhT

# Pvf30oyM6rEQpt3BGLOrtHLFS2h9PKCEoYICQzCCAj8GCSqGSIb3DQEJBjGCAjAw

# ggIsAgEBMIGpMIGVMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcT

# DlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsx

# ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVRO

# LVVTRVJGaXJzdC1PYmplY3QCDxaI8DklXmOOaRQ5B+YzCzAJBgUrDgMCGgUAoF0w

# GAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTkwMjIx

# MDM1MjQyWjAjBgkqhkiG9w0BCQQxFgQU6dwCzngm69YMR76QRWfxv7lXdCcwDQYJ

# KoZIhvcNAQEBBQAEggEAF48wLzDR/yp8ZUU9fcaAnILxc/TnsQQ2HpzTuBmPqlKO

# TmqyVkIfIgCuyjcNJR+3CfYc/Nlw91aIgEQkC+N2aQdOEic9fNsmFQmKo/x7EnEG

# ey4JqyeCIrlDecQ5KBf+w6dzX9hD4JlpxtCx654XdV/om+B/+GnhAh0GEWlWAd2u

# U/WUNry4s1lLzQFhaVaQp2/phGUHM3IajG4Y5opsljMXUMvSG2YLETlJGnX/3Jfy

# uHtdgdlG7xKscaRZy7e5wVJ5JBn91XmX4qlpcOnhQPCNOzIVuy5/WmkI2bIWno7M

# n/AJzH7q3deq8h475foaVoqzKupnPtjRIqClCB5J4g==

# SIG # End signature block