How to use a custom function in a Google Sheets QUERY?-CodePudding

In the cell, I'd have:

=QUERY(GETMONTHSDATA(), "select *")

For the function:

function GETMONTHSDATA() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return [].concat(
    spreadsheet.getRange('January 2022!A1'), 
    spreadsheet.getRange('February 2022!A1'), 
    spreadsheet.getRange('March 2022!A1')
  );
}

The cell would be blank as if it couldn't get any data. When I'd click in the cell, it would say "Unknown function: GETMONTHSDATA", but I'm sure that's just a visual bug since I could return a string in another cell and it'd work just fine. If I were to stringify the return, it outputs [{},{},{}].

Is there a proper way to run the QUERY using a custom function? It should be equal to =QUERY({'January 2022'!A1,'February 2022'!A1,'March 2022'!A1}, "select *")

CodePudding user response：

with spreadsheet.getRange('January 2022!A1') you will not get any value, but all the characteristics of the range

This works

=QUERY(GETMONTHSDATA(),"select *")

with

function GETMONTHSDATA() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return [].concat(
    spreadsheet.getRange('January 2022!A1').getValue(), 
    spreadsheet.getRange('February 2022!A1').getValue(), 
    spreadsheet.getRange('March 2022!A1').getValue()
  );
}