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!
Table of Contents
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());
}
}
}
Related Articles
- Create A Unique ID Using A Custom Function In Google Sheets
- Populate and Filter Dates Between Start/End Dates in Google Sheets
- Create Your Own APIs with Google Sheets and Google Apps Script (Part 1)
- Create a Custom Function in Google Sheets
- Create Your Own APIs with Google Sheets and Google Apps Script (Part 3)