Move Row on Edit using Google Sheets
Tags: googlesheets, googleappsscript
Published: 2021-12-11 15:20:00

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6yoau6ag1v5s7298z0s4.png)

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

### Approved Sheet

![Move Row on Edit - Approved Sheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iif96wpkfe2unmavjjnu.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c5pc2kw4fzdhlvvlylh9.png)

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](https://developers.google.com/apps-script/guides/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](https://developers.google.com/apps-script/guides/triggers/events), which we’ll call **e**. It provides helpful information about the event that occurred.

```javascript
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](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet) object, which will provide additional functions we’ll need.

```javascript
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
```

Create a variable called **approvedSheet**. This gets the sheet called Approved and returns a [Sheet](https://developers.google.com/apps-script/reference/spreadsheet/sheet) object, which will provide additional functions we’ll need.

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

Create a variable called **activeSheet**. This gets the active sheet and returns a [Sheet](https://developers.google.com/apps-script/reference/spreadsheet/sheet) object, which will provide additional functions we’ll need.

```javascript
var activeSheet = SpreadsheetApp.getActiveSheet();
```

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

```javascript
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.

```javascript
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.

```javascript
var value = e.value;
```

Create a variable called **lock**. This returns a [Lock](https://developers.google.com/apps-script/reference/lock/lock) object. We’ll use some of its methods to lock the spreadsheet while we’re in the process of moving a row.

```javascript
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.

```javascript
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.

```javascript
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.

```javascript
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

```javascript
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](https://nightwolf.dev) and follow us on [Twitter](https://twitter.com/nightwolfdev)!**