Multiple views of master spreadsheet that allow editing of master data

Question

I have a monster spreadsheet with lots of contacts in it. Each contact is a row, with different attributes as columns (first_name, last_name, number, etc.). There are a hundreds columns—i.e., too many too make the spreadsheet viewable.

I would like a way to have several separate abbreviated views of this master spreadsheet that:

1. show ALL of the rows;
2. show only SOME of the columns;
3. allow new data entered into the abbreviated view to populate the corresponding cells in the master sheet
4. allow sorting in the abbreviated view that does not destroy the data in the master sheet

I know that IMPORTRANGE() allows for you to reproduce data across sheets, but it doesn't seem to satisfy (3) and (4) above.

The new filter view seems almost perfect, but it doesn't allow you to create a filter view that only shows a few columns (e.g., A:A, B:B, E:E, X:X, Z:Z, AB:AB, etc.). You can hide them manually every time you want to look at a filter view, but this defeats the purpose.

Is this possible?

Answer 1

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);
    }
  )
}

Answer 2

3 is not possible (well, you can do 3 with google-apps-script, if you know how to program) , but 1,2 and 4 can be done.

use =filter() and =sort(), look at http://productforums.google.com/forum/#!topic/docs/WHN-xZBCwLQ

I keep thinking of creating a solution to No 3. As I have seen a few people who would like it. But not sure what demand exists, I.e. I keep thinking of adding it to www.cellmaster.com.au

Answer 3

(2) is doable if we use

query(importrange(<file>,<sheet>!<Range>),"select [Col<?>]")

[Col<?>] is a list of Column with specified number is listed separated by a comma

we don't need to use a script - but query() function for a range with mixed types of data (number, text, blank cells) often make it broken, so consider changing all of them as "text format" as much as possible.

for the (3), in abbreviated view sheet, just leave blank column(s) and let collaborators fill it. Then in the master sheet, you can "Vlookup()" or "importrange(match())" by use criteria is merged from other fields which you sure it's unique. eg:

Let say the addition column is "X", and criteria is matched from A,C,M:

=arrayformula(vlookup(A:A&C:C&M:M,
{importrange([separated_file],[sheet]![A:A])&
importrange([separated_file],[sheet]![C:C])&
importrange([separated_file],[sheet]![M:M]),
importrange([separated_file],[sheet]![X:X])},
2,0))

• the {} is an array formula, refer: https://exceljet.net/glossary/array-formula
• because we may have many seperated_files, thus we may need to merge many columns by columns top & down - in the vlookup() ranges separated by ;
• this master vlookup() run on a 2 columns range

I think with Sort(), the (4) also solved-able

anyway, the query() let you filter as well. see this link: https://support.google.com/docs/answer/3093343?hl=en