Google sheets: How do I get my custom function to refresh automatically?

Question

I have written a Google Sheet application for myself that (among other things) has a schedule for consultants to work on projects. I have written a custom function that displays summary information from the calendar; for example:

Tubb, Ernest|Project 1
Tubb, Ernest|Project 2
Perkins, Carl|Project 1

I wrote the function because the mess of FILTER() functions I was using was incomprehensible; and the javascript required to do the same thing is relatively straightforward.

What I find however, is that when the underlying data changes, the cells written by my function are NOT recalculated the way they were with the FILTER() function. How can I program some sort of listener that makes my function's output be refreshed when the underlying data changes? Do I have to do this by hand with the onEdit() function?

And here is my function:

// Global Constants - these will change each quarter based on the calendar structure.
var CALENDAR_QUARTER_START = "F"
var CALENDAR_QUARTER_END = "CS"

// Generate a nice 2-column display of Consultant and Project(s) they are scheduled on,
// based on the consultant names and calendar codes in the Calendar tab.
// "billable" parameter specifies the return of only uppercase activities (true) or all activities (false)
function fortifyConsultantsAndProjects( billable ){

  // Resolve arguments
  var billable = arguments[0];
  if (arguments[0] == undefined) billable = true;

  var calendar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar");
  var consultants = calendar.getRange( "A8:A" ).getValues();

  var ret = [];
  var row = 0;
  for ( var i=0; i<consultants.length; i++ ){
    var consultant = consultants[i].toString();
    var projects = fortifyGetProjectsForConsultant( consultant, billable );
    for (var j=0; j < projects.length; j++ ) {
      ret.push( [] );
      ret[row][0] = consultant;
      ret[row][1] = projects[j];
      row++;
    }
  }
  return ret;
}

function fortifyGetProjectsForConsultant( consultant, billable ){

  // Resolve arguments
  var consultant = arguments[0];
  if (arguments[0] == undefined) consultant = "Held, Doug";
  var billable = arguments[1];
  if (arguments[1] == undefined) billable = true;

  // Get the range of consultants defined in Column A of Calendar tab.
  var calendar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar");
  var consultants = calendar.getRange( "A8:A" ).getValues();

  // Identify which Calendar row contains the specified consultant's schedule.
  var row;
  for ( var i=0; i< consultants.length; i++){
    //Browser.msgBox( "compare " + consultant + " and " + consultants[i] );
    if ( consultants[i].toString() == consultant ){
      row = i+8;
      break;
    }
  }

  // Obtain all the consultant's schedulings. Contains many duplicates, so sort.
  var projects = calendar.getRange( CALENDAR_QUARTER_START + row + ":" + CALENDAR_QUARTER_END + row + "" ).getValues()[0].sort();

  // Iterate through the sorted project codes, removing duplicates and blanks
  var ret = [];
  var row = 0;
  var prev = "";
  for ( var i = 0; i< projects.length; i++ ){
    var temp = projects[i].toString();
    if (temp != "" && temp != prev ){

      // Resolve whether to return each row based on project billability (uppercase)
      if ( temp.toUpperCase() == temp || billable == false ){
        ret[row] = temp;
        row++;
      }
      prev = temp;

    }
  }
  return ret;
}

Answer 1

Well since nobody ever answered, I will explain how I solved the problem.

Instead of a zero-argument function parameter list, I receive all of the ranges (for instance the range Calendar!A8:A that you see hard coded here). Then any time data in the parameter ranges changes, the function is invoked again. This does occasionally cause some sort of runtime error in Google sheets - on update, I sometimes get "the document is not available" and an instruction to reload the sheet.

When a range is received as a parameter, that data is received byvalue as a 2 dimensional array of cell values -- the same as the array returned from Range.getValues().