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
- 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());
}
}
}
Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!
Top comments (0)