Mirror a tab to another workbook on a schedule (without IMPORTRANGE)

TL;DR — Copy a tab into another spreadsheet on a time-driven trigger: open the target by id, clear below the headers, setValues, stamp when it last mirrored — with LockService and chunking for big tabs. Plus a soft NitroGAS note for copyValuesToExternalSheet.

The client wants “their” copy of the Orders tab. You reach for IMPORTRANGE, it works in the demo, and then: the client can’t see it because they don’t have access to your source file, a #REF! shows up on a Monday, or someone sorts the imported range and it snaps back.

For a lot of bootstrapper setups, a plain values copy on a schedule is calmer. Your script opens the target workbook, clears the old rows, writes the new ones, and stamps when it happened. The client gets a normal tab they can filter and chart. Your source stays private.

TL;DR

A time-driven trigger copies source values → target tab; no cross-file formulas
Open the target by id, get-or-create the tab, clear below the header row, one setValues
Stamp “last mirrored” in the target so the client knows how fresh it is
Wrap it in LockService so two runs never interleave; chunk writes for big tabs
Soft helper name: copyValuesToExternalSheet (snippet + NitroGAS catalog)

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.

IMPORTRANGE vs a scheduled copy

IMPORTRANGE is live and zero-code. It’s great for internal sheets. It hurts when:

the target’s viewers shouldn’t (or can’t) get access to the source file
the client wants to sort, filter, or annotate the rows
you want a snapshot (“as of 6am”) rather than a moving target
the source is big and the formula keeps recalculating or timing out

A scheduled copy trades “live” for “predictable.” For client reporting, predictable usually wins.

What not to do

Don’t clear() the whole target tab — you’ll lose headers, formatting, and filters
Don’t hard-code the target id in the script body; put it in Script Properties
Don’t copy formulas across files — they’ll reference ranges that don’t exist there
Don’t let two triggers (or a trigger + a manual run) mirror at the same time
Don’t mirror every minute “just in case” — hourly or daily is plenty for most client copies

Workflow

1. Share the target workbook with the account that owns the trigger (edit access)
2. Save the target id in Script Properties
3. mirror(): lock → read source → clear-below-headers → setValues → stamp
4. Install a daily / hourly time-driven trigger once
5. Check the target the morning after the first scheduled run

The mirror

function mirrorOrdersToClient() {
  // Skip this run if another mirror is still going
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    console.log('Mirror already running, skipping');
    return;
  }
  try {
    var targetId = PropertiesService.getScriptProperties().getProperty('clientWorkbookId');
    if (!targetId) throw new Error('Set Script Property clientWorkbookId first');

    var source = SpreadsheetApp.getActive().getSheetByName('Orders');
    var values = source.getDataRange().getValues();
    var headers = values.shift(); // data rows only from here on

    var written = copyValuesToExternalSheet(targetId, 'Orders', values, { headers: headers });
    stampMirrored_(targetId, written);
  } finally {
    lock.releaseLock();
  }
}

The copyValuesToExternalSheet snippet does the target side: openById, get-or-create the tab, clearContent() below the header row (formatting stays), grow the grid if needed, then one setValues.

Two small choices worth making on purpose:

Values vs display values. getValues() sends real numbers and dates. If the client needs “$1,200.00” exactly as you see it, use getDisplayValues() instead.
Columns to share. Mirroring everything is easy; mirroring only client-safe columns is better. Filter the columns before you pass values.

Stamp “last mirrored”

A copy without a timestamp invites “is this current?” emails. Put a small note in the target:

function stampMirrored_(targetId, rows) {
  var ss = SpreadsheetApp.openById(targetId);
  var sheet = ss.getSheetByName('About') || ss.insertSheet('About');
  var when = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd HH:mm z');
  sheet.getRange('A1:B2').setValues([
    ['Last mirrored', when],
    ['Rows', rows]
  ]);
}

Same idea as a status cell for your own jobs — the stampLastSuccessfulRun snippet works here too if you want OK/FAIL alongside the time.

Install the trigger once

function installMirrorTrigger() {
  // Remove old copies so re-running this doesn't stack triggers
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'mirrorOrdersToClient'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });

  ScriptApp.newTrigger('mirrorOrdersToClient').timeBased().everyDays(1).atHour(6).create();
}

Big tabs: chunk the write

One setValues of 50k rows × 20 columns can be slow or hit limits. Write in slices instead — same clear, then a loop:

function writeInChunks_(sheet, startRow, values, chunkSize) {
  chunkSize = chunkSize || 5000;
  var numCols = values[0].length;
  for (var i = 0; i < values.length; i += chunkSize) {
    var slice = values.slice(i, i + chunkSize);
    sheet.getRange(startRow + i, 1, slice.length, numCols).setValues(slice);
    SpreadsheetApp.flush(); // push each chunk before building the next
  }
}

If even chunked writes flirt with the 6-minute limit, the source is probably too big for a nightly full copy — mirror only recent rows, or split by month. Batch writes without timeouts covers the batching side in more depth.

Heads-up: between the clear and the last chunk, the target is partly empty. For client copies at 6am that’s fine. If someone might be reading mid-run, write to a hidden Orders_staging tab first, then swap names when it’s done.

Failure modes worth naming

“You do not have permission” — the trigger owner needs edit access to the target. Share it before the first scheduled run.
Target tab renamed by the client — the helper creates a fresh tab with the old name. Protect the tab name in your handoff notes (or protect the sheet).
Row counts shrink — clear-below-headers handles it; old rows don’t linger at the bottom.
Overlapping runs — tryLock skips the second run instead of interleaving two copies.
Wrong target — keep a staging workbook id handy and test there first.

Soft Co-Pilot note

NitroGAS gives you copyValuesToExternalSheet from the snippet catalog without leaving the editor. Describe it as “copy Orders → client workbook every morning at 6” and Co-Pilot can turn that into a first draft — lock, copy, stamp, trigger. You still share the target and check the first run. Free core; Co-Pilot is a pass for when you want the head start.

Closing checklist

Target shared with the trigger owner (edit)
Target id in Script Properties
Clear below headers, not the whole tab
LockService around the mirror
“Last mirrored” stamp in the target
One trigger, installed by a re-runnable setup function

Happy Coding!