How to filter data in a Google Sheet so that just the last 20 items per name are included?

Question

I have a google spreadsheet that have more then 1000 rows and it gets updated weekly, each row contain name and one of 3 decisions was made. I want to automate it and have in a separate sheet a table with names and count decisions in numbers and just last 20 for each name.

The desired result would look like this screenshot.

I'm a beginner in JavaScript and any idea or structure on how I can do this using App Script will be very welcome.

Answer 1

You can create descending ordinals by agent with this formula in cell Sheet1!AF1:

=arrayformula( 
  { 
    "Descending ordinal by agent"; 
    iferror( 1 / 
      countifs( 
        E2:E, E2:E, 
        row(E2:E), ">=" & row(E2:E) 
      ) 
    ^ -1 ) 
  } 
)

Then Insert > Sheet and insert this formula in cell A1 of the new sheet:

=query( 
  Sheet1!A1:AF, 
  "select E, count(E) 
   where AD is not null 
   and AF <= 20 
   group by E 
   pivot AD", 
  1 
)

The formula will give counts by name and decision, only looking at rows where the ordinal is 20 or less. See the new Solution sheet in your sample spreadsheet.