In an arrangement where each of N user's changes propagate to all the other users' screens, that incurs N2 propagation costs (network messages, recalculations, and display updates). That grows rapidly as N grows. So if N > 3 you want to avoid it for sure.
A separate sheet per user might avoid most of the updates but the best bet is to use a separate spreadsheet document for each user.
Given that, using a separate Google Account per user might improve performance a bit by avoiding synchronized updates to some shared state (e.g. the user's document list). Also this is the normal case, unlike one account editing docs from many tablets at once, and any optimizations will favor the normal case. You'd have to measure it to be sure, but my semi-informed bet is that you won't notice the performance difference.
Giving each user a separate login account would help with tracking changes, but that might not be worth your setup work.
Idea: If all the columns are uniform within each sheet, that is, if the data is like a simple sequence of records, look into using Google Fusion Tables in place of spreadsheets. Fusion Tables scale up to very large data sets since the rows are independent of each other. You can "publish" columns from one table data to other tables.
Idea: If this is not a temporary application, consider replacing the spreadsheets with a custom implementation as a web app or native Android apps. Even then, it's good to prototype your application with spreadsheets as a way to discover what really matters to your use.
Instead of filter views, I would use the filter
command. For example, suppose Sheet1 holds raw data, with A being a character, B the list of "mentioned" chapters, and C the list of "appeared" chapters. You can then add another sheet for "filtered" data, where the user enters chapter number in, e.g., cell A1 and immediately get the list of rows matching that number thanks to the command
=filter(Sheet1!A2:C, regexmatch(Sheet1!B2:B,"\b"&A1&"\b") + regexmatch(Sheet1!C2:C,"\b"&A1&"\b"))
Here, each regexmatch
scans its column (B or C) paying attention to word boundaries (\b
) so that, e.g., "20,23,25" would not match "3". Addition plays the role of OR operator here.
If you are sharing this, you can allow editing but protect all cells except A1 on the second sheet: this way, the users can only change the chapter number for filtering.
There is a potential drawback: the changes to A1 are actual edits; i.e., what one user does affects everyone. If this is unacceptable, then filter views are the only option. You can circumvent the "OR" issue by creating a new column with
=B2&","C2
i.e., concatenation of columns B and C. Then apply filter views based on that column. However, the filter view will require either
- custom formula with regexmatch as above (not user-friendly at all)
- different format of data to make the condition "text contains..." work: for example, you could format as
,2,4,10,13,16,
so that the text to search for would be ,4,
(Otherwise, there's the same issue of "13" matching "3".)
Best Answer
Now Google Drive has a feature called “filter views”. It makes some filters available only to you while you don't change any data.
(Note: If you had existing shared filter on columns first remove that.)
Source.