Merge multiple sheets (dynamically) in a Google Sheets spreadsheet

0
votes

I have a Google Spreadsheet with 3 sheets containing data from different countries (Country1, Country2, Country3) and a 4th sheet (Summary) that is supposed to append these together. The columns are the same in all sheets.

I know already that I can combine them like this:

=QUERY({Country1!A2:G;Country2!A2:G;Country3!A2:G}; "SELECT * WHERE Col1 IS NOT NULL")

However, I need a way to combine an arbitrary amount of sheets together, without modifying the formula. So, if Country4 gets added, this should be appended as well. How can I do that?

If it helps, I have a Settings sheet with a column that contains all the names of the countries (and therefore country sheet names). I have a script that dynamically generates new sheets if a country is added in the Settings sheet. If necessary, this script can also update the formula in the Summary sheet.

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
You wrote that you have a script that generates new sheets. As part of that script, why not then update your formula? – Diego
@Diego thanks. Yes, that's what I'm doing currently. SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary").getRange('A1').setValue(summaryQuery); But I was wondering if there is an easier/more effective method, without using scripts. – otherguy
@otherguy I think this is not possible without using scripts if you do not know the number of sheets, what is the script you are using so far? – Rafa Guillermo
@RafaGuillermo I do know the number and names of the sheets. Like I said, there is a Settings sheet with Country1, Country2, Country3 in column A. – otherguy
When I say if you do not know the number of sheets I really mean for an arbitrary amount. Sorry for my poor explanation. I believe this formula will need to be generated with a script for an arbitrary number of Sheets. I can provide this script for you, if you like? – Rafa Guillermo

1 Answers

2
votes

Answer:

You can dynamically create the formula for this using an attached Script.

Code:

function generateFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var formula = "=QUERY({";
  for (var i = 0; i < sheets.length; i++) {
    var name = sheets[i].getName();
    if (name != "Settings" && name != "Summary") {
      formula = formula + name + "!A2:G;";
    }
  }
  formula = formula.slice(0, -1) + '}, "SELECT * WHERE Col1 IS NOT NULL", 0)'
  ss.getSheetByName("Summary").getRange("A1").setFormula(formula);
}

This is just one possible way of doing this, though I do not believe it is possible to make it arbitrary without a script. Either way, I hope this is helpful to you!

References: