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);
}
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…