Google-sheets – How to save a view in Google Sheets

google sheets

I have a table in Google Sheets that lists products in rows and dependencies in columns. Most product depend on only a few things so there are a lot of columns showing extraneous information which makes it look at. I can hide all the columns not relevant to a given product, as well as all the rows not associated with the product (there are multiple rows for a product representing different pipeline stages).

However, if I want to switch products, I have to manually unhide everything and then manually rehide stuff for the new product. I thought I could use a filter view to save the current view, but when I switch to a different filter it doesn't remember what was hidden for that view.

How can I have different preset settings which each remember which rows and columns were hidden for that setting?

Best Answer

You could copy and transpose all the data, so that each product has a column, and all the dependencies are in rows below it.

This isn't generally good practice to organize data like this, but it would allow you to easily filter the dependencies, and then you could use filter views.

If you wanted to go a step further, you could save a filter view for each product.

Note: It is possible to set up a dynamic transposition from one range to another, in which case you could have two matching tabs with all the same data; one where you edit it and view it as desired with the filter views I described above, and another that's referencing that range and transposing it dynamically to the "best practice" way to organize data (Records in rows, and Dependencies / fields in columns).

Related Solutions

How to Calculate Weighted Averages in Google Sheets

There is no built in function to calculate the weighted average so you have to write a custom function if you want to avoid using too many temporary cells. So here is how you can achieve your goal.

Go to Tools > Scripts > Script editor..., Copy/Paste the bellow code and save it:

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v[0].length != w[0].length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v[0].length; ++c) {
    sum_v += v[0][c] * w[0][c]; 
    sum_w += w[0][c]; 
  }

  return sum_v/sum_w;
}

Use it like:

=weightedAverage(B3:D3,$B$2:$D$2)

Where B3:D3 are your values and $B$2:$D$2 your weights. It's not error proof (the only check is to make sure both arrays have the same length) but it will do the trick.

In the above example I'm not trying to extract the weights from the title of the attribute, but I'm reading them from the second row (B2:D2) to make our life easier and clearer. The $ is not changing the result of the formula. It's only affecting what's happening when you're copying the formula in another cell. The part of the cell reference following the $ will not change (link for more details). Write the formula once in the cell E3 and Copy it to the rest of the rows to see it in action.

Google-sheets – Multiple views of master spreadsheet that allow editing of master data

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