One tab per site, 30 sites, about 1700 rows each and growing monthly. The summary tab currently does VLOOKUP into each tab by name, which means 30 formulas per metric and a rebuild every time a site opens. The alternative is stacking everything with IMPORTRANGE into one long table and running QUERY over it. Only two of us maintain this and neither of us writes code. Which way scales better in practice?
If all 30 sites have identical column layouts, stacking is easy. If three of them have an extra column someone added locally, fix that first or the stack silently misaligns and you will chase it for a day.
Two practical warnings on the IMPORTRANGE route. Each source needs authorising once, and a single broken one shows as one #REF that takes down the whole stack, so leave a note about which row range belongs to which site. And IMPORTRANGE refreshes on its own schedule, so numbers can be a few minutes stale — fine for a tracker, not fine if someone is reading it live on a call.
Good caveat. I put a last-recalculated cell at the top of anything built on IMPORTRANGE so people can judge for themselves.
At 50k rows across 30 sources you should also think about whether Sheets is still the right home for this in a year. It will work, it will just get slow enough that people stop opening it. Not an argument against doing it now, just be honest about the ceiling.
@csv_apologist · 11mo ago · 3 replies
Stack it, but stack it once into a real all-sites tab rather than doing it inside every summary formula.
The pattern that has held up for me: one hidden tab that is nothing but
={IMPORTRANGE(url1,"data!A2:F"); IMPORTRANGE(url2,"data!A2:F"); ...}, with a site name column carried through from each source, then every summary on the sheet is a QUERY against that one range. Adding site 31 is one line in one place instead of a new column in 40 formulas.The VLOOKUP-per-tab approach does not fail gradually. It fails the day somebody renames a tab.
Reply
Report
@listcomp_leo · 11mo ago
Add the site name as a column inside each source tab rather than trying to add it during the stack. Much easier to read later and it survives reordering.
Reply
Report
@shadowing_sena · 11mo ago
The rename failure mode is exactly what happened last month and why I am rethinking it.
Reply
Report