How to pass an entire sheet as a parameter to a google sheets function with dependencies

by Jim B.   Last Updated October 19, 2019 00:03 AM

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(sheetName) {
  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)

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)

This works because gsheets 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?


Related Questions

Updated April 29, 2019 15:03 PM

Updated April 12, 2019 01:03 AM

Updated July 12, 2018 14:03 PM

Updated April 05, 2018 19:03 PM

Updated October 24, 2018 01:03 AM