I'm writing a script that takes in 2 dates (start and end date), creates new columns for each week between the two dates, then auto-fills the start of each week in the header of the new columns in a sheet called 'Timeline & Financials'. The function is called nm().

I'm using the onEdit function to trigger the script when either the start (cell W1) or end date (cell X1) cell in 'Timeline & Financials' changes.

Users are not supposed to edit cells W1 or X1 manually. Those cells are linked to cells in another tab called 'Reousrce(s)' in the same spreadsheet via the function =MIN('Resource(s)' !G2:G900) and =MAX('Reousrce(s)' !H2:H900) To clarify, W1 and X1 in 'Timeline & Financials' contain the min and max function

My goal is to have users edit the 'Resource(s)' tab which will change W1 or X1 in 'Timeline & Financials' then trigger the function that creates the columns.

My problem is finding a way for onEdit to recognize when a cell has been changed then trigger the script.

I found 2 solutions to specify which cells must be edited to trigger but each have issues:

Solution 1:

The function successfully triggers when W1 or X1 updates due a change in the 'Resource(s) sheet but it unintentionally runs the script when any cell in 'Timeline & Financials' is changed.

How can I limit the trigger to only W1 or X1 using this solution? Am I misunderstanding the use of .getA1Notation()?

Solution 2

The function will only successfully trigger if the user goes in and manually types in the dates in W1 or X1, not when 'Resource(s)' updates W1 or X1.

My guess is that this solution only checks the function in the cell (=MIN('Resource(s)' !G2:G900) and =MAX('Reousrce(s)' !H2:H900)) rather than the value in the cell (the actual dates)

Is there a way for script to look out an edit in value in a cell rather than a change of a function?

The code is below:

function onEdit(e){
  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timeline & Financials");
  var startDate = "V3"; 
  var endDate = "V4";

  // Solution 1
  // if (e.range.getA1Notation() == startDate || e.range.getA1Notation() == endDate ){
  //     console.log("in if");
  //     return;
  // } else {
    
  //   deleteColumns();
      
  //     nm();
  // }


 // Solution 2
 //  if (e.range.rowStart != 1 ||  !(e.range.columnStart >= 23 && e.range.columnStart <    25) ){
 //    console.log("in if");
 //    return;
 //   } else {
 // 
 //  deleteColumns();
 //    
 //  nm();
 //  }

} ```