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


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.