Google Apps Script for counting Matrix

Question

I have a spreadsheet with a matrix set up to count how many times a student has had a lesson with a particular tutor.

The matrix works fine with this formula:

=ARRAYFORMULA(SUM(IF(TERM4!$B$6:$B$2398=B$1,IF(TERM4!$C$6:$C$2398=$A2,1,IF(TERM4!$D$6:$D$2398=$A2,1,FALSE()))),FALSE()))

however due to the number of students/tutors the matrix is 7000 cells, slowing the working sheet down considerably.

Is there a better way to do this. Can I run a google app script to count the matrix on a trigger (eg. once a week) to count the matrix, so the formulas are not slowing the sheet down.

I would also like the formula to return a blank rather than a 0 if the result is FALSE.

Thanks for your help!

Answer 1

Yes its possible to do it with gas. The only part that gets a little complex is that if your script takes over 5min it wont process all the rows. To avoid that, process it by chunks (say 100 at a time) and use scriptProperties to remember which spreadsheet and row you last processed. Each trigger will process as much as it can until all spreadsheets and rows are processed.