google-sheetscircular-reference

How To Transfer A Value From One Cell To Another In Google Sheets

I have an interesting situation where every second column in my Google Sheet represents the amount of items at various stations. What I'm trying to accomplish is to automatically adjust the amounts of these values when a transfer value is entered into a cell between these columns.

Visual example

For example, here are the first few cells on the sheet:

Cell B5: Data entered here should be added to the value in C5, then clear

Cell C5: Displays AMOUNT C

Cell D5: Data entered here should be subtracted from value in C5, added to the value in E5, then clear

Cell E5: Displays AMOUNT E

See this attached example. Values are added to the yellow cells, and what they need to be doing to their adjacent cells is described above each one.

Some examples:

  1. Adding the number 5 to Cell B5 needs to change the value of C5 from 72 to 77, and then B5 needs to clear.

  2. Adding the number 12 to J6 needs to change the value of I6 from 36 to 24, the value of K6 from nothing to 12, and then J8 needs to clear.

  3. Adding the number 32 to R5 needs to change the value of Q5 from 41 to 9, the value of S5 from 1 to 3, and then R5 needs to clear.

  4. Adding the number 5 to Cell V6 needs to change the value of U6 from 5 to nothing, and then V6 needs to clear.

I would prefer using formulas, and since I can avoid circular reference errors by going to File/Spreadsheet Settings/Calculation and changing the Max number of iterations to 1 (Threshold at 0.05), I can use a formula like =C5+B5 in Cell C5. Is there a way to add to this formula so that B5 is cleared after its value is added to C5? And how would I then add the functionality of subtracting a value entered in D5 from C5, and then clear D5 as well?

Does anyone have any ideas? It would be much appreciated!

Solution

  • 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.