Move Row on Edit using Google Sheets

nightwolfdev - Dec 11 '21 - - Dev Community

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

Move Row on Edit - Submitted Sheet

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

Approved Sheet

Move Row on Edit - 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.

Apps Script Editor

  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) {

}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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

var numColumns = activeSheet.getLastColumn();
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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') {

}
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

}
Enter fullscreen mode Exit fullscreen mode

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

    }

  }
}
Enter fullscreen mode Exit fullscreen mode

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .