Overview
The OP basically wants to be able to use the spreadsheet functions over the original data without having to scroll across a large number of rows and columns. His background make him think about having custom views and a easy way to change from one to another but Google Sheets doesn't have a built in feature to manage custom views.
Fortunately the fourth requirements are doable by using built-in features and Google Apps Script
Rows could be hidden by using filters but columns only could be hidden manually or by using code.
For 1, and 2 use Google Apps Script to create custom menus that apply the hide/show columns and optionally rows over the "master sheet". This will allow the user to edit the data directly over the "master sheet" and sort it.
Code example
The following code creates a menu for handling the hide/show operations of columns specified in a global variable.
var cols = [1,5,7]; //columns to be hidden/shown
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{name: "Hide", functionName: "hideCols"},
{name: "Show", functionName: "showCols"}
];
ss.addMenu("Custom Views", menuEntries);
}
function hideCols() {
var sheet = SpreadsheetApp.getActiveSheet();
cols.forEach( function(col) {
sheet.hideColumns(col);
}
)
}
function showCols() {
var sheet = SpreadsheetApp.getActiveSheet();
cols.forEach( function(col) {
sheet.showColumns(col);
}
)
}
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.
Best Answer
The best and safest way is to have your confidential data (formulae) on a separate spreadsheet (note: not separate sheet) and use
IMPORTRANGE()
to import it where it's needed to be injected. Hiding and/or protecting a sheet within the same spreadsheet is not a bullet-proof solution at all and it can be easily compromised.