1

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?

1 Answer 1

1

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.

3
  • The key thing I'm looking for is the leverage what INDIRECT does, where it appears to figure out what the dependencies are from the way the reference is calculated. I guess I was hoping for some kind of "alias" spreadsheet function that would let me define foo(x) = myFunction2(INDIRECT(X&"!A:Z"))
    – Jim B.
    Oct 20, 2019 at 21:25
  • Yes, evaluation of the INDIRECT function seems to be deferred. So the reason the INDIRECT trick works, is that you're basically passing the entire data range as argument to myFunction2. If you have ProductSheet in A1, then of course INDIRECT(A1&"!A:Z") equals ProductSheet!A:Z, so any change in ProductSheet will cause a reevaluation of the entire formula. Oct 21, 2019 at 7:39
  • 1
    Another approach would be to generate the formula in a script, then writing that formula to a sheet cell. First, create a dropdown or similar of the product names. When the user selects a product from the dropdown, intercept the edit with an onEdit script, let the script assemble a string like "=myFunction2(" + selectedProductName + ("!A:Z"))", then write the formula string to a cell using setFormula. If you belive this would work for you, I could try make an example. Oct 21, 2019 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.