Google Workspace Tutorials

How to Copy a Row to another Sheet with Google Apps Script

Learn how you can copy a row, or multitple rows, from one sheet to another in a Google Sheets document by using Google Apps Script!


How to Copy a Row to another Sheet with Google Apps Script

You want to copy a row, or multiple rows, from one sheet to another in Google Sheets using Google Apps Script?

In that case you can follow along in this Apps Script tutorial! We’ll learn how to copy rows by using either the setValues or copyTo method.

If you’re anything like me, you might enjoy watching the video of this tutorial. 

 

Create a Google Sheet document

The first step is to create a Google Sheet document with a source sheet and a target sheet. In this tutorial our source sheet is named “Source” and our target “Target”. Then head over to the Script Editor to start coding.

If you’ve never written a line of Google Apps Script before or you just want to refresh your knowledge then check out this blog post.

Copy a Row with Google Apps Script
After creating your document open the Script editor

Copy Row with Google Apps Script by Using the setValues Method

We start off by getting the active spread sheet and then getting the source sheet by name.

Then we’ll use the getDataRange and getValues methods to get the data from our source sheet.

Next we want to find out how many rows and columns we have in our source sheet. This information is needed for defining the range we want to write our data into on the target sheet.

Then it’s time to get our target sheet by name as well as the range to copy our data to.

The last line of code is the actual copying of our data to our target(range).

function copyRowsWithSetValues() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Source');
  
  let sourceRange = sourceSheet.getDataRange();
  let sourceValues = sourceRange.getValues();
  
  let rowCount = sourceValues.length;
  let columnCount = sourceValues[0].length;
  
  let targetSheet = spreadSheet.getSheetByName('Target');
  let targetRange = targetSheet.getRange(1, 1, rowCount, columnCount);
  
  targetRange.setValues(sourceValues);
} 

Copy Row with Google Apps Script by Using the copyTo Method

Also with this way of copying rows to another sheet we start off with getting our spread sheet and the source sheet.

Once we’ve got that information we get the data range on our source sheet. We do not have to get the values as we had to do in the previous example.

Instead, we directly get our target sheet and copy the source range to that sheet.

function copyRowsWithCopyTo() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Source');
  
  let sourceRange = sourceSheet.getDataRange();
  
  let targetSheet = spreadSheet.getSheetByName('Target');
  
  sourceRange.copyTo(targetSheet.getRange(1, 1));
} 
 
 
 

setValues vs. copyTo

The obvious question at this point is: Should you use setValues or rather copyTo to copy rows from one sheet to another with Google Apps Script?

The short answer is: It depends. Here my suggestions on when to use which method.

You’re copying data from one sheet to another and both sheets are in the same document? Then I would use the copyTo method.

You’re copying data from one sheet to another sheet which is located in a different document? Then you have to use setValues as copyTo only works within the same document.

And also note that in order to use the setValues method you have to pass in a two-dimensional array. If you’re using the copyTo method, on the other hand, you need a data range.

 

Similar posts

Google Workspace Blog

Don't want to miss any of our blog posts about Google Workspace? Then go ahead and sign up!