Hi,
I am a Management consultant and helped many business forms automate their business processes using google sheets, forms, scripts & excel sheets. There are few projects where we developed a mini ERP for the clients.
I understood your requirement. Thanks for detailed explanation. I will be able to solve your problem in a more easier manner without using script.
Script has following limitations.
1. Google has a quota limits for running scripts. It's limited to 90 mins per day.
2. Script has two types, On edit scripts and trigger based scripts. In your case, we have to use on-edit scripts which has few quotas allocated and It slows down the sheet when number of entries increase. Even though we optimism script to maximum extent possible, there are still limits to it.
3. Google update Script language once in a while. Consequently, Some of the scripts might stop working and it demands new work. (It's rare case scenario though)
Because of the above problems, We try to avoid scripts to the extent possible and use them only such cases where it can not be done without script. In your case, we can try to do it without using scripts. It takes little bit extra effort but it's recommended for the long-term sustainability.
For any reason that's not explained in the job, If I am not able to complete the assignment without using script. I will do the work for 30 USD using script.
Ping me to discuss further.
Have a nice day.