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

1. Create a sheet called Submitted.
2. Create the following columns:
   • First Name
   • Last Name
   • Move

Approved Sheet

1. Select the Submitted sheet's menu and choose Duplicate.
2. Rename the sheet to Approved.

Script Editor

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

1. Navigate to Extensions > Apps Script.
2. The script editor will include a starting function. You can remove all the code.
3. 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());

    }

  }
}

Visit our website at https://nightwolf.dev and follow us on Twitter!