Copying Data Between Sheets with Google Apps Script

Google Apps Script is a powerful tool that allows developers to automate and customize Google Workspace products. One functionality that developers commonly use is copying data from one sheet to another in Excel. In this article, we will discuss how to do this using Google Apps Script.

Copying data from one sheet to another in Excel can be done in several ways. We will discuss the two most common ways: using the setValues() method and the Range object’s copyTo() method.

Method 1: Using setValues()

The setValues() method is an easy way to copy data from one sheet to another. It takes a 2D array of values and pastes them into a range of cells on the destination sheet. Here is an example:

function copyData() {
    var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    var data = sourceSheet.getRange("A1:B10").getValues();
    destinationSheet.getRange("A1:B10").setValues(data);
}

In this example, we first retrieve the source and destination sheets using the getSheetByName() method. Then, we retrieve the data we want to copy using the getRange() method. Finally, we paste the data into the destination sheet using the setValues() method.

Method 2: Using copyTo()

The copyTo() method is another way to copy data between sheets. Instead of using a 2D array, we use the Range object’s copyTo() method to copy the contents of a range to another range on a different sheet. Here is an example:

function copyData() {
    var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    var sourceRange = sourceSheet.getRange("A1:B10");
    var destinationRange = destinationSheet.getRange("A1");
    sourceRange.copyTo(destinationRange);
}

In this example, we first retrieve the source and destination sheets using the getSheetByName() method. Then, we retrieve the source and destination ranges using the getRange() method. Finally, we use the copyTo() method to copy the contents of the source range to the destination range.