How to automate the compilation of all the data from different tab (same range) using QUERY in google sheet?

Question

Hi everyone,

I want to combine all the data from different tab (Exercise 1, Exercise 2, Exercise 3) under the Main tab. I achieved that by using the QUERY function as shown in the screenshot above. However, there will be Exercise 4, Exercise 5,... in the future where the tabs have not been created. Once the tab for Exercise 4, Exercise 5,... have been created, I need to modified the QUERY function in the main tab.

I'm looking for a way to automate the QUERY function so that I'm not required to modify the QUERY function every time when I have an extra new tab. May I know is there any way that I can achieve this automation on QUERY function? If there is other method without using QUERY function and able to achieve the same goal, please let me know as well. Any help will be greatly appreciated!

This is the link for my sheet: https://docs.google.com/spreadsheets/d/1FqmokrdbkvXQzx2WLSkfzR06aTA96NBfaP8pNTp1AqE/edit#gid=0

Answer 1

if you dont want to use scripts you can pre-program future sheets like:

=QUERY({
 IFERROR('Exercise 1'!A3:B, {"",""});
 IFERROR('Exercise 2'!A3:B, {"",""});
 IFERROR('Exercise 3'!A3:B, {"",""});
 IFERROR('Exercise 4'!A3:B, {"",""});
 IFERROR('Exercise 5'!A3:B, {"",""});
 IFERROR('Exercise 6'!A3:B, {"",""})}, 
 "where Col1 is not null")

this way as soon as they will be created the query will read them

Answer 2

Another Option is to use Apps Script to update your sheet Main using Time-driven triggers or Custom Menu

Sample Code:

function getsheetdata() {
  var range = 'A3:B';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var data=[];
  sheets.forEach(sheet => {

    
    //Logger.log(sheet.getName());
    if(sheet.getName()!="Main"){
      Logger.log(sheet.getName());
      var tmpData = sheet.getRange(range).getDisplayValues();
      var tmpData = tmpData.filter(rowData => {
        return (rowData[0]!="");
      });

      data.push(tmpData);
    }
  });

  data = data.flat();
  //Delete existing data in Main!A3:B
  ss.getSheetByName("Main").getRange("A3:B").clear();

  //Write to Main!A3:B
  ss.getSheetByName("Main").getRange(3,1,data.length,data[0].length).setValues(data);
  
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Refresh Menu')
      .addItem('REFRESH MAIN', 'getsheetdata')
      .addToUi();
}

What it does?

• Create a custom menu that will call the getsheetdata() when clicked
• In getsheetdata():

1. Define the range of the data to be collected from all the existing sheets (excluding the main sheet)
2. Get all sheets available in the spreadsheet using getSheets(). Loop each sheets (excluding the main sheet) and get the value of the range defined in step1.
3. Filter the 2-d array values returned by getDisplayValues(), by checking column 1 (array index 0) which should be not empty/null. Append the filtered data into an array data using array.push()
4. Change the data from 3-d array to 2-d array using array.flat()
5. Delete existing data in Main!A3:B. Then write the data collected.

(OPTIONAL)

You can set a Time-driven trigger to execute getsheetdata() automatically every minute.

Output:

(Update data using time-driven trigger every 1-minute)

(Update data using custom menu)

Answer 3

It's not the most elegant solution, but it will allow you to set up your query with future ranges:

=QUERY({
if(E3<>"",indirect(E3),{"",""});
if(E4<>"",indirect(E4),{"",""});
if(E5<>"",indirect(E5),{"",""});
if(E6<>"",indirect(E6),{"",""});
if(E7<>"",indirect(E7),{"",""})
}, "Select * where Col1 is not null")

The example values in E3:E7 could go on a config sheet, but for ease, I've put them on the Main sheet.