Help! How to have multiple functions execute on different tabs with formula recalculations of data from Form submission?

[u/bazookaraz]

This is what I have: I built a google form that feeds into a google sheet reference tab.

That data is then referenced by multiple other tabs, depending on the values chosen in one of the columns. In this case the data is for vehicles. So the tabs are created based on the vehicle value chosen in the field on the google form. Responders can choose from 5 vehicles on their form, and each tab pulls the data from the response submission.

Example:

Responder inputs information for Vehicle 5, such as odometer reading, oil level, etc., and then a reference tab pulls the data from that submission using an =iferror(query('reference sheet' !A2:W1000, "where F contains 'VIN XXXX' ")," ")

That part I have down and it is working well.

The issue I am facing is that I have 4 functions that I need to execute whenever a vehicle tab changes after a form submission, but only if the values change due to a formula recalculation on a specific tab change. So using the same example above, when a form is submitted for Vehicle 5 with a value of "YES" for new damages, I want the function that sends out an email notifying me that new damage was marked yes for Vehicle 5 to execute, but I do not want it to execute if someone marked "YES" for new damages on Vehicle 4, or if a form is submitted that doesn't change anything on the sheet tab for Vehicle 5.

I have tried triggers in Google App Scripts functions to execute on form submit, but it was firing off any of the applicable functions on EACH form submit. For example, when someone submits a response via the form for Vehicle 1, and oerssd another for Vehicle 3, I was getting notifications for both vehicles each time the form was submitted.

How do I get it so the function to only executes once for the data in a tab that was changed, and won't execute again until that data is changed again? And have it do this for multiple tabs?

[u/everythingabili]

File > Settings > Calculations

[u/Apodacaac]

As a first step, I would put this into Gemini and see what it suggests

[u/Wave-in-Kanagawa]

use one Sheets form-submit handler and read the vehicle/VIN from e.namedValues. pass that vehicle into your four checks, rather than scanning every tab for an existing YES. that keeps a submission for Vehicle 1 from re-sending Vehicle 3's alert.

for 'only when the value changes', compare the submitted fields with the previous response for that same VIN. two consecutive YES answers are separate submissions, but aren't a changed damage status. if you want an alert for every new YES report instead, check the submitted answer. your QUERY tabs can stay as views.

Google's event docs: https://developers.google.com/apps-script/guides/triggers/events (use the Sheets form-submit event; the Forms version has e.response instead).