Python Smartsheet API, copying contents of one sheet to a new sheet

Question

I'm running into an issue with smartsheet API. I can modify contents of a source smartsheet just fine, but I need to copy certain contents from one sheet into a new blank sheet. The main issue comes with rows and columns having assigned IDs whereas the destination sheet is completely blank. Is there any simple way to create rows with as many columns as needed without having to specify column_ids, row parent_ids, etc?

I tried adding a row copied from the old sheet to the new sheet. That didn't work due to column_id mismatch. I tried setting every column_id of the copied row to the corresponding column_ids in the new sheet, but then the row had a parent_id mismatch

Answer 1

To implement the scenario you've described will be a two-step process.

Step 1: Copy Sheet

First, use the Copy Sheet operation to create an empty copy of the source sheet.

By default, the new copy of the sheet will be an empty sheet that's structured like the source sheet (e.g., same number of columns, column types & names, etc.) -- but it will not contain any data or formatting, attachments, discussions, forms, filters, etc. from the source sheet.

If you DO want the new sheet to contain all of the same data & formatting, attachments, discussions, etc. from the source sheet, you can specify the include parameter in your API request to indicate which types of things you want to include. (See the API docs for details about valid values for this parameter.)

Step 2: Copy rows from Source sheet to the New sheet

Once you've created the new sheet, you can then use the Copy Rows to Another Sheet operation to copy specified rows from the source sheet to the new sheet.

Specify the include parameter in your request to indicate what types of things you want to copy to the destination sheet -- i.e., all, discussions, attachments, children. (See API docs for details about these parameter values.)

The following code example illustrates the two-step process I've described.

'''
STEP 1: Create a copy of the source sheet. 
        Just copy sheet structure -- don't include any data, formatting, attachments, discussions, etc.
        Name the new sheet My New Sheet and create it in the folder that has the specified folder_id.
'''
# specify the ID of the sheet to be copied
source_sheet_id = 2702602705784708

response = smartsheet_client.Sheets.copy_sheet(
    source_sheet_id,                                # sheet_id
    smartsheet.models.ContainerDestination({
        'destination_type': 'folder',               # folder, workspace, or home
        'destination_id': 7825486215702404,         # folder_id
        'new_name': 'My New Sheet'                  # name of the new sheet
    })
)

# specify the ID of the (newly created) sheet
destination_sheet_id = response.result.id

'''
STEP 2: 
    Copy specified rows from the source sheet to the destination sheet.
'''
source_sheet_row_ids = [409928880836484, 7165328321892228]

# copy rows from source sheet to (bottom of) destination sheet
# (include the attachments, children, and discussions)
response = smartsheet_client.Sheets.copy_rows(
    source_sheet_id,
    smartsheet.models.CopyOrMoveRowDirective({
        'row_ids': source_sheet_row_ids,
        'to': smartsheet.models.CopyOrMoveRowDestination({
            'sheet_id': destination_sheet_id
        })
    }), 
    'children'
)

In this example, my source sheet looks like this -- notice that the first row has 2 'child' rows, the second row contains a discussion, and last row contains an attachment:

And after running the code above -- which specifies the IDs of the first and last row in the source sheet as the rows to copy -- the (newly created) destination sheet looks like this:

Notice that the discussion and attachment from the source sheet weren't copied over to the destination sheet -- because my code specified that only children (i.e., child rows of the specified rows) be included in the data that's copied into the new sheet.

Finally, please note that a sheet created with the "Copy Sheet" operation will have all of the columns as the source sheet has. If there are certain columns that you don't want in the new (destination) sheet, you can either DELETE them after you've finished copying over all the rows you want to copy over -- or just HIDE them by setting the hidden property to true for columns you don't want to see in the destination sheet. (If you're going to be copying more rows into the destination sheet in the future, it needs to contain all the same columns as the source sheet where rows are copied from -- so you can't delete the column altogether else copy rows won't work in the future.)