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

  • 1
    Welcome. Would you please add a brief description of your search/research efforts as is suggested in How do I ask a good question?.
    – Tedinoz
    Sep 3, 2020 at 7:49
  • 1
    Would you please edit your question to provide more information about your scenario. If an item checkbox is checked, how many units are to be adjusted (one, all the Opened items for that item, or some other qty). Once the "Remaining" value is updated, do you expect that the checkbox should be 'unchecked'? Do you expect that the "Opened" quantity should be adjusted also? Item2 is an example where the items bought/remaining is actually less then the items opened - should this generate a negative quantity?
    – Tedinoz
    Sep 3, 2020 at 7:54
  • Hello @Tedinoz, thanks for the welcome and details provided. Please excuse me, I'm a newbie at using this site and appreciate your feedback. In relation to the quantity of Bought and Remaining < Opened:- Bought and Remaining*(of Bought) can be more or less than *Opened. (1 Bought and/or Remaining item once Opened has 10 items inside). The value shouldn't return negative. ------------------------------------------------------------The question and Sample sheet have been updated and look forward to your reply.
    – Stephanie
    Sep 4, 2020 at 12:16
  • 1
    Would you please edit your question to show how you have tried to solve this question. Your solution requires an onEdit(e) script and the spreadsheet that you originally referenced contained several projects containing similar scripts. However these projects are not included in the latest version of the spreadsheet that you have referenced. If you have made an attempt at solving the question, please show it; if you haven't attempted a solution, please explain what aspect of those scripts does not satisfy your problem or, possibly, what aspect of them you don't understand.
    – Tedinoz
    Sep 5, 2020 at 4:17
  • @Tedinoz, apologies for the onEdit(e) scripts previously inserted. I went through them and realised they were from other projects (will make sure to look through all the aspects of the sheet before linking). ------ The current script on the Sample Sheet only completes a previous stage of the question required. It's there as part of the process that leads up to the question - can be worked around or deleted if necessary.
    – Stephanie
    Sep 5, 2020 at 10:56

1 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){

  // 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
    // reset the sheet2 checkbox to unchcecked.
    // if is not satisfied
    //Logger.log("DEBIG: if is NOT satisfied");

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)


with this code:


// 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


  • this is exactly what I was looking for. ------ I realise what you meant by my question and will take on your advise with using cell formatting (font size, row height, row width, etc) instead of merging cells. This will ideally make A LOT of things easier
    – Stephanie
    Sep 7, 2020 at 10:47
  • The checkbox is only checked when "Quantity Opened" is reduced so will again use your method for the reduction of "Quantity Opened". --- I am currently trying to find a way to tick the checkbox when "Quantity Opened" returns 0 but will try to fiddle with a few ways and post again if not possible. ------ for the other logical issues - it just be a case of manually reentering if need be though wouldn't be an issue as "Quantity Opened" would be updated only when there is a value in "Quantity Remaining" --- will use formatting to flag.
    – Stephanie
    Sep 7, 2020 at 10:56
  • After a few variations, found an alternative though would need your assistance. Is there a way to do the exact same thing though with the singular value(s) from "Quantity Opened" being the chckoxCol (editedvalue == "0") and the remainingCol to be the same from Sheet 1. ---- I've figured out that the script variations do not respond on auto functions and/or formulas - ONLY through physical edits (seems quite obvious). I've previously searched for onChange though haven't been able to find a solution.
    – Stephanie
    Sep 7, 2020 at 13:33
  • A different Sample Sheet that hopefully answers the logic issues you raised earlier: docs.google.com/spreadsheets/d/… Please also note: [Auto Decrease Chcbx - Sht2 & 1] for the project in current discussion.
    – Stephanie
    Sep 7, 2020 at 13:35
  • _ I am currently trying to find a way to tick the checkbox when..._, Is there a way to do... I'll look at this - I am sure it is doable, but would you please explain exactly the problem that you're trying to solve, and the physical process and logic relating to this.
    – Tedinoz
    Sep 8, 2020 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.