如何使用Python复制Smartsheet中的单元格图片及数据

Got it, let's fix that cell image issue you're facing with Smartsheet! I’ve worked with Smartsheet’s API quite a bit, and the key thing to know here is that cell images are actually stored as inline attachments—not separate from the cell, but linked directly to it. That’s why your current code (which only pulls cell values) misses them. Let’s walk through how to adjust your code to capture these images alongside your data.

First, Understand the Difference #

Smartsheet has two types of attachments:

• Regular sheet/row attachments (the ones you’re not targeting)
• Inline cell attachments (type: INLINE_IMAGE)—these are the images embedded directly inside cells. We need to specifically filter for these.

Example Solution (Using Python SDK) #

I’ll assume you’re using the official Smartsheet Python SDK since it’s the most common tool for this. If you’re using another language, the logic translates similarly—you just need to use the equivalent API endpoints.

First, make sure you have the SDK installed:

pip install smartsheet-python-sdk

Here’s modified code that pulls cell data and downloads inline cell images, then links them to their respective cells in a CSV (or you can extend this to Excel):

import smartsheet
import os
import csv

# Initialize your Smartsheet client with your API key
smartsheet_client = smartsheet.Smartsheet("YOUR_SMARTSHEET_API_KEY")
smartsheet_client.errors_as_exceptions(True)  # Raise errors instead of returning them silently

# Configuration - update these values
TARGET_SHEET_ID = 1234567890  # Replace with your sheet's ID
DATA_OUTPUT_PATH = "./smartsheet_export.csv"
IMAGE_SAVE_FOLDER = "./smartsheet_cell_images"

# Create folder for images if it doesn't exist
os.makedirs(IMAGE_SAVE_FOLDER, exist_ok=True)

# Fetch the full sheet data
sheet = smartsheet_client.Sheets.get_sheet(TARGET_SHEET_ID)

# Prepare CSV structure - add an extra column for image paths
csv_header = [col.title for col in sheet.columns]
csv_header.append("Cell Image Filepaths")
csv_rows = [csv_header]

# Loop through each row and cell to extract data + images
for row in sheet.rows:
    row_values = []
    cell_image_paths = []
    
    for cell in row.cells:
        # Add the cell's value to our row data
        cell_val = cell.value if cell.value is not None else ""
        row_values.append(cell_val)
        
        # Check if this cell has any attachments
        if cell.attachments:
            # Filter only for inline cell images
            for attachment in cell.attachments:
                if attachment.type == "INLINE_IMAGE":
                    # Download the image file
                    attachment_data = smartsheet_client.Attachments.get_attachment(
                        sheet_id=TARGET_SHEET_ID,
                        row_id=row.id,
                        attachment_id=attachment.id
                    )
                    
                    # Generate a unique filename to avoid overwriting
                    filename = f"row_{row.id}_col_{cell.column_id}_{attachment.name}"
                    full_image_path = os.path.join(IMAGE_SAVE_FOLDER, filename)
                    
                    # Save the image to disk
                    with open(full_image_path, "wb") as img_file:
                        img_file.write(attachment_data.data)
                    
                    # Record the path to link with the cell
                    cell_image_paths.append(full_image_path)
    
    # Add all image paths for the row (separate with semicolons if multiple)
    row_values.append("; ".join(cell_image_paths))
    csv_rows.append(row_values)

# Save the combined data to CSV
with open(DATA_OUTPUT_PATH, "w", newline="", encoding="utf-8") as csv_file:
    writer = csv.writer(csv_file)
    writer.writerows(csv_rows)

print(f"Success! Data saved to {DATA_OUTPUT_PATH}")
print(f"Cell images saved to {IMAGE_SAVE_FOLDER}")

Key Details in This Code: #

1. Filtering for Inline Images: We check each cell’s attachments and only process those with type == "INLINE_IMAGE"—this ensures we don’t pull regular sheet/row attachments.
2. Unique Filenames: Using row ID + column ID in the filename prevents overwriting images from different cells that might have the same original name.
3. Linking Images to Data: The CSV gets an extra column with paths to the downloaded images, so you can easily map each image back to its cell.

Bonus: Insert Images Directly into Excel #

If you want to embed the images directly into an Excel file instead of just linking paths, you can use openpyxl to add images to the correct cells. Here’s a quick add-on to the code above:

from openpyxl import Workbook
from openpyxl.drawing.image import Image

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Write the header row
for col_idx, header_text in enumerate(csv_header, 1):
    ws.cell(row=1, column=col_idx, value=header_text)

# Write rows and insert images
for row_idx, row_data in enumerate(csv_rows[1:], 2):
    # Write cell values
    for col_idx, cell_val in enumerate(row_data[:-1], 1):
        ws.cell(row=row_idx, column=col_idx, value=cell_val)
    
    # Insert images into the last column
    image_paths = row_data[-1].split("; ")
    if image_paths[0]:  # Skip if no images
        for img_path in image_paths:
            img = Image(img_path)
            # Resize image to fit cell (adjust as needed)
            img.width = 100
            img.height = 100
            # Place image in the "Cell Image Filepaths" column
            ws.add_image(img, f"{chr(64 + len(csv_header))}{row_idx}")

# Save the Excel file
wb.save("./smartsheet_export_with_images.xlsx")

Important Notes: #

• API Permissions: Make sure your Smartsheet API token has at least READ access to the sheet and its attachments.
• API Quotas: Each attachment download counts as an API request, so if you have a large sheet with many images, keep an eye on your API quota limits.
• Multiple Images per Cell: The code handles cases where a single cell has multiple inline images (rare, but possible) by saving all and listing their paths.

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