I'm working on a scoreboard automation process using Google Sheets, but I've come up with a problem.
I want an automatic e-mail to be sent to a list of emails when a cell value in a range is met.
For example, when users take a test in their Google Sheets file, I will set up a IMPORTRANGE FUNCTION to import the results to my ADMIN files. and if his/her scores is below 50%, the ADMIN file will instantly send an email to the teacher and the students. The contents of the email should be able to edit easily based on different scores. For example, if the score is below 30% > send an warning email, if the score is above 60%, send a congratulations email
The file I'm working on https://docs.google.com/spreadsheets/d/1s0IApxtJuUNbHhKRxEpFnJg_rd2_JAk-GjnSUP9VdJs/edit#gid=0
Other needs:
The script should be able to send notification instantly right after the user complete the test The script should work when other test takers use their files, but the email will be sent from the ADMIN file as I don't want my users have to authorize the code, I just want to authorize the script in the admin file only.
I've posted a similar question here Send automated email based on cell value changes in a range
however it only works when users edit on that file and if I import ranges from other files it will not send the email even if the cell value is changed in the same way.
Thanks.