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());
}}
  • 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 Jun 21 '19 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 Jun 21 '19 at 12:09
  • I've edited my original script (see main post) - and tried a few variants, no joy – user221314 Jun 21 '19 at 13:23
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
  }
}
  • Hi, I've tried a couple of versions but still no joy :-( – user221314 Jun 21 '19 at 13:20
  • I've edited my original script (see main post) - and tried a few variants, no joy – user221314 Jun 21 '19 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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