包含多个 onEdit 函数 [英] Bracketing multiple onEdit functions
问题描述
我的 Google 电子表格中有 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
第三个是:
//事件/事件表的自动识别
// 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屋！
