This will likely be an easy question to answer but its an issue I run into often. Most built in google sheet functions change dynamically when cell values are updated, I need my function to do the same rather than needing to run the function to check the dates. The following script is likely messy to anyone who knows what they're doing, but it works. It is built to scan if todays date matches any in the range named "Dates" and return the value in that objects column and row of the active cell. However, this only activates when I first plug the function into the cell. I need it to change the cells value whenever one of those dates matches todays date or when none of them match todays date.

function getClass() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var dateRanges = ss.getRangeByName("Dates");
  var dateValues = dateRanges.getValues();
  var studentRow = sheet.getActiveCell().getRow();
  var todaysDate = Utilities.formatDate(new Date(),"00:00:00", "MM/dd/yyyy");
  var datesWithCells = [];
  
  for (i=0;i<=dateRanges.getNumColumns() -1;i  ){
    var date = [Utilities.formatDate(dateValues[0][i], "00:00:00", "MM/dd/yyyy")];
    var col = [dateRanges.getColumn()   i]
    datesWithCells.push([col,date]);
    };
  
  for (i=0;i<=dateRanges.getNumColumns() -1;i  ){;
    var dataCol = datesWithCells[i][0];
    if (datesWithCells[i][1] == todaysDate){
      return sheet.getRange(studentRow,dataCol).getValue();
    } else {
      return "N/A";
    };
  };
}

Image shows the cells correctly displaying N/A as no dates match todays date. If todays date did match, it would not change value because the function has already ran and will not update this value as needed

CodePudding user response：

It is unclear why you would want to do that with a custom function, because a plain vanilla spreadsheet formula would seem to suffice:

=hlookup(today(), D2:Z, row() - row(D2)   1, false)

To answer your question, you should not hard-code the location of the data in the custom function. Instead, pass the values to the custom function through parameters. The formula that uses a custom function will update its result automatically when its parameters change.