Google-sheets – the fastest way to use Google Spreadsheets by several users – from the same account or from different accounts

google sheetsgoogle-drive

Terminology – I use spreadsheet for a document and sheet for a sheet within the document.

TL; DR

If several users must work on data, stemming from the same source, which is fastest:

  1. one Google Account for all users, one spreadsheet and different sheets for the users.
  2. one GA, different spreadsheets for the different users.
  3. different GAs for the differents users

Long version

Let's say I have a document with sheet A, containing a table with several columns – ID, Name, Shares etc.. I want to copy this data several times so that this data can be accessed by different people. On their view (be that in a sheet withing the same document or in another document) the data will be combined with other things. So if I have columns A, B, and C in the original source, they will be copied to columns A, B, and C in the users' view and the users will enter data / modify columns D, E, F, etc.. I have several options:

  1. Create Sheets B, C, D in the same spreadsheet. This way person 1, 2 and 3 will use the same Google Account, the same document and only use different sheets.

  2. Create new spreadsheets in the same Google Account and use Importrange to import the data. Each user works in a separate documents, but from Google's point of view it is the same user/account, logged from different devices and working on different documents.

  3. Create separate Google Accounts, within them create spreadsheets and then import using Importrange.

As this is an In-house thing authorisations/permissions are not an issue, so it's not a problem if the users use the same Google Account. My purpose is to use the most lightweight option because the users will be using relatively cheap Android tablets, which do not possess much processing power or fast wifi cards. I have two things to consider:

  • as I have tried to make things as automatic as possible I have a lot of index/match formulas, importrange formulas and validations plus a source table of more then 4500 rows. This may not sound a lot, but it definitely makes a difference on a tablet. So I guess putting too much logic in a single spreadsheet is not a good idea.
  • having the same spreadsheet opened by many people is also not very good as updating changes, made by one user on the screens of other users takes (relatively) long. At the beginning I went with option 1 (one account, one document, many users with many spreadsheets), but the Google Drive app would constantly crash on the tablets.

Currently I use option 2 – one Google Account, different spreadsheets for the different users, which is much better. The main logic happens in one spreadsheet and each user opens a separate spreadsheet, where the data is copied. However, I don't know if it's a problem that one Google Account is used from several devices at the same time. Does it make sense to create separate accounts for the different users from performance point of view? This will certainly introduce a lot of overhead for me (keeping records of usernames/passwords, logging users etc.), but if it will be faster I would go with it.

Best Answer

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.

Related Topic