IMPORTRANGE is great until the client workbook needs a static copy, the source sheet has to stay private, or the formula starts showing #REF! at 9am. copyValuesToExternalSheet(targetId, tabName, values, opts) opens the target spreadsheet by id, gets or creates the tab, clears everything below the header row (headers and formatting stay), and writes your 2D block in one setValues.
When to use
- Scheduled mirrors of an Orders / Leads tab into a client-facing workbook
- Snapshots for people who shouldn’t have access to the source file
- Replacing a fragile IMPORTRANGE with values-only copies
How to use this snippet
/**
* Replace the data rows of a tab in ANOTHER spreadsheet with a 2D block.
* Keeps header rows (and formatting); creates the tab if it doesn't exist.
*
* @param {string} targetId destination spreadsheet id
* @param {string} tabName destination tab name
* @param {Array<Array<*>>} values data rows only (no header row), rectangular
* @param {{headerRows?: number, headers?: string[]}=} opts
* headerRows rows to keep at the top (default 1)
* headers written to row 1 only when the tab is empty (e.g. just created)
* @return {number} rows written
*/
function copyValuesToExternalSheet(targetId, tabName, values, opts) {
opts = opts || {};
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
// 1. Open the target workbook and get (or create) the destination tab.
var ss = SpreadsheetApp.openById(targetId);
var sheet = ss.getSheetByName(tabName) || ss.insertSheet(tabName);
// 2. Brand-new tab? Seed the header row so humans know what they're looking at.
if (opts.headers && sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, opts.headers.length).setValues([opts.headers]);
}
// 3. Clear old data BELOW the headers. clearContent keeps formatting and validation.
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow > headerRows && lastCol > 0) {
sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
}
if (!values || !values.length) return 0;
var numRows = values.length;
var numCols = values[0].length;
values.forEach(function (row, i) {
if (row.length !== numCols) {
throw new Error('copyValuesToExternalSheet: row ' + i + ' has ' + row.length +
' columns, expected ' + numCols);
}
});
// 4. Make sure the grid is big enough, otherwise getRange throws.
var needRows = headerRows + numRows - sheet.getMaxRows();
if (needRows > 0) sheet.insertRowsAfter(sheet.getMaxRows(), needRows);
var needCols = numCols - sheet.getMaxColumns();
if (needCols > 0) sheet.insertColumnsAfter(sheet.getMaxColumns(), needCols);
// 5. One write for the whole block.
sheet.getRange(headerRows + 1, 1, numRows, numCols).setValues(values);
return numRows;
}
Example
// Copy the Orders tab (minus its header) into the client's workbook.
function mirrorOrdersToClient() {
var clientId = PropertiesService.getScriptProperties().getProperty('clientWorkbookId');
var source = SpreadsheetApp.getActive().getSheetByName('Orders');
var all = source.getDataRange().getValues();
var headers = all.shift(); // remove header row from the data block
var written = copyValuesToExternalSheet(clientId, 'Orders', all, { headers: headers });
Logger.log('Mirrored %s rows to %s', written, clientId);
}
Tips:
- The script’s account needs edit access to the target spreadsheet — share it before the first scheduled run.
- Values only: formulas arrive as their results, which is usually what a client copy wants. Use
getDisplayValues()on the source if you need formatted text (currency, dates) instead. - There’s a short window between clear and write where the target is empty. For big tabs, wrap the mirror in a lock and write in chunks (see the mirror guide).
- Store the target id in Script Properties, not in the code.
Tip: NitroGAS Co-Pilot can turn “copy Orders → client workbook every morning” into a first draft with a time-driven trigger.
Happy Coding!
