Copy or move rows to another sheet automatically

Moving changed rows to another sheet is pretty useful when you want to:

• move a form response to another sheet
• delegate a sales inquiry to a specific representive
• copy a changed row to a backup sheet for logging purpose
• achieve a row when the data is not relevant

The scenario

So here is the scenario. Suppose you have a sheet to track your to-do items.

There is a “Status” column where you fill out the status and change to “Done” at some point.

You want the task to be automatically moved to another sheet called “Done” when the Status cell is changed to the “Done”.

It may be possible to use filter or query to mimic what is expected. But when you really want your data to be physically moved, filter or query may not work well.

In order to achieve that, the solution is to employ Google Apps Script.

Just move the row

The first thing you should be concerned is trigger, which is the start point of the whole automation. Basically when certain things happen, Google sheet will emit some events to trigger user defined script if any.

There are different kinds of triggers that can handle different situations. For simplicity we will use the default on edit trigger which will be fired whenever the sheet is being manually edited.

Below is the code snippet to accomplish the task. All you need to do is copying the script to your script editor (can be opened via Extensions -> Apps Script).

function onEdit(e){
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Todo'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] === "Done"){
            var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Done");  
            targetSheet.appendRow(rowValues);  
            sourceSheet.deleteRow(row);
        }
    } 
}

The script is pretty self-explanatory. Just a few things to note:

1. if(sourceSheet.getSheetName() === 'Todo') checks the name of the source sheet. Otherwise events from other sheets may trigger the moving, which is unwanted.

2. rowRange.getValues()[0] gets the values of the row as the data returned by rowRange.getValues() is a two-d array.

3. if(rowValues[0] === "Done") checks if the value of the status column is “Done”. Note here status column is the first element of the array. If the column is in another position, it should be changed accordingly.

4. If you only want to copy the row to another sheet, the last line of code should be commented out.

Move the row backward

A further requirement is to move the row backward if the status is changed from “Done” to “Todo”.

This could be easily implemented by duplicating the script and modifying the condition slightly.

function onEdit(e){
    var sourceSheet = e.range.getSheet();
    var row = e.range.getRow();
    if(sourceSheet.getSheetName() === 'Todo'){
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] === "Done"){
            var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Done");  
            targetSheet.appendRow(rowValues);  
            sourceSheet.deleteRow(row);
        }
    } 

    if(sourceSheet.getSheetName() === 'Done'){
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] !== "Done"){
            var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Todo");  
            targetSheet.appendRow(rowValues);  
            sourceSheet.deleteRow(row);
        }
    } 
}

The script can certainly be improved as copying code is something unhealthy. You’d better avoid too much duplication for the sake of easy maintenance. But this is not the foucs of the post.

Move the row to the top

The above script will only append the row to the bottom of the target sheet. You may want to show the moved row on the top so that people can easily see it, or even insert the row in a certain position.

For example, let’s slightly enhance the script to insert the row as the second row, i.e., after the header row.

function onEdit(e){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Todo'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] === "Done"){
            var targetSheet = ss.getSheetByName("Done");
            targetSheet.insertRowAfter(1);  
            var targetRange = targetSheet.getRange(2, 1);
            rowRange.copyTo(targetRange); 
            sourceSheet.deleteRow(row);
        }     
    } 
}

Explanation on the changed code:

1. targetSheet.insertRowAfter(1); creates a blank row right after the head row. You could change 1 to any other position number.

2. targetSheet.getRange(2, 1); gets the first column of the second row, the newly created row, as a range.

3. rowRange.copyTo(targetRange) copies the row data to the blank row.

Move the row with value only

If you don’t want to copy the format of the row, you can use another copyTo function with a parameter of paste type.

function onEdit(e){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Todo'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] === "Done"){
            var targetSheet = ss.getSheetByName("Done");
            targetSheet.insertRowAfter(1);
            var targetRange = targetSheet.getRange(2, 1);
            rowRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      
            sourceSheet.deleteRow(row);
        }     
    } 
}

There are other options to control the copy behavior. Refer to CopyPasteType.

Further notes

This script only works when change is made on a single row. When the change involves multiple rows, you should employ for loop to go through every row.

In this case, deleting row will be a bit tricky because the index of row might be affected after each deletion. So you’d better delete them from bottom up.

If you want to implement more advanced behavior, you can refer to google sheet API and gradually enhance the script to make it work as you wish.

How to do it with Sheet Automation extension

Finally let’s see how to do the above with Sheet Automation.

If you haven’t installed Sheet Automation, it is an extension in Google Workspace Marketplace and click “Individual Install” to install the extension.

Once it is installed, go to sheet menu Extensions - Sheet Automation and open it. A sidebar should appear on the right side shortly.

All you need to do is creating automation rule with a few parts.

First, create a Column is Updated trigger to on column A of sheet Todo. You could monitor multiple columns here.

Then create a Collumn Condition to check if the value from column A is “Done”

Last create the action to move the row to sheet Done with proper options.

Once the automation rule is saved, it will be ready to react to the change made to column A. There is no magic though. It just does what the above scripts do, in a much easier way.

You may be interested in How to implement two-way sync in Google Sheets.

Comments

Add a Comment