Skip to main content
3 of 4
deleted 3030 characters in body

Google Sheets onEdit - Run Two Scripts

I'm trying to run two scripts in one sheet - Only the last one runs though - I'm sure I'm missing something small but any ideas?


function onEdit(e) {
  moveRowsFromSheetToSheet_(e);
  moveRowsFromSheetToSheet1_(); 
}

function moveRowsFromSheetToSheet_(e) {

  var columnNumberToWatch = 13; // column A = 1, B = 2, etc.
  var sheetNamesToWatch =          ['Review','Live', 'Won', 'Lost'];
  var valuesToWatch =              ['Review','Live', 'Won', 'Lost'];
  var targetSheetsToMoveTheRowTo = ['Review','Live', 'Won', '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());

function moveRowsFromSheetToSheet1_(e) {
 
  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());
}}