How to use triggers with functions that require parameters from the spreadsheet?
AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll
, inside which you call all the wrapper functions (GetValue
) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob
).
Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue
and call RealFunctionThatDoesTheJob
directly from within the function that is being triggered (RefreshAll
).
Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);
)
Here is the example:
function RefreshAll() {
var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");
var Value1 = GetValue1();
currentSheet.getRange(Row,Column).setValue(Value1);
var Value2 = GetValue2();
currentSheet.getRange(Row,Column).setValue(Value2);
var Value3 = GetValue3();
currentSheet.getRange(Row,Column).setValue(Value3);
}
function GetValue1() {
return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}
function GetValue2() {
return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}
function GetValue3() {
return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}
If you are using the new (Q2 2014) spreadsheets you can try the Recalculation option found under [File] » [Spreadsheet Settings ...]
I believe it will work in your case. It depends what you mean by "on an alternate page". If you mean a 2nd sheet in the same spreadsheet you may be in luck. If you mean an alternate workbook, I think you lose.
In our case, we have several users with workbooks having ImportRange() calls to a master workbook, which is updated by Google Apps Script activity. The new Recalculation feature does NOT cause the remote users' workbooks to pull from the master the way a manual edit would.
Best Answer
Your script can be resolved with a couple of simple changes in syntax
getValue();
- you should assign this result to a variable so that you can use the variable to thesetValue()
on the other spreadsheetgetValue
VsgetValues
-getValue
returns a single value (the value of the top-left cell in the range); by comparisongetValues
returns the rectangular grid of values for the range. Even though you specified the range as "A2:G10" by usinggetValue
you only returned the value in cell A2. If you wanted the values in every cell of the range you should have usedgetValues
setValue
VssetValues
- the same rule works for setting values -setValue
sets a single value, andsetvalues
sets a rectangular grid of values (and must match dimensions ofgetValues
range).setValue(sourceValue)
- you need to specify the value(s) that are to be set. This is the reason why you assign thegetValue
method to a variable. That variable is used here to define the value that is to be set.