Concatenate or join of dynamic array when importing multiple ranges

Question

I have a set of hyperlinks to other sheets that will change as new links are added or subtracted. I am looking to write a function that will import the same range from all of these sheets. The sheets are all based on the same template.

I am able to create the dynamic range using the following:

C1 ="B1:B"&counta(B1:B)

I then join everything with this formula here:

="{importrange("&join(", """&"Sheet1!A:A"&"""); importrange(",indirect(C1))&", """&"Sheet1!A:A"&""")}"

The output shows what I need the formula to be, however it will not calculate and appears as a string. I tried using indirect on the formula created, however this does not work either as it states indirect requires a valid range.

{importrange(https://docs.google.com/spreadsheets/d/19S08r/, "Sheet1!A:A"); importrange(https://docs.google.com/spreadsheets/d/19S08r/, "Sheet1!A:A"); importrange(https://docs.google.com/spreadsheets/d/1uxdty/, "Sheet1!A:A"); importrange(https://docs.google.com/spreadsheets/d/19S08r/, "Sheet1!A:A")}

Answer 1

Google Sheets built-in functions can't convert a TEXT value into formula. This could be done only by using Google Apps Script or the Google Sheets API.

The method to be used is setFormula / setFormulaR1C1 to return a single formula, and setFormulas / setFormulaR1C1 to return an array of formulas.

NOTE: IMPORTRANGE requires that the first parameter be a TEXT value, so if you will include the URL directly instead of using a cell reference, the URL should be quote enclosed.