Indeed this is actually totally possible! I'm actually completely surprised by the lack of information about this!
Back in 2016, I developed a system for researchers to develop, test, deploy, collect information, analyze, and generate reports for the various studies there wanting to conduct.
Part of this system includes a rather sizable master table, something that contains a boatload of variables that are necessary for the entire process.
The entire system is actually an ecosphere of apps, consisting of about a dozen separate individually apps each specific for a purpose. One of the difficulties that we faced with this setup, was the size of that main master table in conjunction with the size of the other tables that are collecting data.
- Everything started growing way too fast
The solution was to create a short version of that Master table, using import range to only bring in a subset of the columns into a reference style table that I could then connect to all the other smaller apps in the ecosystem.
- That short version of the table is built using import range
Once we discovered that we could use import range in another table and then add that table into your app sheet app to get essentially a copy of it, we started looking for additional functionality and things that we could do.
Eventually we went so far as to add other columns after the import range section, giving the ability to have basically two copies of the table - each with their own specific individual columns (after the shared columns) specific for what's going on.
- The key to this setup was making sure that the rows inside the original sheet never change order.
- There's never a row that is deleted, there's never a row that is moved, we can add rows at the bottom, but that's it - and as long as the row-order maintains the same, everything will be one for one.
- The data inside the import range is read only, so you can't modify that in the short version of the table; but all of the columns after that are completely editable.
-----------------------------------------------------
I should note this is a very non-standard approach, but if you're already trying to use import range... I just wanted to tell you what was possible.
- I should also mention the difficulty that this brought in, caused by the fact that changes made to the original table won't propagate to the short version of the table until you sync the entire app - because the system needs to send the data to the Google sheet, The Google sheet can then propagate that to the import range, and then app sheet picks up the new data from the import range Google sheet and present it in the app.
--------------------------------------------------
okay now that you know what's possible, let me steer you in the direction that I would go.
- I would NOT try and combine all of these records together using import range
- Depending on the situation, one thing I might try and do is natively actually connect each of those tables to my app. This would give you the ability to natively modify the actual records, which is probably something that you might want to do down the line.
- But if I'm honest, I would try and see about actually moving all of this into a singular table. Now that you're building an app sheet app to manage all of this, you can create a relational database that will easily separate out one person's items from another... So having a separate sheet for each person is no longer necessary. And having all of the records inside a singular table just makes everything so much easier.
In my opinion, if you can get all of the data in a singular data table, and then reference connect that to other things, and present that inside your app... This will most likely be an interface that will set you up for success in the future.
Cheers! ๐ช
LinkedIn
Twitter
