Is there a way to make custom functions in Google Sheets update automatically?

Other Questions Google Sheets
1

I’ve created a Google Sheet for managing consultant schedules on projects. I wrote a custom function to show summary info from the calendar. It looks like this:

Smith, John|Project A
Smith, John|Project B
Jones, Mary|Project A

I made this function because using lots of FILTER() functions was confusing. The JavaScript for this is pretty simple.

But there’s a problem. When I change the data, the cells with my function don’t update like they did with FILTER(). How can I make my function’s output refresh when the data changes? Do I need to use onEdit() or is there another way?

Here’s a simplified version of my function:

function showConsultantProjects(billableOnly) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Schedule');
  var consultants = sheet.getRange('A2:A').getValues();
  var projects = sheet.getRange('B2:D').getValues();

  var result = [];
  for (var i = 0; i < consultants.length; i++) {
    var consultant = consultants[i][0];
    for (var j = 0; j < projects[i].length; j++) {
      var project = projects[i][j];
      if (project && (!billableOnly || project === project.toUpperCase())) {
        result.push([consultant, project]);
      }
    }
  }
  return result;
}

Any ideas on how to make this update automatically?

2

I’ve encountered this issue as well. Unfortunately, custom functions in Google Sheets don’t automatically recalculate like native functions. One workaround is to use a time-driven trigger to periodically refresh your sheet. You can set this up in the Script Editor under Edit > Current project’s triggers. Choose to run a function on a time-based schedule, like every hour or every 15 minutes. In that function, you could force a recalculation of your custom function cells. It’s not perfect, but it can help keep your data more up-to-date without manual refreshing. Just be mindful of quota limits if you’re running it frequently.

3

hey there, i’ve dealt with this before. custom functions don’t auto-update like built-in ones. you could try using the ARRAYFORMULA() function to wrap your custom function. it might force recalculation when data changes. worth a shot! let me know if it works for ya.

4

I’ve faced similar challenges with custom functions in Sheets. One approach that’s worked well for me is using the SpreadsheetApp.flush() method within your function. This forces the spreadsheet to update all pending changes.

Here’s how you could modify your function:

function showConsultantProjects(billableOnly) {
  // Your existing code here

  SpreadsheetApp.flush();
  return result;
}

This should trigger a recalculation when the underlying data changes. It’s not perfect, but it’s helped in most of my use cases.

Another trick is to add a volatile function like NOW() to your custom function call in the sheet. For example, using =showConsultantProjects(true, NOW()) forces a more frequent recalculation.

Hope this helps! Let me know if you find another solution that works for you.