3

This question merges multiple sheets of the same dimension into a single sheet using an embedded array. I need the same solution, but with a dynamic collection of sheets stored in column G.

I've tried using JOIN and CONCATENATE to first create the array as a string and then evaluate it. But there's no equivalent EVAL function in Google Sheets so I can't process this text as a formula.

=CONCATENATE("={", JOIN(";", ARRAYFORMULA("filter('" & G:G & "'!A2:F, len('" & G:G & "'!A2:A))"), "}")

I've tried using ARRAYFORMULA and INDIRECT but INDIRECT doesn't work across array ranges so this only returns the first sheet in G1:

=ARRAYFORMULA(INDIRECT("'" & G:G & "'!A2:F"))
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
E.Beach
  • 1,829
  • 2
  • 22
  • 34
  • Does the answer to this question get you on the right track? https://stackoverflow.com/questions/34227186/query-several-ranges-and-add-automatically-a-column-to-know-the-source-of-each-r/34311717#34311717 – kirkg13 Apr 23 '20 at 00:54
  • There's a related question which mentions that, there currently is no formula that's similar to the `EVAL` function: https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell – Gangula Oct 21 '21 at 18:46

2 Answers2

0

I think you don't need "EVAL" equivalent, because there is another alternative: use a custom function instead. This function code may look like the following:

function MERGESHEETS(g) {
  if (!g || !g.length) return;  // invalid input data
  var ss = SpreadsheetApp.getActive();
  return g.reduce(function(acc, sheetName) {
    var sheet = ss.getSheetByName(sheetName[0]);
    if (sheet) {
      var values = sheet.getRange('A2:F').getValues().filter((v) => v[0]);
      acc = [...acc, ...values];
    }
    return acc;
  }, []);
}

Here you can include your dynamic data G:G as the only argument g value. More information about filter and reduce javascript functions you may find in many sources such as that.

Александр Ермолин
  • 1,145
  • 1
  • 10
  • 17
0

With the new REDUCE() function you can do this:

=REDUCE(, G:G, LAMBDA(acc, cur, VSTACK(acc, INDIRECT("'" & cur & "'!A2:F"))))
kishkin
  • 5,152
  • 1
  • 26
  • 40