Copy Google Spreadsheet Data to Another Sheet with Apps Script

Google Sheets is a powerful tool for storing and analyzing data. However, when managing growing spreadsheets, you may often need to copy data from one sheet to another—for example, to back up data, summarize information, or prepare reports. Fortunately, Google Apps Script makes this process simple and automated.

In this blog, we’ll walk through everything you need to know about copying data from one sheet to another using Apps Script—step by step.

Why Use Apps Script to Copy Data?

Apps Script lets you automate tasks in Google Sheets with JavaScript-like code. Using a custom script is ideal for:

• Backing up data daily or weekly to another sheet.

• Extracting filtered or calculated data to a report sheet.

• Copying rows when a condition is met.

• Moving data from one spreadsheet to another file entirely.

Basic Concept: Source Sheet → Target Sheet

You typically want to:

1. Get the source data

2. Access the destination sheet

3. Clear old data (optional)

4. Paste the new data

Step-by-Step: Apps Script to Copy Data Between Sheets

Step 1: Open Script Editor

1. Open your Google Sheet.

2. Click on Extensions > Apps Script.

3. Delete any placeholder code.

Step 2: Paste the Script

Here is a simple function to copy all data from one sheet to another within the same spreadsheet.

function copySheetData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Sheet1"); // Replace with your source sheet name
  const targetSheet = ss.getSheetByName("Sheet2"); // Replace with your target sheet name

  if (!sourceSheet || !targetSheet) {
    Logger.log("Sheet not found");
    return;
  }

  // Get the data from the source sheet
  const sourceData = sourceSheet.getDataRange().getValues();

  // Clear the target sheet before pasting
  targetSheet.clearContents();

  // Paste data into target sheet
  targetSheet.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);

  Logger.log("Data copied successfully!");
}

How to Run It

1. Click the Save icon (?) and name your project, e.g., CopySheetData.

2. Click the Run ▶ button.

3. When prompted, authorize the script.

Once authorized, it will copy all data from "Sheet1" to "Sheet2".

Customizing the Script

Copy Only a Specific Range

const sourceData = sourceSheet.getRange("A1:D20").getValues();

Append Data to Target Sheet Instead of Replacing

const lastRow = targetSheet.getLastRow();
targetSheet.getRange(lastRow + 1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);

Copy to Another Spreadsheet

function copyToAnotherSpreadsheet() {
  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = sourceSS.getSheetByName("Sheet1");

  const targetSS = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID");
  const targetSheet = targetSS.getSheetByName("Sheet2");

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

? Replace "TARGET_SPREADSHEET_ID" with the actual ID found in the URL of your target spreadsheet.

Automating the Copy Function (Optional)

You can set this to run automatically every day or hour:

1. In Apps Script, click Triggers (⏰ icon) in the left panel.

2. Click + Add Trigger.

3. Choose the function copySheetData.

4. Select event type like Time-driven → Day timer → Midnight to 1am.

Now your sheet will auto-copy daily.

Error Handling Tips

• Always check for null or missing sheets.

• Wrap your function in a try-catch block if deploying to a team.

function safeCopy() {
  try {
    copySheetData();
  } catch (error) {
    Logger.log("Error: " + error.message);
  }
}

Use Cases for Copying Data Between Sheets

• Daily backups of data entry sheets.

• Archiving completed form responses.

• Moving data after it's processed.

• Summarizing information from multiple sources into a dashboard.

Conclusion

Copying data between sheets using Apps Script is a simple yet powerful way to automate your workflow in Google Sheets. Whether you need to back up, organize, or summarize data, a few lines of code can save hours of manual work.

Start with the basic script, and then customize it to suit your exact needs—copy specific columns, append only new rows, or even copy data between different spreadsheets.

Google Apps Script gives you the flexibility to turn your spreadsheet into a powerful data engine with just a little bit of scripting.