How to copy a row from one sheet to another with Google Apps Script
Share on twitter
Share on linkedin
Share on facebook
Share on reddit
Share on telegram
Share on whatsapp
Share on email

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));
} 

Google Apps Script Online Course

Learn how to automate Google Sheets with Google Apps Script! Check out our comprehensive online course.

saperis online course

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.

4 thoughts on “How to Copy a Row to another Sheet with Google Apps Script”

  1. It is a very great help , I get stuck with these for many weeks , I looked thru many clips, but it is still not simple and not right to the target like yours , many thanks to you .

  2. Hello, thank you so much for this tutorial. Very helpful.

    I was wondering how you can copy rows from one sheet to another sheet already with existing values. I’m hoping to add values in Sheet 1 to the last row in Sheet 2. Values in Sheet 1 will be updated regularly, so would like those to be automatically added to Sheet 2 each time it updates.

Comments are closed.

Subscribe to our Newsletter

Subscribe to our newsletter and be the first to know about our new blog posts and videos.

Share this post with your friends

Share on twitter
Share on linkedin
Share on facebook
Share on reddit
Share on telegram
Share on whatsapp
Share on email

Subscribe to our Weekly Newsletter

You’ll get tips, tricks and infos all about G Suite.