CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
otherguy
otherguy

Reputation: 985

Merge multiple sheets (dynamically) in a Google Sheets spreadsheet

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.

Upvotes: 0

Views: 2508

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

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.

References:

Upvotes: 4

Related Questions