Batch writes without timeouts

TL;DR — Lock, chunk, and watermark your Apps Script sheet writes so big jobs finish (or resume) instead of dying halfway with a spinner and half a dataset.

When a script dies halfway through writing thousands of rows, you don't need a fancier API — you need a boring pattern: lock, chunk, watermark, resume. Here's the helper we actually paste into client projects so you stop babysitting the spinner.

TL;DR

Don't setValue in a loop cell-by-cell.
Prefer setValues on blocks; size chunks so one execution finishes (or exits cleanly for the next run).
Use LockService when triggers or people can overlap.
Persist a watermark (last row written) so retries resume instead of rewriting from row 1.
Unlock in finally so a failed run doesn't leave the lock stuck.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

The failure mode

You've seen this movie. Client sheet, ~20k rows, nightly sync. It worked fine on your sample of 50. Then production hits and suddenly:

"Exceeded maximum execution time"
Half the sheet looks right, half is stale or blank
Someone hits Run again "to finish it" and now you've got duplicates
Slack lights up like a Christmas tree

We've been that person refreshing the spreadsheet like it owes us money. The script isn't cursed — it's just writing more than one Apps Script execution can reliably finish, with no memory of where it left off.

What not to do

Three habits that feel productive and then bite you:

Cell-by-cell writes — setValue inside a for loop over thousands of rows. Each call is a round trip. You'll burn your time budget on overhead, not data.
One giant unbounded setValues — dumping 50k rows in a single call with no time budget. Sometimes it works. When it doesn't, you're left with a partial write and no resume point.
Retry from row 1 with no idempotency — re-running the same job and appending (or overwriting blindly) without knowing the last successful row. That's how you get duplicates and angry audits.

Building blocks

Piece	Why
`setValues` on a 2D array	One call, many cells — the default write shape
Chunk size	Stay under time limits; tune (e.g. 500–2000 rows)
`LockService.getScriptLock()`	Stop overlapping triggers / manual runs from stomping each other
`PropertiesService` watermark	Remember the last row written so the next run continues
`try` / `finally` unlock	Don't leave the lock stuck if something throws

Optional alternative: if you want a visible progress trail for stakeholders, keep a _sync_log sheet (timestamp, watermark, status, last error) instead of — or in addition to — PropertiesService. Same idea, just easier to eyeball in the UI. For the sample below we default to PropertiesService because it's simpler and doesn't pollute the workbook.

The pattern (pasteable helper)

Let's begin with a complete helper you can drop into the Apps Script editor. Skim the steps, then paste and adapt the sheet names / column counts.

Step A — acquire a script lock (wait with a timeout)
Step B — read the watermark from PropertiesService
Step C — prepare the next chunk from your source data
Step D — setValues for that block only
Step E — advance the watermark
Step F — if time remains, loop; else exit cleanly for the next run
Step G — release the lock in finally

/**
 * Batch-write rows to a sheet with lock + watermark + time budget.
 * Re-run (manually or via time-driven trigger) until the watermark
 * catches up to source.length. Safe to overlap — LockService serializes.
 *
 * Assumptions:
 * - Destination sheet already has a header in row 1
 * - We write contiguous rows starting at row 2
 * - source is a 2D array: [[colA, colB, ...], ...]
 */
function batchWriteWithWatermark(source) {
  var CHUNK_SIZE = 1000;          // tune me
  var MAX_RUNTIME_MS = 5 * 60 * 1000; // leave headroom under the 6-min limit
  var LOCK_WAIT_MS = 30000;
  var WATERMARK_KEY = 'batchWrite_lastRow'; // 0-based index into source

  var lock = LockService.getScriptLock();
  var props = PropertiesService.getScriptProperties();
  var startedAt = Date.now();

  if (!lock.tryLock(LOCK_WAIT_MS)) {
    throw new Error('Could not acquire script lock within ' + LOCK_WAIT_MS + 'ms. Another run is in progress.');
  }

  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('SyncTarget'); // <-- your sheet
    if (!sheet) {
      throw new Error('Sheet "SyncTarget" not found.');
    }

    var watermark = parseInt(props.getProperty(WATERMARK_KEY) || '0', 10);
    if (isNaN(watermark) || watermark < 0) watermark = 0;

    var numCols = source.length ? source[0].length : 0;
    if (!numCols) {
      Logger.log('Nothing to write — source is empty.');
      return { done: true, watermark: watermark };
    }

    while (watermark < source.length) {
      // Time-budget check before starting another chunk
      if (Date.now() - startedAt > MAX_RUNTIME_MS) {
        Logger.log('Time budget hit at watermark ' + watermark + ' / ' + source.length + '. Exiting cleanly — re-run to continue.');
        return { done: false, watermark: watermark };
      }

      var end = Math.min(watermark + CHUNK_SIZE, source.length);
      var chunk = source.slice(watermark, end);

      // Destination row: header is row 1, so first data row is 2 + watermark
      var startRow = 2 + watermark;
      sheet.getRange(startRow, 1, chunk.length, numCols).setValues(chunk);

      watermark = end;
      props.setProperty(WATERMARK_KEY, String(watermark));
      Logger.log('Wrote rows up to source index ' + watermark);
    }

    Logger.log('Batch write complete. Total rows: ' + source.length);
    // Optional: clear watermark so the next full sync starts fresh
    // props.deleteProperty(WATERMARK_KEY);
    return { done: true, watermark: watermark };
  } finally {
    lock.releaseLock();
  }
}

/** Example entry point — replace with your real source fetch. */
function runNightlySync() {
  var source = buildSourceRows_(); // your function that returns a 2D array
  var result = batchWriteWithWatermark(source);
  if (!result.done) {
    Logger.log('Partial run OK. Next execution will resume at index ' + result.watermark);
  }
}

/** Demo source — swap for API / another sheet / Drive file. */
function buildSourceRows_() {
  var rows = [];
  for (var i = 0; i < 5000; i++) {
    rows.push([i + 1, 'Item ' + (i + 1), new Date().toISOString()]);
  }
  return rows;
}

A few notes on what this is doing:

tryLock + wait — if a time-driven trigger and a manual run overlap, the second one waits (or fails loud) instead of double-writing.
Watermark in PropertiesService — after each successful chunk we persist progress. A timeout on the next chunk doesn't lose the previous ones.
Time budget via Date.now() — we stop before Apps Script kills us, so the watermark stays honest.
finally unlock — even if setValues throws, we release the lock.

Wire runNightlySync to a time-driven trigger (or a custom menu) and let it take as many executions as it needs.

Tuning the chunk size

Start conservative — something like 500 rows — and bump up until runs are stable. A few realities:

Sheets full of formulas, ARRAYFORMULA, or volatile functions (NOW, RAND) chew through your budget faster than a flat values sheet.
Wide rows (dozens of columns) are heavier than narrow ones at the same row count.
Prefer the time-based stop in the helper over a fixed "always write N chunks" count. Machines and sheet complexity vary; wall-clock budget is the honest signal.

If a chunk of 2000 is flaky but 800 is boringly reliable, pick boring. You can always raise it later.

Overlaps & duplicates

Locks matter whenever more than one thing can start the job: a nightly trigger and you hitting Run, two editors, a form submit that kicks the same sync, etc. Without a lock, two executions can read the same watermark and write the same block twice — or interleave mid-chunk.

A few practical rules:

Prefer write-by-position (or by key) over append-forever. The sample overwrites a known row range from the watermark forward. If your source is keyed (IDs), upsert by key instead of blindly appending.
Log enough to debug. At minimum: watermark before/after, chunk size, and any error message. Logger.log is fine for solo work; a _sync_log sheet is nicer when someone else has to triage at 2am.
Fail loud. Throwing when the lock can't be acquired beats silent no-ops that look like "the sync ran."

Minimal test plan

Before you point this at a client workbook:

Fake ~5k rows in a scratch spreadsheet (the buildSourceRows_() stub is enough).
Set CHUNK_SIZE = 100 so a single run can't finish — you want multi-run behavior on purpose.
Kill a run mid-way (or let the time budget trip). Confirm the watermark advanced and a second run resumes, not restarts.
Fire the function twice quickly. Confirm the second wait/fails on the lock instead of double-writing.

If those four pass, you're in good shape to raise the chunk size and point it at real data.

When this isn't enough

For truly huge jobs — hundreds of thousands of rows, multi-tab rebuilds, or syncs that need to finish in one shot — Apps Script's Spreadsheet service will keep feeling tight. In that world, look at the Advanced Sheets Service (batch spreadsheets.values / batchUpdate style calls) or exporting/importing via Drive-friendly formats and processing outside the 6-minute box. Most bootstrapper syncs never need that jump; exhaust lock + chunk + watermark first.

Keep the helper handy

If you're tired of rebuilding this on every client file, NitroGAS keeps patterns like this as snippets inside the Apps Script editor — Co-Pilot's there if you need to adapt it. Free extension; Co-Pilot optional. Either way, the code above stands on its own.

Closing checklist

Writes go through setValues on chunks — not cell-by-cell
LockService wraps the job; unlock lives in finally
Watermark (PropertiesService, or a _sync_log sheet) advances after each successful chunk
A time budget exits cleanly so the next run can resume
You tested multi-run resume + overlapping lock behavior on a scratch sheet

That's it. Lock, chunk, watermark, resume — then go do something more interesting than watching a spinner.

Happy Coding!