Copy and Move Data Between Google Sheets with Apps Script

Copying data between spreadsheets is a common task — archiving completed records, consolidating data from multiple sources, or syncing a master sheet with department files. Apps Script handles all of these cleanly.

Copy a range from one sheet to another (same spreadsheet)

function copyRangeToSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheetByName('Raw Data');
  const target = ss.getSheetByName('Archive');

  const sourceRange = source.getDataRange();
  const values = sourceRange.getValues();

  target.clearContents();
  target.getRange(1, 1, values.length, values[0].length).setValues(values);

  Logger.log('Copied ' + values.length + ' rows to Archive.');
}

Move rows matching a condition to another sheet

function moveCompletedRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const source = ss.getSheetByName('Tasks');
  const target = ss.getSheetByName('Completed');

  const data = source.getDataRange().getValues();
  const statusCol = 3; // Column D (0-indexed)

  // Collect rows to move, iterate from bottom to avoid index shifting
  for (let i = data.length - 1; i >= 1; i--) {
    if (data[i][statusCol] === 'Done') {
      target.appendRow(data[i]);
      source.deleteRow(i + 1);
    }
  }

  Logger.log('Completed rows moved to Completed sheet.');
}

Copy data to a different spreadsheet

function copyToExternalSpreadsheet() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetSsId  = 'TARGET_SPREADSHEET_ID';
  const targetSheet = SpreadsheetApp.openById(targetSsId).getSheetByName('Imports');

  const values = sourceSheet.getDataRange().getValues();
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, values.length, values[0].length).setValues(values);

  Logger.log('Data copied to external spreadsheet.');
}

Append new rows only (sync without duplicates)

function syncNewRows() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source');
  const targetSheet = SpreadsheetApp.openById('TARGET_SS_ID').getSheetByName('Target');

  const sourceData = sourceSheet.getDataRange().getValues();
  const targetData = targetSheet.getDataRange().getValues();

  // Use first column as unique key (e.g. ID)
  const existingIds = new Set(targetData.map(r => r[0]));

  const newRows = sourceData.slice(1).filter(row => !existingIds.has(row[0]));

  if (newRows.length > 0) {
    targetSheet.getRange(
      targetSheet.getLastRow() + 1, 1,
      newRows.length, newRows[0].length
    ).setValues(newRows);
    Logger.log(`Synced ${newRows.length} new row(s).`);
  } else {
    Logger.log('No new rows to sync.');
  }
}

Duplicate a sheet within the same spreadsheet

function duplicateSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Template');

  const copy = sheet.copyTo(ss);
  copy.setName('Report - ' + new Date().toLocaleDateString());

  // Move it to the end
  ss.moveActiveSheet(ss.getNumSheets());

  Logger.log('Sheet duplicated: ' + copy.getName());
}

Consolidate data from multiple spreadsheets into one

function consolidateSheets() {
  const sourceIds = [
    'SPREADSHEET_ID_1',
    'SPREADSHEET_ID_2',
    'SPREADSHEET_ID_3',
  ];

  const masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master');
  masterSheet.clearContents();

  let firstFile = true;

  sourceIds.forEach(id => {
    const ss = SpreadsheetApp.openById(id);
    const sheet = ss.getSheets()[0]; // First sheet
    const data = sheet.getDataRange().getValues();

    if (firstFile) {
      // Include header from first file only
      masterSheet.getRange(masterSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
      firstFile = false;
    } else {
      // Skip header row for subsequent files
      const rows = data.slice(1);
      if (rows.length > 0) {
        masterSheet.getRange(masterSheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
      }
    }
  });

  Logger.log('Consolidation complete. Total rows: ' + (masterSheet.getLastRow() - 1));
}

Tips

SpreadsheetApp.openById() requires the script to have permission to access the target file. Share it with the script owner's account.
Writing all rows at once with setValues() is much faster than looping with appendRow().
When moving rows by deleting them from the source, always iterate from bottom to top to avoid row index shifting.
Add SpreadsheetApp.flush() after large writes to force changes to be committed immediately.