Dynamically create an embedded array in Google Sheets

Question

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"))

Does the answer to this question get you on the right track? stackoverflow.com/questions/34227186/… — kirkg13
– kirkg13, Commented Apr 23, 2020 at 0:54
There's a related question which mentions that, there currently is no formula that's similar to the EVAL function: stackoverflow.com/questions/16303680/… — Gangula
– Gangula, Commented Oct 21, 2021 at 18:46

Александр Ермолин · Accepted Answer · 2023-06-27 13:06:16Z

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.

answered Jun 27, 2023 at 13:06

Александр Ермолин

1,1751 gold badge11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kishkin · Accepted Answer · 2023-06-27 23:53:34Z

0

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

=REDUCE(, G:G, LAMBDA(acc, cur, VSTACK(acc, INDIRECT("'" & cur & "'!A2:F"))))

answered Jun 27, 2023 at 23:53

kishkin

5,3551 gold badge28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Dynamically create an embedded array in Google Sheets

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related