Google Spreadsheet Query using Dynamically defined Sheets

Question

i was strugling with something unusual or not...

I am doing an google spreadsheet, that will have a lot of sheets, and their names are dates as MM/YYYY, and i need to get all the data from a range A1:B100 for example, and do QUERY stuff on an master sheet with that data, the problem is, i have done a function with javascript to get the Range of that sheets, but i can´t use them on =QUERY() function, tryed a lot, with different aproaches from internet, but nothing successfull yet..

My sample spreadsheet:

https://docs.google.com/spreadsheets/d/1W5W7y16yvOUoqNZCZ59ol8D2nbelJHNqtgaxrtb1jC8/edit?usp=sharing

Also the app script i have done to manage the sheets data dynamically:

let sheetsData = () => SpreadsheetApp.getActiveSpreadsheet().getSheets().filter(sheet => sheet.getName().match(/^[\d]/)).map(sheet => sheet.getRange("A1:B20"));
Logger.log( sheetsData() );

My sample usage on the spreadsheet is =QUERY(sheetsData();"SELECT *"), i can´t even list the data... lul

Any help will be most welcome, tnx.

chuckx chuckx · Accepted Answer · 2021-02-04T07:05:38

Instead of returning the Range objects, you need to return the values contained within the ranges. You also have to make sure that the data you return is structured as a two-dimensional array.

function GET_DATA() {
  const dataSheets = SpreadsheetApp.getActiveSpreadsheet()
      .getSheets()
      .filter(s => s.getName().match(/^[\d]/));

  const ranges = dataSheets
      .map(s => s.getRange("A1:B20"));

  return ranges
      .reduce((result, range) => result.concat(range.getValues()), []);
}

The above implementation works with a formula such as =QUERY(GET_DATA(), "SELECT *").

However, there's a fundamental problem with this approach. Since the range location is hardcoded on the Apps Script side and not specified as part of the formula, recalculations are not triggered as data in the sheets change.

Google Spreadsheet Query using Dynamically defined Sheets

1 Answers