Article

Bulk Operations at Scale with Smartsheet API

Mastering Smartsheet API series — part 5

This post is part of the Mastering Smartsheet API series, a practical companion to the Smartsheet API documentation.

Your integration works fine in testing. Then someone points it at a 40,000-row migration, and it's still running four hours later — one row at a time, hitting the rate limit every few minutes, with no way to tell how far it's gotten or which rows actually failed. Bulk operations exist to solve exactly this, but only if you build around how they actually behave: fixed row limits per call, all-or-nothing failure by default, and no built-in rollback.

This post covers the patterns that make bulk operations reliable at scale — chunking, partial success, safe concurrency, and progress tracking — not just the endpoint reference.

The 500-row ceiling

Every bulk row operation (Add Rows, Update Rows) caps out at 500 rows per call. Send more than that in a single request and the call fails outright. So the first thing any at-scale integration needs is a chunking layer: split your dataset into batches of 500 or fewer before it ever touches the API.

This isn't just about staying under the limit. Smaller, well-sized batches also make failures cheaper to diagnose and retry — a failed batch of 500 is a lot easier to triage than a failed batch of 50,000.

Python
def chunk_rows(rows, batch_size=500):
    """Split a list of row objects into batches within the API limit."""
    for i in range(0, len(rows), batch_size):
        yield rows[i : i + batch_size]

Delete Rows is the exception

Add Rows and Update Rows take their payload in the request body, so the 500-row limit applies cleanly. Delete Rows works differently, row IDs go in the URL as a query string, not the body, so the real ceiling is however many IDs fit in a URL before it gets too long, not a fixed count. In practice, developers report needing to keep Delete Rows batches closer to 200 rows to stay safe. Reuse the same chunk_rows() helper above, just call it with a smaller batch_size (e.g. chunk_rows(rows, batch_size=200) ) when deleting.

All-or-nothing is the default — partial success is opt-in

By default, if one row object in a bulk request is invalid, the entire call fails and nothing in that batch is written. For a 500-row batch, one bad row means all 500 roll back to their prior state.

Partial success changes that. Enable it with a query parameter on the request, and Smartsheet will write everything valid in the batch and report back what failed, instead of rejecting the whole thing. The response comes back with message: "PARTIAL_SUCCESS" and resultCode: 3 , plus a failedItems array — one entry per row that didn't make it, each with an index (its position in the batch), and an error object with an errorCode and message:

JSON
{
  "message": "PARTIAL_SUCCESS",
  "resultCode": 3,
  "result": [ /* successfully written rows */ ],
  "failedItems": [
    {
      "rowId": 0,
      "index": 12,
      "error": {
        "errorCode": 1042,
        "message": "Invalid value for a Cell"
      }
    }
  ]
}

The practical pattern: enable partial success on every bulk call, always check for failedItems , and route failed rows into a separate queue for inspection or retry rather than assuming a 200 response means everything landed.

Python
def process_batch_result(result, batch, log):
    if result.get("message") == "PARTIAL_SUCCESS":
        for failure in result.get("failedItems", []):
            log.warning(
                "Row at batch index %s failed: %s (code %s)",
                failure["index"],
                failure["error"]["message"],
                failure["error"]["errorCode"],
            )
        failed_indexes = {f["index"] for f in result["failedItems"]}
        return [row for i, row in enumerate(batch) if i not in failed_indexes]
    return batch

Concurrent writes cause save collisions, not just rate limit errors

It's tempting to speed things up by firing batches in parallel against the same sheet. Don't. Concurrent writes to the same sheet can collide and return error code 4004, and Smartsheet's own guidance is to execute requests against a single sheet serially — one at a time, waiting for each to complete before starting the next.

This is a big part of why bulk operations matter in the first place: batching 500 rows into one call is both faster and safer than firing 500 parallel single-row calls. If you need to write to multiple sheets, parallelizing across sheets is fine — the constraint is specifically about hammering one sheet from multiple directions at once.

Progress tracking for long-running jobs

A 50,000-row job spans 100 batches at the 500-row limit. Nobody — including the person running the migration — wants to stare at a blank terminal for that long. Track and surface progress as you go: batches completed, rows written, rows failed, and an estimate of what's left.

Python
class BulkProgress:
    def __init__(self, total_rows, batch_size=500):
        self.total_rows = total_rows
        self.total_batches = -(-total_rows // batch_size)  # ceiling division
        self.batches_done = 0
        self.rows_written = 0
        self.rows_failed = 0

    def record_batch(self, written, failed):
        self.batches_done += 1
        self.rows_written += written
        self.rows_failed += failed

    def report(self):
        pct = (self.batches_done / self.total_batches) * 100
        return (
            f"{self.batches_done}/{self.total_batches} batches "
            f"({pct:.1f}%) — {self.rows_written} written, "
            f"{self.rows_failed} failed"
        )

There's no native rollback — plan for it at the application layer

Smartsheet doesn't offer a transaction concept spanning multiple bulk calls — there's no "undo the last 30 batches" button. If batch 47 of 100 fails in a way that matters, batches 1–46 are already committed. The API doesn't roll that back for you, so your integration needs to:

Keep a record of every row ID created or modified per batch, so a rollback script can target exactly those rows if needed
Decide up front whether a failed batch should halt the whole job or log-and-continue
For migrations, consider writing to a staging sheet first and only moving data into the production sheet once the full load is verified

None of this is exotic — it's the same discipline you'd apply to any batch ETL job. Smartsheet just doesn't do it for you.

Putting it together

Python
def bulk_update(client, sheet_id, rows, batch_size=500):
    progress = BulkProgress(len(rows), batch_size)
    failed_rows = []

    for batch in chunk_rows(rows, batch_size):
        result = client.update_rows(
            sheet_id,
            batch,
            params={"allowPartialSuccess": "true"},
        )
        succeeded = process_batch_result(result, batch, logger)
        batch_failed = len(batch) - len(succeeded)

        if batch_failed:
            failed_rows.extend(
                row for row in batch if row not in succeeded
            )

        progress.record_batch(len(succeeded), batch_failed)
        logger.info(progress.report())

    return progress, failed_rows

This isn't production-complete — you'd still want retry logic on top of it for transient errors (see Part 4 on rate limits and retries), plus whatever staging or logging your specific migration or ETL job calls for. But it's the right shape: chunk, enable partial success, never parallelize writes against one sheet, track progress, and keep enough of a record to recover if something goes wrong midway.

Checklist before you run a large bulk job

Rows are chunked into batches of 500 or fewer
Partial success is enabled on every bulk call
failedItems is checked and logged on every response, not just non-200s
Writes to a single sheet are serialized, never parallelized
Progress (batches, rows written, rows failed) is tracked and visible
Row IDs from each successful batch are recorded somewhere recoverable
You've decided in advance: does one failed batch halt the job, or continue and report at the end?

Catch up on the series

New learning blogs drop regularly — join the API & Developers group in the Smartsheet Community and click Follow → Include in Email Digest to get each new blogpost delivered straight to your inbox via the weekly digest.

This post is part of the Mastering Smartsheet API series, designed as a practical companion to the Smartsheet API documentation. For questions, visit the Smartsheet Community or the developer forum.