如何用Python实现Smartsheet中Grid1数据覆盖更新GridM？

Root Cause of ValueError #

The error happens because each Cell object in the Smartsheet Python SDK requires a single integer for column_id, not a list. A Cell represents one specific cell in a row—you need to create a separate Cell instance for every column in the row, each with its own unique column ID.

Complete Workflow to Replace GridM Data #

Since you need to fully overwrite GridM’s existing data with Grid1’s content, follow these steps:

1. Set Up Authentication #

Initialize the Smartsheet client with your API token:

import smartsheet

SMARTSHEET_API_TOKEN = "your_api_token_here"
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_API_TOKEN)
smartsheet_client.errors_as_exceptions(True)

2. Fetch Sheet Details & Column Mappings #

Retrieve both sheets and create a mapping between Grid1’s column titles and GridM’s column IDs (critical because each sheet has unique column IDs):

# Replace with your actual sheet IDs
GRID1_SHEET_ID = 123456789
GRIDM_SHEET_ID = 987654321

grid1_sheet = smartsheet_client.Sheets.get_sheet(GRID1_SHEET_ID)
gridm_sheet = smartsheet_client.Sheets.get_sheet(GRIDM_SHEET_ID)

# Map Grid1 column titles to GridM column IDs
gridm_column_map = {col.title: col.id for col in gridm_sheet.columns}

3. Delete All Existing Rows in GridM #

Clear all rows from GridM first to ensure full replacement. Handle batch deletion (Smartsheet limits batch size to 100 rows per request):

# Get all row IDs from GridM
gridm_row_ids = [row.id for row in gridm_sheet.rows]

# Delete rows in chunks of 100
for i in range(0, len(gridm_row_ids), 100):
    batch_ids = gridm_row_ids[i:i+100]
    smartsheet_client.Sheets.delete_rows(GRIDM_SHEET_ID, batch_ids)

4. Prepare Grid1 Rows for GridM #

Convert Grid1’s rows into the format required for GridM using the column mapping:

prepared_rows = []

for grid1_row in grid1_sheet.rows:
    cells = []
    for grid1_cell in grid1_row.cells:
        # Get matching GridM column ID via column title
        column_title = grid1_cell.column_title
        gridm_col_id = gridm_column_map.get(column_title)
        
        if gridm_col_id:
            cells.append({
                "column_id": gridm_col_id,
                "value": grid1_cell.value,
                "display_value": grid1_cell.display_value
            })
    
    prepared_rows.append({"cells": cells})

5. Batch Add Rows to GridM #

Add the prepared rows to GridM in batches (max 100 rows per request):

# Add rows in chunks of 100
for i in range(0, len(prepared_rows), 100):
    batch_rows = prepared_rows[i:i+100]
    response = smartsheet_client.Sheets.add_rows(GRIDM_SHEET_ID, batch_rows)
    print(f"Added {len(response.result)} rows to GridM")

Key Notes #

• Column Mapping: Always use the target sheet’s (GridM) column IDs when creating cells—never reuse source sheet (Grid1) column IDs, as they are unique per sheet.
• Batch Limits: Smartsheet API restricts batch operations (add/delete rows) to 100 items per request. The code above handles this by splitting data into chunks.
• Error Handling: Enabling errors_as_exceptions(True) raises exceptions for API errors, which you can catch and handle based on your use case.

内容的提问来源于stack exchange，提问作者ralhpq1