Google Sheets – Create a Multi-User Dashboard

this may be open-ended but let me put this forward.

I have a very complex dashboard which works out staff hours across the country. In this dashboard our team members have a drop down to choose their store which populates the same sheet with data relevant to their store, all to do with staff hours.

Now historically, the teams have used excel to do this but due to excels limitations and the amount of time it takes to update the data when changing the drop down (seconds in google sheets and about 5 minutes per drop down in excel!) I have decided to move to google sheets.

the issue lies when we have multiple users using the same sheet, if two of my area managers used the sheet at the same time then they would essentially be fighting for to see their data. With excel we would just email out the report and everyone would have their own copy. So what I'm after is a filter views sort of solution.

I've thought of the following:

Create n number of sheets in the same workbook using javascript to parse through an array of Area Names whilst duping and changing the reference cell to the cell reference in the array. I could then create a front page in google sheets which links to each page like a table of contents.

Issues with this is that the workbook would become massive and I may go past my 2mil cell limit.

I really do not know of any other way except for perhaps getting a BI tool like PowerBI and creating a dashboard there and putting the data into SQL server.(which I cannot do for the mo as I've just started and we don't have PBI or sharepoint yet!)

Does anyone have any pointers or guidance to the above?