包围多个onEdit功能 [英] Bracketing multiple onEdit functions
问题描述
我的Google Spreadsheet中有3个onEdit应用程序脚本函数，这些函数可以单独工作，但是我无法确定将括号嵌套的位置。
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.
它们都在onEdit（e）函数下。我知道您无法像其他功能一样分开onEdit函数。
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);
}
第一个函数是：
//削减从单位标准表留下的员工并粘贴到单位标准-员工剩余表中
// Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet
第二个是：
//事件/事件表的相关下拉列表
//Dependent Dropdowns for Event/Incidents Sheet
第三个是：
//事件/事故单的自动ID
// 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.
推荐答案
目前尚不清楚您要做什么。首先，您的第二个功能甚至都没有定义。因此，我试图用我能理解的代码来回答问题，但是您将必须检查条件。
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.
我建议您使用每个函数独立的。然后，只要满足特定条件，就可以在 onEdit（）中调用它们。例如：
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);
}
}
这篇关于包围多个onEdit功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
