Bulk delete sheet rows using Windows Powershell

apfautz

There is not currently a method for bulk deleting rows using the Smartsheet API (that I could find) so thought I would share what I came up with. Deleting single rows or small chunks of rows for sheets with thousands of rows is slow. I was able to use the example found here as inspiration... https://community.smartsheet.com/discussion/88154/how-to-delete-all-the-rows-of-a-sheet-using-python-at-once

The basic idea is to to do a PUT rest call that sets each rows parentID property equal to the row id of a parent row. I've tested this with a sheet that had 20000 single column rows. You can do this in smaller chunks if needed by setting the chunk size. It then deletes the parent rows which also deletes their child rows in a single DELETE rest call.

You will need to populate your API Key and the ID of the target sheet for the script to work.

Hopefully someone finds this helpful.

Powershell Script:

function DeleteSheetRows([object]$sheet){

    $sheetID = $sheet.id

 

    $rowsurl = "$APIurl/sheets/$sheetID/rows"

    $rows = $sheet.rows

    # bail if there are no rows in the sheet

    if ($rows.Length -eq 0) { return }

    # Deleting rows individually or in small chunks is very slow.

    # We will first move large chunks of rows as children under the first row in the chunk.

    # We will store the ids of the parent rows until all rows are nested.

    # Then we will delete the parent rows which also deletes the child rows.

    $r1 = 0

    $chunksize = 20000 # this is the largest number of rows tested. There is no limit that I know of on the smartsheet ARRAY[] size.

    $r2 = $r1 + $chunksize - 1

   

    $name = $sheet.name

    $parentIDs = [System.Collections.ArrayList]::new()

    while ($r1 -lt $rows.Count) {

        # retrieve subset of rows as a chunk.

        $IDs = $rows[$r1..$r2].id

        $rcount = $IDs.Count

        # setup children and parent row json data.

        $parentID = $IDs[0]

        $childIds = $IDs[1..$IDs.Count]

        $json = $childIDS | % {

            [PSCustomObject]@{

                id = $_;

                parentId = $parentID

            }

        } | ConvertTo-Json -Depth 10

        # move the child rows into the parentRow.

        $result = Invoke-RestMethod -uri $rowsurl -Method Put -Headers $put_headers -Body $json

        if ($result.message -eq "SUCCESS") {

            $rcount = $IDs.Count

            write-host $rcount "rows nested into parent"        

        } else {

            write-host $rcount " - " $result.message

        }

        # increment the chunk

        $r1 += $chunksize

        $r2 += $chunksize

        # store the parent id. We will delete all the parents (and their children) at a later step.

        $parentIDs.Add($parentID.Tostring())

    }

   

    # delete the parent rows

    $rurl = "$url/sheets/$sheetID/rows?ids="

    $rowstoDeleteUrl = $rurl + [string]::join(',', $parentIDs.ToArray())

    $rowstoDeleteUrl += "&ignoreRowsNotFound=true"

   

    $result = Invoke-RestMethod -Method Delete -Uri $rowstoDeleteUrl -Headers $get_headers

    if ($result.message -eq "SUCCESS") {

        $rcount = $rows.Count

        write-host $rcount "rows deleted from $name"

    }

    # cleanup (if you don't these can cause big memory leaks in large scripts)

    $sheet = $null

    $rows = $null

    $json = $null

    $IDs = $null

    $parentIDs = $null

    $childIds = $null

}

$timer = [Diagnostics.Stopwatch]::StartNew()

# set access variables for smartsheet

$apiKey = ""

$APIurl = "https://api.smartsheet.com/2.0"

$get_headers = @{"Authorization" = "Bearer " + $apiKey}

$put_headers = @{}

$put_headers.Add("Authorization", "Bearer " + $apiKey)

$put_headers.Add("Content-Type", "application/json")

$sheetID = ''

if ($apiKey -eq '' -or $sheetID -eq '') {

    throw "Make sure the apiKey and sheetID variables are populated before running script"

}

$surl = "$url/sheets/$sheetID"

$sheet = Invoke-RestMethod -uri $surl -Headers $get_headers

DeleteSheetRows $sheet

$timer.elapsed.totalseconds

$timer.Stop()