Google Apps Script Triggers For Spreadsheets

Question

I've created a spreadsheet for one of my classes to keep track of marks. There is a leaderboard in the class and if a student is on top of the leaderboard for 7 days or more they receive a bonus for being so spectacular.

I have 4 columns for this. The first is the MARK column. The second is their SCORE. The third is the DATE they received the hi-score. The fourth is the AMOUNT_OF_DAYS that have elapsed since they obtained a hi-score. I have a script that runs if I enter in their results. It checks to see if it is a hi-score and date stamps todays date in the DATE column. The AMOUNT_OF_DAYS column is a simple formula that calculates the amount of days that have elapsed from when the hi-score was obtained. I would like to have the MARK column populated automatically once the AMOUNT_OF_DAYS value is 7 or greater. I tried the "change" trigger event, but I don't think that is the purpose of it. I don't think onEdit would work because that's only triggered when I manipulate the spreadsheet manually I think. Any ideas? Thanks in advance.

Answer 1

Does your spreadsheet look like this?

You can put a conditional formula in a cell:

=if(D2>=7,"bonus Time!",0)

The above formula enters the text bonus Time! if the cell in D2 is greater than or equal to 7, otherwise it enters the value zero.

You already have a script that runs when you enter the data, and then checks if it's a high score. I don't understand why you can't extend that script to also check the AMOUNT_OF_DAYS?

Answer 2

Here's what I did to solve this:

I created a function in a script that would be responsible for checking to see if any values in the THE_AMOUNT_OF_DAYS column are greater or equal to 7. Under the "Resources" menu in the script editor go to "all your triggers" and then add a trigger. Specify which function you'd like to run. Make sure that it is a "time-driven" event instead of a "From spreadsheet" event. Set the time to the interval you'd like the script's function to fire at.

One thing to note is that Browser.msgbox() functions don't seem to work when the function fires via the trigger, but it will work when you run the function from the script editor manually. The only reason I mention this is because I'm sure that many of us use this to debug our scripts.

Special thanks to @teatimer for pointing me in the right direction :)