Reading and writing sheets Updated November 2026

Copy data between sheets

Within one spreadsheet, source.getRange("A1:C10").copyTo(target.getRange("A1")) copies values, formulas and formatting; add SpreadsheetApp.CopyPasteType.PASTE_VALUES to paste values only. Across spreadsheets, copyTo does not work: read with getValues and write with setValues on the other file. sheet.copyTo(otherSpreadsheet) duplicates a whole tab.

Syntax

range.copyTo(destination) | range.copyTo(destination, copyPasteType, transposed) | sheet.copyTo(spreadsheet)

Part	What it is
`destination`	A Range; only its top-left cell matters
`copyPasteType`	SpreadsheetApp.CopyPasteType.PASTE_VALUES, PASTE_FORMAT, PASTE_FORMULA, PASTE_NORMAL
`transposed`	true to swap rows and columns

Examples

const ss = SpreadsheetApp.getActiveSpreadsheet();
const src = ss.getSheetByName("Data");
const dst = ss.getSheetByName("Report");
src.getRange("A1:D20").copyTo(dst.getRange("A1"));

Result: A1:D20 of Data appears in Report with formulas and formatting

src.getRange("A1:D20").copyTo(dst.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Result: Same block pasted as static values, no formulas

const other = SpreadsheetApp.openById("1AbC...xyz");
const values = src.getDataRange().getValues();
other.getSheetByName("Import").getRange(1, 1, values.length, values[0].length).setValues(values);

Result: All data from Data copied into the Import tab of another spreadsheet

const copy = src.copyTo(other).setName("Data backup");

Result: A full copy of the Data tab inside the other spreadsheet, named Data backup

Common mistakes

Target range and source range must be on the same spreadsheet: copyTo works within one file only; use getValues and setValues across files.
Copied formulas now point at the wrong cells: copyTo adjusts relative references like a paste; use PASTE_VALUES or absolute references.
sheet.copyTo names the tab Copy of Data: chain setName to pick the name; a duplicate name throws, so delete the old one first.
Formatting lost after setValues: setValues moves values only; copy formats with copyTo and PASTE_FORMAT, or set formats explicitly.

Practice Copy data between sheets

These drills check your answer on live data, free, no account needed.

Split into chunksSplit `arr` into arrays of `size` items each. The last chunk holds whatever is left, so it can be shorter. `size` is at least 1. Do not change `arr`.
Flatten one levelReturn a new array where items that are arrays are replaced by their contents, one level deep only. Items that are not arrays (numbers, strings, `null`, objects) stay as they are, and deeper arrays stay nested.

Questions

How do I copy only new rows to another sheet?

Read the target's last row, then write only the source rows beyond that count, or key rows by an id and append those missing from the target.

How do I copy a sheet to a new spreadsheet?

const newSs = SpreadsheetApp.create("Export"); sheet.copyTo(newSs); then delete the default Sheet1 in newSs if you do not want it.

getActiveSheet vs getSheetByNameChoose between getActiveSheet and getSheetByName in Google Apps Script: when the active sheet is unreliable, targeting a tab by name, and missing sheets.
setValue and setValuesWrite data to Google Sheets with Apps Script: setValue for one cell, setValues for a 2D array that must match the range size, and the dimension error fix.
Clear and format cellsClear and format Google Sheets cells with Apps Script: clear content or formats, set background and font color, bold, number and date formats, borders.
appendRowAdd a row to a Google Sheet with Apps Script: appendRow for a single row after the last row with data, and setValues for many rows at once, with examples.