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 - source

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)
=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 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?

Thanks!



Related Questions




How to format the time in this Google Docs script

Updated July 12, 2018 14:03 PM

How to Process Rows of Google Sheet Singly?

Updated April 05, 2018 19:03 PM

Jump to next empty cell in specific column

Updated October 24, 2018 01:03 AM