// Sheets · Apps Script

Copy values only without formulas in Google Sheets.

How to copy a range in Google Sheets so the destination gets plain values, not formulas — using copyTo with PASTE_VALUES or the getValues/setValues pattern in Apps Script.

“I want to copy a range in Google Sheets and have the destination contain the computed results, not the original formulas.”

The script

copy · paste · trigger

copyValuesOnly.gs

Apps Script

// Copy a named range to a destination as plain values, no formulas.
// Run from the Apps Script editor or bind to a button/trigger.
function copyValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getRangeByName('SalesData');
  var dst = ss.getSheetByName('Archive').getRange('A2');

  // Option A: copyTo with PASTE_VALUES (single call, best for large ranges)
  src.copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // Option B: getValues + setValues (useful when you transform data in between)
  // var values = src.getValues();
  // dst.offset(0, 0, values.length, values[0].length).setValues(values);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why a plain copy breaks your destination sheet

When you call range.copyTo(destination) with no extra arguments, Apps Script copies the cell contents verbatim — formulas included. A formula like =SUM(B2:B50) lands in the destination still pointing at the original sheet's B column. If the destination is a different sheet, or a snapshot that is supposed to stay frozen, the formula keeps recalculating against live data. That is rarely what you want.

The fix is one extra argument: SpreadsheetApp.CopyPasteType.PASTE_VALUES. That enum tells the API to evaluate every formula first and write only the result. The third argument (transposed) is nearly always false unless you are rotating the range 90 degrees.

The first time I hit this I spent twenty minutes wondering why my Archive sheet kept updating in real time. The answer was that I had just moved the formulas, not the values.

The getFormulas trap

There is a common detour: developers reach for getFormulas(), thinking it returns the values. It does not. getFormulas() returns the literal formula strings — the "=SUM(B2:B50)" text — for every cell that contains a formula, and an empty string for cells that do not. Passing that 2-D array to setValues() writes the formula strings as text, which is usually useless and occasionally confusing because they look like formulas but are prefixed in a way that prevents evaluation.

getValues() is the correct call for this job. It always returns computed results: numbers, strings, Dates, booleans. If cell A1 contains =TODAY(), getValues() gives you a JavaScript Date object for today, not the string "=TODAY()". That is the array you pass to setValues().

The getValues/setValues pattern is worth using when you need to manipulate the data in JavaScript before writing it — rounding numbers, reformatting dates, filtering rows. For a straight copy with no transformation, copyTo with PASTE_VALUES makes one network round-trip instead of two, which matters on ranges larger than a few hundred rows.

Sizing the destination range correctly

setValues() is strict: the array dimensions must exactly match the target range dimensions. If your source is 50 rows by 4 columns and your destination range is only 10 rows, the call throws a "The data table is not formatted correctly" error with no indication of which dimension is wrong.

The safest pattern is to pass only the top-left cell as the destination and then use offset(0, 0, values.length, values[0].length) to size it dynamically. That way you never have to hard-code row counts. The snippet above shows this in the commented-out Option B block.

copyTo does not have this constraint — it infers the size from the source range automatically, which is another reason to prefer it for straight copies.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does copyTo with PASTE_VALUES also copy formatting?: No. PASTE_VALUES strips formatting, formulas, and data validation — only the computed cell values transfer. If you need values plus formatting, use PASTE_VALUES for one call and PASTE_FORMAT for a second call, or use PASTE_NORMAL and accept that formulas will follow.
My getValues() call returns dates as numbers, not Date objects. Why?: Sheets stores dates internally as serial numbers (days since December 30, 1899). Apps Script wraps them as JavaScript Date objects when the cell format is set to Date or DateTime. If the cell is formatted as Number or Plain Text, getValues() returns the raw serial. Fix the source cell format, or convert the serial manually: new Date(Math.round((serial - 25569) * 86400 * 1000)).
Can I do this without Apps Script, using a built-in Sheets feature?: Yes. Select the source range, copy it (Ctrl+C), then in the destination use Edit > Paste Special > Values only (Ctrl+Shift+V on most platforms). That is the manual equivalent of PASTE_VALUES. Apps Script is only necessary when you want to automate the operation on a schedule or trigger.
Will PASTE_VALUES work across spreadsheet files, not just across sheets in the same file?: copyTo only works within the same spreadsheet. Across files, you must use getValues() on the source and setValues() on the destination range in the other SpreadsheetApp.openById() instance. The two-call pattern is the only option for cross-file transfers.