Can I copy row from one Google Spreadsheet to another based on certain criteria

Question

I have a spreadsheet to track student loan outs:

Column A is the student name
Column B is how many loan outs they have received
Column C is the class period Column D is the teacher name
Column E is the type of loan out received
Column F is the date received Column G is loan out returned.

Columns E,F,G repeat in each row 5 more times.
Column B has a Counta formula count of each time a loan out is given to that student.

I would like each teacher to have a sheet within that book that lists the students that have 2 or more loan outs instead of having to look at the master list.

So for example, as a teacher I would have a seperate sheet to look at and when a student of mine gets two or more loan outs it would copy that row into my seperate spreadsheet.

So I am looking for a formula or script that would first look in column B for a number greater than 1 and then by teacher name in column D and pull the data to the seperate sheet. I would do this for each teacher.

I already have a script running that automatically adds the date when a loan out is entered. I don't know if this possible too. Here is a link to a blank sheet than I am using: Example Sheet

Answer 1

I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.

I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.

Answer 2

You could try using sheetSpider from the script gallery. (Tools > Script gallery)

It's built for situations like the one you have presented.