Google-sheets – How to pass an entire sheet as a parameter to a Google Sheets function with dependencies

google sheetsgoogle-sheets-custom-function

I have a Google Sheets app script that is passed the name of a sheet. Internally it pulls the data from that sheet and returns a value. Something like this:

function myFunction1(partNumber) {
  var sheetName = partNumber;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();
  var result = someProcessingOnTheSheetData(values);
  return result;
}

I did it this way because the users of the function can simply specify the sheet name when they use it in a formula:

(cell A1 has sheet name)
=myFunction1(A1);

Sexy. But the problem is Google Sheets has no idea that this function refers to the data on a sheet, so it won't recalculate it when data on Sheet1 changes. Less Sexy.

I found a way of solving this by passing in a range on the sheet using INDIRECT:

function myFunction2(values) {
  var result = someProcessingOnTheSheetData(values);
  return result;
}

And calling it in a formula like:

(cell A1 has sheet name)
=myFunction2(INDIRECT(A1&"!A:Z"))

This works because Google Sheets seems to figure out how to keep track of the formula's dependencies even with INDIRECT and re-run the function.

But, less sexy. My users now need to make sure they get this bizarre (to them) syntax right or no recalculation and chaos ensues.

Is there a better way? Can a function somehow communicate it's dependencies to the spreadsheet side? Any intermediate improvement I can make in the way the formula is written?

Best Answer

in order to have Google Spreadsheets reevaluate a formula, the input arguments to the formula must get new values.

In your case, the only input parameter is argument is the name of a sheet, so the formula won't be evaluated as long as the sheet name stays the same (unless you specify a refresh interval in the spreadsheet's settings).

But you want to reevaluate the formula when some data changes. This means you should have that data, or a function of it, as argument to your formula.

So instead of passing the sheet name, and let your script extract the sheet data:

=myFunction1("ProductSheet");

function myFunction1(sheetName) {
  ...
  var sheet = spreadsheet.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();
  var result = someProcessingOnTheSheetData(values);
  ...

... pass the values directly to your function:

=myFunction1(ProductSheet!A1:X);

function myFunction1(values) {
  var result = someProcessingOnTheSheetData(values);
  ...

Granted, this will make it a bit more difficult for your users to enter the correct formula.

If you prefer to keep the ability to pass just the sheet name, you'll need to introduce some kind of value that is derived from the data you're processing, that will change when the data changes. An obvious candidate is to introduce a "last updated" timestamp value for each sheet. This timestamp could be calculated from your data, manually updated, or updated by a script (see this answer for inspiration). Once that is in place, you could keep the last updated timestamp in a cell next to the sheet name, e.g. if A1 has the sheet name, keep its last updated timestamp in B1.
This will allow you to write the formula as:

=myFunction1(A1, B1)

... and the result will update every time the value of B1 changes. Note that you don't have to actually use the B1 value in your script - it is enough to have it as input to your formula.

I have created an example spreadsheet to demonstrate. Feel free to copy it to your own Google Drive, and experiment with it all you want.

Related Topic