I usually use IF B3:B is empty (ISBLANK) leave COLUMN… empty
It must work
I see what you are saying. I could duplicate it. One thing you could do is get crafty to trick it a little bit. First create a dummy order with an email that has all 'z’s. Then sort your unique formula in your orders summary sheet so the dummy record is always at the bottom and can’t have the checkbox validation set on it. Then when the 500 new rows kick in, it shouldn’t add the checkbox validation because I think it’s based on the last row in the sheet before the 500 rows are added. It’s not ideal, but a thought.
I know you are concerned about speed, but I really don’t think that having the extra rows should affect performance as glide ignores them, so I would also consider just adding 10,000 rows, so you have enough of a buffer and don’t have to worry about the 500 rows issue.