Move Row on Edit using Google Sheets

December 11, 2021 • Google Apps Script, Google Sheets • Published By Josh • 4 minute read

Have you ever needed to move a row of data from one sheet to another when entering a specific value into a specific column? With this article, you’ll learn how using the onEdit trigger and some Apps Script coding!

Spreadsheet Setup
Submitted Sheet
Approved Sheet
Script Editor
Final Code

Spreadsheet Setup

Submitted Sheet

Create a sheet called Submitted.
Create the following columns:
- First Name
- Last Name
- Move

Approved Sheet

Select the Submitted sheet’s menu and Choose Duplicate.
Rename the sheet to Approved.

Script Editor

Let’s start writing some code! Google Sheets has a handy script editor available.

Navigate to Extensions > Apps Script.
The script editor will include a starting function. You can remove all the code.
Navigate to File > Save. Give the script project a name and select Ok.

Create a function called onEdit. This reserved function name is one of the built in simple triggers Apps Script provides. This will run automatically when a user changes the value of any cell in the spreadsheet. It’s passed an event object, which we’ll call e. It provides helpful information about the event that occurred.

function onEdit(e) {
 
}

Let’s continue adding logic to the onEdit function.

Create a variable called activeSpreadsheet. This gets the active spreadsheet and returns a Spreadsheet object, which will provide additional functions we’ll need.

var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

Create a variable called approvedSheet. This gets the sheet called Approved and returns a Sheet object, which will provide additional functions we’ll need.

var approvedSheet = activeSpreadsheet.getSheetByName('Approved');

Create a variable called activeSheet. This gets the active sheet and returns a Sheet object, which will provide additional functions we’ll need.

var activeSheet = SpreadsheetApp.getActiveSheet();

Create a variable called numColumns. We’ll need this later when getting the range of columns for the row.

var numColumns = activeSheet.getLastColumn();

Create a variable called cell. Remember the event object passed into the onEdit function? It provides the cell range that was edited.

var cell = e.range;

Create a variable called value. Remember the event object passed into the onEdit function? It provides the value that was edited in the cell range.

var value = e.value;

Create a variable called lock. This returns a Lock object. We’ll use some of its methods to lock the spreadsheet while we’re in the process of moving a row.

var lock = LockService.getScriptLock();

We want to move a row from the Submitted sheet to the Approved sheet when the user enters Y in the Move column of the Submitted sheet. We need to check if the user is on the Submitted sheet. We need to check if they entered a value of Y. We need to check if that value was entered in the Move column (column 3). Let’s set up that condition.

if (activeSheet.getSheetName() == 'Submitted' && cell.getColumn() == 3 && value == 'Y') {
 
}

Let’s continue adding logic to the if condition.

There could be multiple users doing something in the spreadsheet. We only want to move the row if no one else is taking a similar action at the same time. So we’ll wait for 10,000 milliseconds (10 seconds) to try and get a lock.

lock.tryLock(10000);

If we can’t get a lock, let’s display a message to the user. If a lock was obtained successfully, let’s proceed with getting the data in the row, copying it to the Approved sheet, and removing it from the Submitted sheet.

if (!lock.hasLock()) {
 
  // Could not obtain lock so tell user to try again in a moment.
  activeSpreadsheet.toast('Spreadsheet is busy, please try again in a moment.');
 
} else {
 
  // Get data in edited row.
  var row = activeSheet.getRange(cell.getRow(), 1, 1, numColumns).getValues();
 
  // Copy row to Approved sheet.
  approvedSheet.appendRow(row[0]);
 
  // Remove row from Submitted sheet.
  activeSheet.deleteRow(cell.getRow());
 
}

All that’s left to do is test it out! Enter a Y in the Move column for one of the rows in the Submitted sheet. It will get copied to the Approved sheet and removed from the Submitted sheet!

Final Code

function onEdit(e) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var approvedSheet = activeSpreadsheet.getSheetByName('Approved');
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var numColumns = activeSheet.getLastColumn();
  var cell = e.range;
  var value = e.value;
  var lock = LockService.getScriptLock();
 
  // Make sure user is on the Submitted sheet in column 3 and the value is Y.
  if (activeSheet.getSheetName() == 'Submitted' && cell.getColumn() == 3 && value == 'Y') {
 
    // Wait for 10 seconds for other potential users to finish.
    lock.tryLock(10000);
 
    if (!lock.hasLock()) {
 
      // Could not obtain lock so tell user to try again in a moment.
      activeSpreadsheet.toast('Spreadsheet is busy, please try again in a moment.');
 
    } else {
 
      // Get data in edited row.
      var row = activeSheet.getRange(cell.getRow(), 1, 1, numColumns).getValues();
 
      // Copy row to Approved sheet.
      approvedSheet.appendRow(row[0]);
 
      // Remove row from Submitted sheet.
      activeSheet.deleteRow(cell.getRow());
 
    }
 
  }
}