/**
* Moves a row from a sheet to another when a magic value is entered in a column.
* The name of the sheet to move the row to is given by the magic value.
*
* To take this script into use:
*
* - take a backup of your spreadsheet through File > Make a copy
* - select all the text in this script, starting at the first "/**" line above, and ending at the last "}"
* - copy the script to the clipboard with Control+C (on a Mac, ⌘C)
* - open the spreadsheet where you want to use the function
* - choose Tools > Script editor > Blank (this opens a new tab in the browser)
* - if you see just the 'function myFunction() {}' placeholder, press Control+A (on a Mac, ⌘A)
* followed by Control+V (⌘V) to paste the script in
* - otherwise, choose File > New > Script file, then press Control+A (on a Mac, ⌘A) followed by
* Control+V (⌘V) to paste the script in
* - if you have an existing onEdit(e) function, add the following line as the first line after the
* initial '{' in that onEdit(e) function:
* moveRowsFromSheetToSheet_(e);
* ...and then delete the onEdit(e) function below (5 lines of comments, 3 lines of code)
* - press Control+S (⌘S) to save the script
* - when prompted, name the project 'moveRowsFromSheetToSheet'
* - close the script editor tab and go back to the spreadsheet tab
* - the script will run automatically when you edit a cell in the column that is being watched
*
* see https://productforums.google.com/d/topic/docs/icrZ3WPMRoU/discussion
*/
/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
moveRowsFromSheetToSheet_(e);
moveRowsFromSheetToSheet1_();
}
/**
* Moves a row from a sheet to another when a magic value is entered in a column.
* The name of the sheet to move the row to is given by the magic value.
*
* @param {Object} e The onEdit() event object.
*/
function moveRowsFromSheetToSheet_(e) {
// version 1.1, written by --Hyde, 29 October 2018
// - clears the cell in column F of the row that was moved to a new sheet to allow the array formula to work
// - only move rows when they are in the unfrozen area of the sheet
// - switched from SpreadsheetApp.getActiveSpreadsheet() to e.source, etc.
// version 1.0, written by --Hyde, 14 July 2015
///////////////////
// adjust the following variables to fit your needs
var columnNumberToWatch = 13; // column A = 1, B = 2, etc.
var sheetNamesToWatch = ['REVIEW'['Review','LIVE''Live', 'WON''Won', 'LOST'];'Lost'];
var valuesToWatch = ['REVIEW'['Review','LIVE''Live', 'WON''Won', 'LOST'];'Lost'];
var targetSheetsToMoveTheRowTo = ['REVIEW'['Review','LIVE''Live', 'WON''Won', 'LOST'];'Lost'];
///////////////////
var sheet = e.range.getSheet();
if (sheetNamesToWatch.indexOf(sheet.getName()) == -1
|| valuesToWatch.indexOf(e.range.getValue()) == -1
|| e.range.getColumn() != columnNumberToWatch
|| e.range.getRow() <= sheet.getFrozenRows()) {
return;
}
var targetSheet = e.source.getSheetByName(targetSheetsToMoveTheRowTo[valuesToWatch.indexOf(e.range.getValue())]);
var targetSheetColumnAValues = targetSheet.getRange(
/*row*/ 1,
/*column*/ 1,
/*numRows*/ targetSheet.getLastRow(),
/*numColumns*/ 2).getDisplayValues();
var row = targetSheetColumnAValues.length - 1;
while (row && !(targetSheetColumnAValues[row][0].length || targetSheetColumnAValues[row][1].length)) {
row--;
}
row += 1;
if (row < targetSheet.getFrozenRows()) {
var error = 'moveRowsFromSheetToSheet_() encountered a badly formatted target column on sheet "' + targetSheet.getName() + '" (row = ' + row + ').';
SpreadsheetApp.getActive().toast(error, 'Error', 20);
throw new Error(error);
}
var targetRange = targetSheet.getRange(row + 1, 1);
sheet.getRange(e.range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
targetRange.offset(/*row*/ 0, /*column F*/ 6 - 1).clearContent(); // clear the value in column F to allow the array formula to work
sheet.deleteRow(e.range.getRow());
}
/**
* Moves a row from a sheet to another when a magic value is entered in a column.
* The name of the sheet to move the row to is given by the magic value.
*
* @param {Object} e The onEdit() event object.
*/
function moveRowsFromSheetToSheet1_(e) {
// version 1.1, written by --Hyde, 29 October 2018
// - clears the cell in column F of the row that was moved to a new sheet to allow the array formula to work
// - only move rows when they are in the unfrozen area of the sheet
// - switched from SpreadsheetApp.getActiveSpreadsheet() to e.source, etc.
// version 1.0, written by --Hyde, 14 July 2015
///////////////////
// adjust the following variables to fit your needs
var columnNumberToWatch = 9; // column A = 1, B = 2, etc.
var sheetNamesToWatch = ['Account', 'Prospect', 'Archive'];
var valuesToWatch = ['Account', 'Prospect', 'Archive'];
var targetSheetsToMoveTheRowTo = ['Account', 'Prospect', 'Archive'];
///////////////////
var sheet = e.range.getSheet();
if (sheetNamesToWatch.indexOf(sheet.getName()) == -1
|| valuesToWatch.indexOf(e.range.getValue()) == -1
|| e.range.getColumn() != columnNumberToWatch
|| e.range.getRow() <= sheet.getFrozenRows()) {
return;
}
var targetSheet = e.source.getSheetByName(targetSheetsToMoveTheRowTo[valuesToWatch.indexOf(e.range.getValue())]);
var targetSheetColumnAValues = targetSheet.getRange(
/*row*/ 1,
/*column*/ 1,
/*numRows*/ targetSheet.getLastRow(),
/*numColumns*/ 2).getDisplayValues();
var row = targetSheetColumnAValues.length - 1;
while (row && !(targetSheetColumnAValues[row][0].length || targetSheetColumnAValues[row][1].length)) {
row--;
}
row += 1;
if (row < targetSheet.getFrozenRows()) {
var error = 'moveRowsFromSheetToSheet_() encountered a badly formatted target column on sheet "' + targetSheet.getName() + '" (row = ' + row + ').';
SpreadsheetApp.getActive().toast(error, 'Error', 20);
throw new Error(error);
}
var targetRange = targetSheet.getRange(row + 1, 1);
sheet.getRange(e.range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
targetRange.offset(/*row*/ 0, /*column F*/ 6 - 1).clearContent(); // clear the value in column F to allow the array formula to work
sheet.deleteRow(e.range.getRow());
}}
