How to consolidate data in google sheet from multiple pages and dynamic tables?

Question

Google Sheets

Hello.

Tell me I can not even find where you can dig.

The essence of the problem.

There are 5-6 pages. The number of pages will be added, their name is unknown (may be different).
It is necessary to pull out some of the data from these pages (the size of the table may differ) into one common one (such as a database).

And if the data in the table changes, then this pivot table should also change.

I tried through "named ranges"
={INDIRECT(H1);INDIRECT(H2);INDIRECT(H3)}
where
H1 Range1
H2 Range2
H3 Range3
more difficult).

The most difficult thing is that I rested on how to pull data from all sheets of the document, provided that sheets can be added / renamed.

Sorry, lost a little, but advanced in my project).

Link to data https://docs.google.com/spreadsheets/d/103yFBslcj8...

Page by page:
Agromol(machine 221) is a data page (Data range 'Agromaul(machine 221)'!B16:H42)
Foxmill (saw 215) is a page with data (Data range 'Foxmill(saw 215)'!B16:H34)
Data will be copied from these sheets. Ranges are different. Those. the width is the same, the number of rows in the

database is different - this is an assembly of data from sheets (intermediate, technical sheet)
All data - the necessary data is already processed and displayed here.

How I implemented everything:
- manually assign a name to the required range (I wrote a couple of formulas so that the name is formed and it is easy to find)
- I write ranges on the database sheet (in orange)
- and add links to the ranges to the formula - in red
={INDIRECT(A2); INDIRECT(A3)}

Everything works, but there was an advice to rebuild the formula, how to do it? Or maybe there is a better option?
those. so that the name of the range gets into the list "Ranges for the database" and then added to the formula

In the scripts there is a function that "pulls out" the page name, but when the name is changed, the script does not work again.
There was an idea when pressing the button to enter the name of the sheet:
sheet2 = ui.prompt('New Sheet Name?');
sheet2 = sheet.copyTo(ss).setName(sheet2.getResponseText());
but this part adds to the very end of all sheets, which is inconvenient. Can I set the sheet to be created on the left or on the right?

Answer 1

I agree with @oshliaer , the easiest way in such initial ones is to make the formula a script. Here is the code to get the list of sheets:

function getSheetNames(){
  return [SpreadsheetApp.getActiveSpreadsheet().getSheets().map(sheet=>sheet.getName())];
};