Google Sheets – How to Use the SPLIT Function with Different Delimiters

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");
}

The error message says that the same column cannot be selected both with and without aggregation. One solution is to move the count function to another column:

=query(A:B, "select toDate(A), count(B), sum(B) group by toDate(A)")

Another solution, which is also applicable when there is no column B, is to duplicate column A before querying: {A:A, A:B} is the array with three columns in which the column A appears twice. One can refer to these columns as Col1, Col2, Col3 in the query string. The following formula returns the desired table:

=query({A:A, A:B}, "select toDate(Col1), count(Col2), sum(Col3) where Col1 is not null group by toDate(Col1) label toDate(Col1) 'date'")

For readability, the same query string with linebreaks:

select toDate(Col1), count(Col2), sum(Col3) 
where Col1 is not null 
group by toDate(Col1) 
label toDate(Col1) 'date'