How do I execute a function that I've built using TextJoin

Question

I am building a function - an ArrayFormula that filters data from multiple sheets. It is built using TextJoin (or Concatenate, it doesn't matter) - that way I can dynamically change the range as I add more sheets. In order to execute this function, I copy the formula, go to a cell, press "="+ Paste + Enter and it will be executed.

My question is how can I execute it automatically. I thought maybe I can use Indirect but can't figure out how to do it. For example, the formula I built is:

arrayformula(filter({Sheet2!A2:H;Sheet3!A2:H;Sheet4!A2:H},len({Sheet2!A2:A;Sheet3!A2:A;Sheet4!A2:A})))

In this spreadsheet there's an example: https://docs.google.com/spreadsheets/d/12PO66ymfPpnO5tNfXr0MHjTY8rIzHfwVY5gtV6vccD0/edit#gid=0

cell A3 and A4 are TextJoin functions to create the range dynamically
cell A1 concatenates A3+A4 to create the function
cell A9 is where I executed it by pressing "="+ Paste + Enter

Tom, what is the maximum number of separate sheets you might realistically be wanting to include in this formula over time? — Erik Tyler
First, whenever someone tells me they have hundreds of sheets (or even dozens) going into one dashboard, my first thought is "This sounds like a possible data design flaw." Of course, I don't know your specific system. But in my decades of experience developing super-complex systems, I've never seen a system yet that needed to have that many separate sheets feeding into one dashboard sheet. That said, while a formula CAN be written to do this automatically, it would be cumbersome, making script your likely best option. — Erik Tyler
For the sake of others, I also recommend that you add your real-world parameters to the post itself. For instance, add to your main post "I have 100 sheets and counting." Also... How many columns per sheet will you really be trying to combine? How many max rows per sheet are possible? These things matter to a solution, and they would be drastically different for 10 sheets x 100 rows x 2 columns than they would be for 100 sheets x 1000 rows x 12 columns. — Erik Tyler
See your sample spreadsheet (two highlighted green tabs, newly added: "Erik Help" and "Range_Sheets") for an example of how you'd set up such an automated formula (though I can't stress enough that I would not do it this way, as it is neither efficient nor necessary). — Erik Tyler

iansedano iansedano · Accepted Answer · 2021-03-11T09:50:32

As far as I know, this is not possible

But there is a workaround (or better way to do it, depends how you look at it).

There is no INDIRECT specifically for formulas, however:

Workaround

A function exists in Apps Script:

formulaCell.setFormula(formulaString)

You don't need much Apps Script to get this working in the way you envision it, the Tutorials can be helpful to get started.

I would also recommend this approach as formulae will quickly become limiting once you cover more complex ground, and will also be much, much easier to maintain and modify. You also get the added benefit of longer execution times.

Sample script

function createFormula() {
  // initializing variables for the Spreadsheet file and the sheet
  let file = SpreadsheetApp.getActive();
  let sheet = file.getSheetByName("Sheet1");

  // This is the range that represents the sheets to filter
  let sheetsToProcessRange = sheet.getRange("C2:C");
  let sheetsToProcess = sheetsToProcessRange.getValues();

  // filter out the empty ones
  sheetsToProcess = sheetsToProcess.filter(row => row[0])
  // At the moment its a 2D array
  // [["Sheet1"],["Sheet2"],["Sheet3"]]
  // This will change it to a 1D array (just to make code easier to read)
  // ["Sheet1","Sheet2","Sheet3"]
  sheetsToProcess = sheetsToProcess.map(row => row[0])

  // initializing the array notation strings
  let filterArrayString = "{";
  let lenArrayString = "{"

  // for each sheet in the array, add SheetX!Range to the strings
  sheetsToProcess.forEach(sheetName => {
    filterArrayString += `${sheetName}!A2:H;`
    lenArrayString += `${sheetName}!A2:A;`
  })

  // Remove trailing semicolon
  filterArrayString = filterArrayString.slice(0, filterArrayString.length - 1)
  lenArrayString = lenArrayString.slice(0, lenArrayString.length - 1)

  // Add in final bracket
  filterArrayString += "}"
  lenArrayString += "}"

  // construct whole formula string
  let formulaString = `=arrayformula(filter(${filterArrayString},len(${lenArrayString})))`

  // Choose the cell for it and set it as a formula
  let formulaCell = sheet.getRange("A9")
  formulaCell.setFormula(formulaString)
}

This, run from the script editor will insert your desired formula in cell A9 in Sheet1.

I have used some more advanced features of JavaScript to do this succinctly but you don't have to do it this way.

You already know how to construct most of the formula with formulae. So you could have a script that just grabs this value and so would shorten the script considerably.

function createFormulaSimplified(){
  // Initializing variables
  let file = SpreadsheetApp.getActive();
  let sheet = file.getSheetByName("Sheet1");
  
  // Range where your already constructed formula lives
  let formulaCell = sheet.getRange("A1")
  let formulaString = formulaCell.getValue()

  // Constructing and setting the formula
  let formulaString = "=" + formulaString
  let formulaCell = sheet.getRange("A9")
  formulaCell.setFormula(formulaString)
}

In effect, this does the same thing as the formula above, but less of the work is done by the script. Basically the only thing it does is grab the formula you have already constructed, adds an = to the start and assigns it to cell A9.

Of course, you could also do all the work that the formula does in Apps Script, but that is for another answer of another question.

How do I execute a function that I've built using TextJoin

1 Answers

As far as I know, this is not possible

Workaround

Sample script

References