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?