Google-sheets – Split Master Sheet into Multiple sheets

google sheets

I have a master sheet where the data is like this:

Would it be possible to split this into multiple spreadsheets so it can look like:

and the sheets are divided into Weeks?

Scripts or formulae are both okay, as long as it's doable and sheets automatically adjust with the master is edited.

Best Answer

Nobody answered, so here's a simple solution using arrays, importrange, query in Gsheet.

With =IMPORTRANGE you can import data from your master sheet : don't forget to link the sheets between themselves. On my example, i importedrange from the same sheet but it'll works with any sheet you own.

There are multiples ways to do this (here's an example) :

First method : ImportRange + Array, this way you can import all the data, then use some filter. I used an array to select and combine the first 2 columns, then the week you need, then the emails.

={IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!A1:B7"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!C1:C7"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!G1:H7")}

Second method : ImportRange + Query all the data is imported then the query filter helps to select the columns you need (SELECT col with name, grade and week) and choose which students to pick based on the criteria ( Col1 contains 'student name').

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1ACw2zWhzuvWlxNmuSX3jc9GQR4YZlbr9ZrLwh2CcXvo/edit#gid=0","Sheet1!A1:H7"),"SELECT Col1,Col2,Col3,Col7,Col8 where Col1 contains 'G3' OR Col1 CONTAINS 'g5'",1)

Third Method : Import range + Array + Query // pretty much the same except you choose which column you need with the import so you don't have to change the query, only the range of the second importrange to select the week of your choice (Col C here)

Edit : highlighted cells containing formulas

Related Solutions

Google-sheets – Creating duplicate Google spreadsheets that filter data from a master sheet and update rows on master sheet

How to automatically pull data between different Google Spreadsheets:

There is a function called ImportRange that allows you to pull data out of one sheet and into another. This can be really useful, if for example you have a spreadsheet that you are using to collaborate with others, and then somewhere along the line you want another person to be able to see some of the data in the sheet but not all of it. e.g. if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.

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