How to Delete all rows in Smartsheet using Proc Https

tsushilinvites

Hi,

How to delete all the rows using Proc Https and Method=delete at one shot without specifying rowid. I have 10K Plus rows. I don't want to loop multiple times.

Thanks,

Sushil

jmyzk_cloudsmart_jp

Hi @tsushilinvites

Using the Smartsheet Python SDK, I was able to run the following code and delete all rows in a sheet with 13188 rows:

sheet_id = 


# Get the sheet

sheet = smartsheet_client.Sheets.get_sheet(sheet_id)


# Collect all row IDs

row_ids = [row.id for row in sheet.rows]

print(len(row_ids))


# Define a batch size (I tested 500 with no success. 400 workded.)

batch_size = 400


# Delete rows in batches

for i in range(0, len(row_ids), batch_size):

    batch = row_ids[i:i + batch_size]

    smartsheet_client.Sheets.delete_rows(

        sheet_id,  # sheet_id

        batch      # batch of row_ids

    )

tsushilinvites

Hi,

Thanks for your response. I am using SAS Application. How to delete using SAS

jmyzk_cloudsmart_jp

@tsushilinvites

Unfortunately, I am not familiar with SAS or its API application.

Here is an AI-generated code that translates my Python code to SAS's PROC HTTP method. I can not guarantee that the code works. But you should try with a test sheet if you want to try.

%let sheet_id = <your_sheet_id>;

%let api_token = <your_api_token>;

%let base_url = https://api.smartsheet.com/2.0;


/* Step 1: Get the Sheet and Retrieve Row IDs */

filename response temp;

proc http

    url="&base_url./sheets/&sheet_id."

    method="GET"

    out=response

    headers "Authorization"="Bearer &api_token."

            "Accept"="application/json";

run;


/* Parse the response to extract row IDs */

libname resp json fileref=response;


proc sql noprint;

    select id into :row_ids separated by ' '

    from resp.rows;

quit;


/* Cleanup the JSON library */

libname resp clear;


/* Step 2: Delete Rows in Batches */

%let batch_size = 400;

%let row_count = %sysfunc(countw(&row_ids));


%macro delete_rows;

    %do i = 1 %to &row_count %by &batch_size;

        /* Create a batch of row IDs */

        %let batch = %sysfunc(catq(2,%sysfunc(scan(&row_ids, &i, ' '))));


        %do j = %eval(&i + 1) %to %eval(&i + &batch_size - 1);

            %let row = %scan(&row_ids, &j, ' ');

            %if &row ne %then %let batch = &batch.,&row;

        %end;


        /* Make the DELETE request */

        filename del_resp temp;

        filename del_req temp;


        data _null_;

            file del_req;

            put '{ "ids": [' "&batch." '] }';

        run;


        proc http

            url="&base_url./sheets/&sheet_id./rows"

            method="DELETE"

            in=del_req

            out=del_resp

            headers "Authorization"="Bearer &api_token."

                    "Content-Type"="application/json";

        run;


        /* Log response for debugging */

        data _null_;

            infile del_resp;

            input;

            put _infile_;

        run;


    %end;

%mend;


%delete_rows;

Here are the curl samples for Smartsheet API methods used in the code.

Get sheet

https://smartsheet.redoc.ly/tag/sheets#operation/getSheet

curl https://api.smartsheet.com/2.0/sheets/{sheetId}?level=2&include=objectValue \

-H "Authorization: Bearer JKlMNOpQ12RStUVwxYZAbcde3F5g6hijklM789"

Delete rows

https://smartsheet.redoc.ly/tag/rows#operation/delete-rows

curl 'https://api.smartsheet.com/2.0/sheets/{sheetId}/rows?ids={rowId1},{rowId2},{rowId3}&ignoreRowsNotFound=true' \