Auto Decrease Based on [tags:CheckBox] from Another Tab (or Alternatively Another Cell on Same Sheet)

Question

I have two tabs:
"Sheet1" - keeps track of bought and remaining items by quantity
"Sheet2" - keeps track of opened items

Is there a way to auto decrease the quantity from Remaining
when the checkbox from Opened is checked?
- the quantity of each individual item from Remaining responds to its own checkbox from Opened
(Item1: D11:D12 to decrease when G11:G12 is ticked
Item2: D13:D14 to decrease when G13:G14 is ticked etc)
- decreasing the Remaining Item Quantity individually by one.


Ideally the Opened Items Quantity (Sheet2) will automatically tick the checkbox when the dropdown is manually adjusted to zero.
recently updated via scripts

Once the Opened Items checkbox is ticked the same item on Sheet 1 will decrease by one.


If it is possible to have the checkbox from Opened Items to uncheck when values are updated then fantastic.

Please see the Sample Sheet which has the alternative and let me know if there is anything that needs elaborating.

The closest form of research I could find to reflect *Opened Items* checkbox was

Spin Button

Answer 1

You want to detect whether a checkbox in Column G on Sheet2 has been checked, identify which row was checked, use that row to correspond to an equivalent Item on Sheet1; get the Sheet1 value for 'Qty Remaining', deduct one unit, and then update the "Qty Remaining" on Shheet1. At the conclusion, you also want to "uncheck" the "checkbox".

There may be many ways to address this question. Please consider this as one solution.

---

function onEdit(e){

  //wa14600401
  // Logger.log(JSON.stringify(e));// debug
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetSourceName = "Sheet2";
  var sheetTargetName = "Sheet1";
  
  var Targetsheet = ss.getSheetByName(sheetTargetName);
  var Sourcesheet = ss.getSheetByName(sheetSourceName);
  
  // establish some varaibles
  var chkboxCol = 7;
  var chkboxMinRow = 11;
  var remainingCol = 4;
  
  // create variables from event objects
  var editedrange = e.range;
  var editedrow = editedrange.getRow();
  var editedcol = editedrange.getColumn();
  var editedsheet = editedrange.getSheet().getSheetName();
  var editedvalue = e.value;
  // Logger.log("DEBUG: Edited values: sheet = "+editedsheet+"\nrange = "+editedrange.getA1Notation()+"\nrow = "+editedrow+"\ncolumn = "+editedcol+"\nvalue = "+editedvalue);

  
  // test if the edit is on the right sheet, right column, right row, and has the right value
  if ((editedsheet == sheetSourceName) && (editedrow >= chkboxMinRow) && (editedcol == chkboxCol)  && (editedvalue == "TRUE") ){

  // IF is satisfied
    //Logger.log("DEBUG: IF IS satisfied");
    // get the range for remaining on sheet1
    var remainingrange = Targetsheet.getRange(editedrow,remainingCol)
    // Logger.log("DEBUG: remaining cell = "+remainingrange.getA1Notation());
    
    // get the 'remaining' balance
    var remaining = remainingrange.getValue();
    // Logger.log("DEBUG: starting remaining value = "+remaining);

    // deduct one from the 'remaining' balance
    remaining = remaining - 1;
    // Logger.log("DEBUG: ending remaining value = "+remaining);
    
    // update the 'remaining' balance for the adustment from opened
    remainingrange.setValue(remaining);
    // reset the sheet2 checkbox to unchcecked.
    editedrange.setValue("False");
    
    
  }
  else{
    // if is not satisfied
    //Logger.log("DEBIG: if is NOT satisfied");
    return;
  }
 return;
}

---

Other matters

1. Sheet1 and Sheet2 make use of merged cells. This appears to be solely for reasons of aesthetics. My own preference is to not to merge for aesthetic reasons, but to use formatting

2. In your question, you say: "Item1: Sheet1 D11:D12 to decrease when Sheet2 G11:G12 is ticked". This suggests a misunderstanding regarding merged cells. When Cell D11 and Cell D12 on Sheet1 are merged, the result is a single cell = D11; similarly, when cells G11 and G12 on Sheet2 are merged, the result is a single cell = G11. This is (partly) why I prefer to use cell formatting (font size, row height, row width, etc) for aesthetics rather than merging cells.

3. There are a couple of additional logic issues. These occur to me at the moment but there may be others:

• what if, inadvertently, the checkbox was ticked for a row that had no opened items.
• what if, inadvertently, the checkbox was ticked for an item that had zero 'remaining".
• what if, inadvertently, the user checked a checkbox but didn't reduce the "Quantity Opened"

FWIW, I suggest that each of these issues should be built into the script to ensure consistency.

---

Addendum 13/09/2020

You asked: "have the singular value(s) from "Quantity Opened" decrease the remainingCol from Sheet 1 when the value is "0" .

Replace this code (line #47-#50)

    editedrange.setValue("False");
    
    
  }

with this code:

editedrange.setValue("False");

// get Qty opened
var openedRange = Sourcesheet.getRange(editedrow,chkboxCol+1);
var openedValue = openedRange.getValue();

// check if Qty Opened = zero
if (openedValue == 0){
  // deduct one from the 'remaining' balance
  remaining = remaining - 1;
  // update the 'remaining' balance for the adustment from opened
  remainingrange.setValue(remaining); 
}

}