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");
}
First, the error: shs
is an array, since you populate it with ss.getSheets()
, so it doesn't have a method getActiveCell()
. However, the elements of the array has that method.
You are also referencing a variable sheet
, which you haven't defined anywhere.
Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs
array to have it operate on all sheets.
The following function will go through all cells in all sheets:
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
...
}
}
But you wanted to skip the first sheet, so let's start with element 1
in the array (since the array is 0
-based, element 1
is the second sheet):
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
var editedCell = shs.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99"; // What to sort.
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: false });
}
}
}
}
As for your comment, your code
function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var n in ss.getSheets()) {
var sheet = ss.getSheets()[n];
var name = sheet.getName();
if (name == 'Dashboard')
return;
var editedCell = ss.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99";
if (editedCell.getColumn() == columnToSortBy) {
var range = sheet.getRange(tableRange);
range.sort({column: columnToSortBy, ascending: false});
}
}
}
could work, if you replace return
with continue
. You don't want return
, it ends the script. continue
skips to the next element in the for
loop.
Best Answer
It does work like a charm!
@TheMaster point showed what was missing: Google Apps Script to open a URL
It didn't open the new Tabs simply because my Chrome pop-ups blocker was enabled!
Got it fixed by:
Chrome setting > Pop-ups and redirects > Allowed