Google-sheets – Google Sheets onEdit – Run Two Scripts

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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());
}}

Best Answer

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
  }
}

Related Solutions

Google Apps Script – Change onEdit Function to onOpen

Rename your script to a normal function, something like setColor. Rewrite the script to contain all the proper references. After that, you can select the proper trigger in the script editor:

enter image description here

Select the onOpen trigger for the function.

The second this you can do, is to create a menu item in the active spreadsheet, like this:

var menu = [{name: "Set Color", functionName: "setColor"}];
ss.addMenu("Extra", menu);

If you add it to the existing script, it will be added on opening of the file. While your at it, use the following script:

function foramtName() {
  var ss = SpreadsheetApp.getActiveSheet();
  var mRows = ss.getMaxRows()-1, mCols = ss.getMaxColumns();
  var cRange = ss.getRange(2, 1, mRows, mCols);

  if (ss.getName() == "e404") {
    var aData = ss.getRange(2, 4, mRows, 4).getValues(); 
    var colors = new Array();    
    for(i in aData) {
      colors[i] = new Array();
      for(var j=0; j<mCols; j++) {
        if(aData[i].reduce(function(a, b) {return a + b}) === 0) {
          colors[i][j] = 'red';
        } else {
          colors[i][j] = 'white';
        }
      }       
    }
    cRange.setBackgrounds(colors);
  }
}

Google-sheets – Run onEdit script in one sheet only

Checking the name of the sheet would be like this:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == 'Inventory') {
    // the rest of the script
  }
}

The line you were trying, with event.source.getActiveSheet() would work if you actually took the event object as a parameter: onEdit(event).

function onEdit(event) {
  var s = event.source.getActiveSheet(); 
  //  etc
}