事件对象
即使代码写为 onEdit(e),您也没有利用事件对象。
在此答案中,代码返回已编辑单元格的新值以及范围。然后,该范围用于计算行、列和工作表名称,这些名称用于验证以及构建范围和工作表setFormula
变量
该代码包括可用于数据输入的有效列范围的变量(C 列到 H 列)以及相应的输入行(第 9 行和第 10 行)。这些被表示为值,但它们可以像假设一样轻松地写入电子表格,并且通过使用在代码中获得的值getValue
.
中使用的绝对单元格引用setFormula
部分是可变的(列引用),部分是硬编码的(相应的行 3,4 和 5)。如果需要,行也可以是可变的。
效率
只有一个if
包含要构建的代码的一个版本的语句setFormula
。
这是通过设计实现的if
陈述:
1. 如果工作表=“日期计算器”并且
2. 如果 editColumn 位于有效的 ColumnStart 和 ColumnEnd 值之间(C 列到 H 列)并且
3. 如果 editRow 位于有效行值之间(第 9 行或第 10 行)并且
4. 如果编辑的值不是空白(长度!= 0)。
最后一个条件(“编辑的值为空”)确保如果单元格内容被删除(和/或没有值),则代码将不会继续。
将列号转换为字母
我使用了 @AdamL 编写的例程,位于将列索引转换为对应的列字母 https://stackoverflow.com/a/21231012/1330560;这会将列号转换为字母。它用于构建“目标列”地址Workdays
。它对字母 A-Z 有效;有一个版本适用于 Z 以外的字母。
Cleanup
如果数据输入到给定列的第 10 行,则需要删除第 9 行(同一列)中的任何值。该代码执行此操作,并删除下面行中任何预先存在的公式日期,因此不会对数据输入派生的日期产生混淆。
function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "Date Calculator";
var sheet = ss.getSheetByName(sheetname);
// get the event source data
var editedCell = e.range;
var editRow = editedCell.getRow();
var editCol = editedCell.getColumn();
var eValue = e.value;
var editedSheet = editedCell.getSheet().getName();
//Logger.log("DEBUG: the cell = "+editedCell.getA1Notation()+", the column = "+editCol+", the row is "+editRow+", the value is "+eValue+", the edited sheet is "+editedSheet);
// create some variables for column and row range
var columnStart = 3; // Column C
var columnEnd = 8; // Column H
var rowOption1 = 9; // row 9
var rowOption2 = 10 // row 10
// create some variables for target cells
var absolutecolumn = "C";
//var absoluterow1 = 3; // not used
//var absoluterow2 = 4; // not used
//var absoluterow3 = 5; // not used
// test for valid edit in row option 1 // Row 9
if(editedSheet === sheetname && columnEnd >=editCol && editCol>=columnStart && rowOption2>=editRow && editRow>=rowOption1 && eValue.length !=0 ){
//Logger.log("DEBUG: You got the right sheet, the edit is in the right range of columns and the edited row was = "+rowOption1);
if (editRow == rowOption2){
// clear row 9
sheet.getRange((+editRow-1),editCol).clear();
}
// clear following 8 rows of data
sheet.getRange((+editRow+1),editCol,8).clear();
// set the targetcolumn as a letter
var targetcolumn = columnToLetter(editCol);
// set formula for row+1
sheet.getRange((+editRow+1),editCol).setFormula("=WORKDAY("+targetcolumn+editRow+",$"+absolutecolumn+"$3)"); //
// set formula row +2
sheet.getRange((+editRow+2),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+1)+",+10)");
// set formula row +3
sheet.getRange((+editRow+3),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+2)+",$"+absolutecolumn+"$4)");
// set formula row +4
sheet.getRange((+editRow+4),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+3)+",$"+absolutecolumn+"$3)");
// set formula row + 5
sheet.getRange((+editRow+5),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+4)+",+10)");
// set formula row + 6
sheet.getRange((+editRow+6),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+5)+",+1)");
// set formula row + 7
sheet.getRange((+editRow+7),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+6)+",$"+absolutecolumn+"$5)");
// change the background to show entry in rowoption1
sheet.getRange(editRow,editCol).setBackground("yellow");
sheet.getRange((+editRow+1),editCol).setBackground("white");
}
}
function columnToLetter(column)
{
var temp, letter = '';
while (column > 0)
{
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
Screenshot