0

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());
}}
3
  • 1
    That's quite a wall of code there. Can you dump the comments and distill it down the minimum code required to reproduce the issue?
    – ale
    Commented Jun 21, 2019 at 12:03
  • This will sound really stupid, but can I remove all the 'orange' text from within the script editor (this looks to be the comments)?
    – user221314
    Commented Jun 21, 2019 at 12:09
  • I've edited my original script (see main post) - and tried a few variants, no joy
    – user221314
    Commented Jun 21, 2019 at 13:23

1 Answer 1

1

nested scenario:

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

function moveRowsFromSheetToSheet_(e) {
  // start of code
  if(condition == met) {
    //operation to follow
  } //closes if statement operations; no close to moveRowsFromSheetToSheet_()

  function moveRowsFromSheetToSheet1_() {
    //start of code
    if(condition == met){   
      //operation to follow
    } //closes if statement operation
  } //closes moveRowsFromSheetToSheet1_()
  moveRowsFromSheetToSheet1_; //calls moveRowsFromSheetToSheet1_() so that it will run after moveRowsFromSheetToSheet_() code
} // closes moveRowsFromSheetToSheet_()

unnested scenario:

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

function moveRowsFromSheetToSheet_(e) {
  // start of code
  if(condition == met) {
    //operation to follow
  } //closes if statement operations


function moveRowsFromSheetToSheet1_() {
  //start of code
  if(condition == met){   
    //operation to follow
  }
}
2
  • Hi, I've tried a couple of versions but still no joy :-(
    – user221314
    Commented Jun 21, 2019 at 13:20
  • I've edited my original script (see main post) - and tried a few variants, no joy
    – user221314
    Commented Jun 21, 2019 at 13:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.