I think this onEdit() trigger is what you need. Also see the documentations on Apps Script and Spreadsheet App.
For example, in your sheet, an implementation for requirement 1 can be as follows.
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var tab = sheet.getSheetByName('Item Transferring');
sheet.setActiveSheet(tab); \\<---
var cell = e.range;
user_input = cell.getValue();
if (cell.getRow() == 5 && cell.getColumn() == 2){ \\ it's probably best that you read the row and column indices of B5 and C5 from a helper tab/sheet in case you decide to move B5 and C5 later.
var cell2 = tab.getRange(5,3);
cell2.setValue(cell2.getValue()+user_input);
cell.clear({contentsOnly: true});
SpreadsheetApp.setActiveRange(cell); \\<--- use this in conjunction with setActiveSheet. Either use both or use neither.
}
}
Set up an onEdit trigger for the function onEdit, and the desire effect will occur.
Note that in your scenarios, the edits are always on single cells. Sometimes, edits can occur to multiple cells simultaneously. Make sure there is no ambiguity in your interface. Also the protect function of Google Sheet may come in handy.
Comment:
Do try to create a different interface where you are not as reliant on change per edit. You can easily run into problems with synchronization. A final sheet that does not take a lot of inputs frequently may be fine. It nevertheless slows down your regular activities.
As well, you should probably have a cell that displays the most recent edit.
EDIT: regarding generalizing the code and applying the above example, you can do something like this:
function process_input(row_input,col_input,input,output,func){
// row_input and col_input are the cell indices you are watching
// input is the range object that the edit trigger passes in
// output is the range object that contains the cell you want your edit to happen to
// func contains the formula you want in the output cell
if (input.getRow()==row_input,input.getColumn()==col_input){
output.setValue(func(input,output));
input.clear({contentsOnly: true});
}
}
As an example that can apply to the rest of your problems, your scenario 1 from earlier would require a function as below. Be careful that a user input can be accidentally non-numeric.
function update_add(input,output){
if (!isNaN(input.getValue()) && isFinite(input.getValue()){
return input.getValue()+output.getValue()
}else{
return output.getValue()
}
}
You would write a simple function like the above for every kind of update you want to have. To put it all together, using your scenario 1 as an example, which is to monitor B5 and update B6 using add recipe, you would do
function onEdit(e){
...
output = tab.getRange(5,3);
process_input(5,2,e.range,output,update_add)
}
As mentioned earlier, it's better for you to pull the indices of the cells you are watching from a separate sheet -- as oppose to hardcoding the numbers 5, 3, 2. To implement a full solution for all of your scenarios, you can simply loop through all the cells you need to watch by update method and then loop through all possible update methods.
You do need to implement the exact code yourself. On Stackoverflow, we only discuss methods and how they work. We discuss using minimally self-contained examples. We can't hand codes to complete people's projects. That won't be a sustainable kind of interaction.
The intended take-away for you from this answer are the various Apps Script utilities linked and described with sample codes and the advices on generalization. That's it. If there are questions about a utility, please read the linked documents; and if you still have questions after, open another post with a specific focus.
