Bracketing multiple onEdit functions

Question

I have 3 onEdit app script functions in my Google Spreadsheet which work individually but I cannot work out where to put the brackets to nest them.

Database

They are all under function onEdit(e). I understood that you can't separate onEdit functions like you can other functions. Please tell me if I'm wrong.

This is my code which is a little messy and probably needs a tidy up.

// Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet
function onEdit(e) {
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var sheetName = "Unit Standards"
  var range = e.range;
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 2;
  var date = range.getValue();
  // Object.prototype.toString.call(date) === '[object Date]' --> checks if value is date
  // editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
  // sheet.getName() == sheetName --> checks if edited sheet is 'Unit Standards'
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && sheet.getName() == sheetName) {
    var numCols = sheet.getLastColumn();
    var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
    var destinationSheet = ss.getSheetByName("Unit Standards - Employees Left");
    // Get first empty row:
    var emptyRow = destinationSheet.getLastRow() + 1;
    // Copy values from 'Unit Standards'
    destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
    sheet.deleteRow(editedRow);
    sheet.hideColumns(column);
  }
  //Dependent Dropdowns for Event/Incidents Sheet
   {
    var range = e.range;
  var editedRow = range.getRow();

  var spreadsheet = SpreadsheetApp.getActive();
  var dropdownSheet = spreadsheet.getSheetByName("Dropdown Lists");
  var eventsSheet = spreadsheet.getSheetByName("Events/Incidents");

  var baseSelected = eventsSheet.getRange('C' + editedRow).getValue();
  var column;

  switch (baseSelected) {
     case 'EBOP': column = 'A'; break;
    case 'Tauranga': column = 'B'; break;
    case 'Palmerston North': column = 'C'; break;
    case 'Kapiti': column = 'D'; 
  }
  var startCell = dropdownSheet.getRange( column +'4');
  var endCellNotation = startCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation();
  var ruleRange =  dropdownSheet.getRange(startCell.getA1Notation() + ':' + endCellNotation);

  var dropdown1 = eventsSheet.getRange('D' + editedRow);
  var dropdown2 = eventsSheet.getRange('E' + editedRow);

  var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();
  var rule2 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();

  dropdown1.setDataValidation(rule1);
  dropdown2.setDataValidation(rule2);
     }    
    }
    if (ss.getSheetName() == tabValidation) {
      var lock = LockService.getScriptLock();
      if (lock.tryLock(0)) {
        autoid_(ss);
        lock.releaseLock();
      }
    }
    
  }  
}

// Auto ID for Event/Incident Sheet
function autoid_(sheet) {
  var data = sheet.getDataRange().getValues();
  if (data.length < 2) return;
  var indexId = data[1].indexOf('ID');
  var indexDate = data[1].indexOf('Event/Incident Date');
  if (indexId < 0 || indexDate < 0) return;
  var id = data.reduce(
    function(p, row) {
      var year =
        row[indexDate] && row[indexDate].getTime
          ? row[indexDate].getFullYear() % 100
          : '-';
      if (!Object.prototype.hasOwnProperty.call(p.indexByGroup, year)) {
        p.indexByGroup[year] = [];
      }
      var match = ('' + row[indexId]).match(/(\d+)-(\d+)/);
      var idVal = row[indexId];
      if (match && match.length > 1) {
        idVal = match[2];
        p.indexByGroup[year].push(+idVal);
      }
      p.ids.push(idVal);
      p.years.push(year);
      return p;
    },
    { indexByGroup: {}, ids: [], years: [] }
  );

  // Logger.log(JSON.stringify(id, null, '  '));
  var newId = data
    .map(function(row, i) {
      if (row[indexId] !== '') return [row[indexId]];
      if (isNumeric(id.years[i])) {
        var lastId = Math.max.apply(
          null,
          id.indexByGroup[id.years[i]].filter(function(e) {
            return isNumeric(e);
          })
        );
        lastId = lastId === -Infinity ? 1 : lastId + 1;
        id.indexByGroup[id.years[i]].push(lastId);
        return [
          Utilities.formatString(
            '%s-%s',
            id.years[i],
            ('000000000' + lastId).slice(-3)
          )
        ];
      }
      return [''];
    })
    .slice(1);
  sheet.getRange(2, indexId + 1, newId.length).setValues(newId);
}

/**
 *
 * @param {any} n
 * @return {boolean}
 */
function isNumeric(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}

The first function is:

// Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet

The second is:

//Dependent Dropdowns for Event/Incidents Sheet

The third is:

// Auto ID for Event/Incident Sheet

I have looked at answers to previous questions on this and still can't work out how to get the brackets in the right place and get them working. I would really appreciate some help.

Answer 1

It's not very clear what you're trying to do. To start, your second function isn't even defined. So I've tried to answer with what I can understand of your code, but you will have to review the conditions.

I would recommend that you make each of your functions standalone. In your onEdit(), you can then call them whenever a specific condition is met. For example:

function onEdit(e) {
  var sheetName = e.range.getSheet().getName();
  if (sheetName == "Sheet1") {
    // do something
  } else if (sheetName == "Sheet2") {
    // do something else
  }
}

With that kind of structure, you can easily call the functions you need whenever your specific conditions are met. Here is the final code, but again, please review the conditions as I put in dummy values here.

function onEdit(e) {
  var value = e.range.getValue();
  var sheetName = e.range.getSheet().getName();
  if (
    Object.prototype.toString.call(value) === "[object Date]" && // Check if value is a date
    sheetName == "Unit Standards" && // checks if edited sheet is 'Unit Standards'
    e.range.columnStart == 2 && // checks if edited cell is from 'Date Left'
    e.range.rowStart > 4
  ) {
    moveEmployees_(e.range);
  } else if (sheetName == "Sheet2" && e.range.rowStart == 2 && e.range.columnStart == 2) {
    dependentDropdowns_(e.range);
  } else if (sheetName == "Sheet3" && e.range.rowStart == 3 && e.range.columnStart == 3) {
    autoid_(e.range.getSheet());
  }
}

/**
 * Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet
 * @param {Range} range
 */
function moveEmployees_(range) {
  var sheet = range.getSheet();
  var editedRow = range.getRow();
  var column = 2;
  var numCols = sheet.getLastColumn();
  var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
  var destinationSheet = ss.getSheetByName("Unit Standards - Employees Left");
  // Get first empty row:
  var emptyRow = destinationSheet.getLastRow() + 1;
  // Copy values from 'Unit Standards'
  destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
  sheet.deleteRow(editedRow);
  sheet.hideColumns(column);
}

/**
 * Dependent Dropdowns for Event/Incidents Sheet
 * @param {Range} range
 */
function dependentDropdowns_(range) {
  var editedRow = range.getRow();

  var spreadsheet = SpreadsheetApp.getActive();
  var dropdownSheet = spreadsheet.getSheetByName("Dropdown Lists");
  var eventsSheet = spreadsheet.getSheetByName("Events/Incidents");

  var baseSelected = eventsSheet.getRange('C' + editedRow).getValue();
  var column;

  switch (baseSelected) {
    case 'EBOP': column = 'A'; break;
    case 'Tauranga': column = 'B'; break;
    case 'Palmerston North': column = 'C'; break;
    case 'Kapiti': column = 'D';
  }
  var startCell = dropdownSheet.getRange(column + '4');
  var endCellNotation = startCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation();
  var ruleRange = dropdownSheet.getRange(startCell.getA1Notation() + ':' + endCellNotation);

  var dropdown1 = eventsSheet.getRange('D' + editedRow);
  var dropdown2 = eventsSheet.getRange('E' + editedRow);

  var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();
  var rule2 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();

  dropdown1.setDataValidation(rule1);
  dropdown2.setDataValidation(rule2);
}

/**
 * Auto ID for Event/Incident Sheet
 * @param {Sheet} sheet
 */
function autoid_(sheet) {
  var data = sheet.getDataRange().getValues();
  if (data.length < 2) return;
  var indexId = data[1].indexOf('ID');
  var indexDate = data[1].indexOf('Event/Incident Date');
  if (indexId < 0 || indexDate < 0) return;
  var id = data.reduce(
    function (p, row) {
      var year = row[indexDate] && row[indexDate].getTime ? row[indexDate].getFullYear() % 100 : '-';
      if (!Object.prototype.hasOwnProperty.call(p.indexByGroup, year)) {
        p.indexByGroup[year] = [];
      }
      var match = ('' + row[indexId]).match(/(\d+)-(\d+)/);
      var idVal = row[indexId];
      if (match && match.length > 1) {
        idVal = match[2];
        p.indexByGroup[year].push(+idVal);
      }
      p.ids.push(idVal);
      p.years.push(year);
      return p;
    }, { indexByGroup: {}, ids: [], years: [] }
  );

  var newId = data.map(function (row, i) {
    if (row[indexId] !== '') return [row[indexId]];
    if (isNumeric(id.years[i])) {
      var lastId = Math.max.apply(null, id.indexByGroup[id.years[i]].filter(function (e) {
        return isNumeric(e);
      }));
      lastId = lastId === -Infinity ? 1 : lastId + 1;
      id.indexByGroup[id.years[i]].push(lastId);
      return [Utilities.formatString('%s-%s', id.years[i], ('000000000' + lastId).slice(-3))];
    }
    return [''];
  }).slice(1);
  sheet.getRange(2, indexId + 1, newId.length).setValues(newId);
}

/**
 * Check if an object is numeric.
 * @param {*} n
 * @return {boolean}
 */
function isNumeric(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}

Finally, to address your concern about brackets, there is no need to have nested functions here. If you really wanted that though, then you would simply place all of the nested functions directly before the final bracket of the "parent" function.

function parent() {
  var result = isNumeric("abc");
  Logger.log(result); // false
  return result;

  function isNumeric(n) {
    return !isNaN(parseFloat(n)) && isFinite(n);
  }
}