Event Objects
Even though the code was written as onEdit(e), you didn't take advantage of the Event Objects.
In this answer, the code returns the new value of the edited cell and also the range. The range is then used to work out the row, column and sheet name and these is used for validation as well as for building the ranges and the setFormula
Variables
The code includes variables for the valid range of columns that can be used for data entry (Column C to Column H), and respective input rows (rows 9 and 10). These are expressed as values, but they could just as easily be written into the spreadsheet as assumptions and the values obtained in the code by using getValue.
The absolute cell references used in the setFormula are partly variable (column reference) and part hard-coded (the respective rows-3,4 and 5). If desired, the rows could be variable as well.
Efficiency
There is just one if statement containing one version of the code to build setFormula.
This is achieved by designing the if statement:
1. if the sheet = "Date Calculator" AND
2. if the editColumn is between the valid ColumnStart and ColumnEnd values (Column C to H) AND
3. if the editRow is between the valid Row values (rows 9 or 10) AND
4. if the edited value isn't a blank (length != 0).
The last condition ("edited value is blank") ensures that if cell contents are been deleted (and/or have no value), then the code won't proceed.
Convert column number to letter
I used a routine written by @AdamL found at Convert column index into corresponding column letter; this converts a column number into a letter. It's used to build the "targetcolumn" address in Workdays. It's valid for the letters A-Z; there's a version for letters beyond Z.
Cleanup
If data is entered into row 10 of a given column, then any value in row 9 (of the same column) needs to be deleted. The code does this and also deletes any pre-existing formula dates in the rows below so there is no confusion about the dates derived by the data entry.
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
