Is it possible to use simple triggers to increment a cell whenever another cell is edited in google sheets?

Question

Here is the desired behavior:

Cell A1 contains a number (0 by default) representing damage taken.
Cells B1:D1 contain checkboxes (FALSE by default).
When cell B1 is edited (i.e. checked "TRUE"), cell A1 is decreased by 1. Cell B1 is automatically reset to FALSE. If cell A1 is decreased below 0, it is reset to 0).
When cell C1 is edited, cell A1 is incremented by 1 and automatically reset to FALSE.
When cell D1 is edited, cell A1 is reset to 0.

I'm very new to Google Apps Script and recently discovered Simple Triggers. It seems like onEdit is meant to trigger when anything in the entire workbook is edited, but I'm not sure. Is it possible for a script to run automatically if a specific range is edited (i.e. B1:D1 as described above)?

For context, I'm designing a character sheet for a play-by-post RPG. When a character takes damage, their capability in one or more skills is decreased by taking damage ranks. To make it as simple as possible to track these throughout the sheet, I'm trying to find a way to easily increment these ranks up and down without having to write a separate macro for each row.

If there's a better solution than the behavior I've proposed (e.g. using something other than checkboxes and onEdit triggers), please let me know! Also, it would be ideal if the solution also worked on mobile.

Cooper Cooper · Accepted Answer · 2019-11-01T16:11:17

/*
Cell A1 contains a number (0 by default) representing damage taken.
Cells B1:D1 contain checkboxes (FALSE by default).
When cell B1 is edited (i.e. checked "TRUE"), cell A1 is decreased by 1. Cell B1 is automatically reset to FALSE. If cell A1 is decreased below 0, it is reset to 0).
When cell C1 is edited, cell A1 is incremented by 1 and automatically reset to FALSE.
When cell D1 is edited, cell A1 is reset to 0.
*/

function onEdit(e) {
  var sh=e.range.getSheet();
  e.source.toast('Entry');
  if(sh.getName()!="Sheet1")return;//might need to change Sheet1 to whatever you're using
  if(e.range.columnStart==2 && e.range.rowStart==1 && e.value=="TRUE") {
    e.source.toast("B1");
    var vA1=e.range.offset(0,-1).getValue();
    e.range.offset(0,-1).setValue((vA1-1<0)?0:vA1-1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart==3 && e.range.rowStart==1 && e.value=="TRUE") {
    e.source.toast("C1");
    var vA1=e.range.offset(0,-2).getValue();
    e.range.offset(0,-2).setValue(vA1+1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart===4 && e.range.rowStart==1 && e.value=="TRUE") {
    e.source.toast("D1");
    e.range.offset(0,-3).setValue(0);
    e.range.setValue("FALSE");
  }
}

Just a reminder: You can't run this function from the script editor just install it in a .gs file and play with your check boxes on Sheet1. If you not using Sheet1 the change the name of the sheet in the script to whatever you sheet name is.

It looks like this will work for "A1:D".

function onEdit(e) {
  var sh=e.range.getSheet();
  e.source.toast('Entry');
  if(sh.getName()!="Sheet1")return;
  if(e.range.columnStart==2 && e.value=="TRUE") {
    e.source.toast("B1");
    var vA1=e.range.offset(0,-1).getValue();
    e.range.offset(0,-1).setValue((vA1-1<0)?0:vA1-1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart==3 && e.value=="TRUE") {
    e.source.toast("C1");
    var vA1=e.range.offset(0,-2).getValue();
    e.range.offset(0,-2).setValue(vA1+1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart===4 && e.value=="TRUE") {
    e.source.toast("D1");
    e.range.offset(0,-3).setValue(0);
    e.range.setValue("FALSE");
  }
}

Modified a bit to help you test it

function onEdit(e) {
  var sh=e.range.getSheet();
  var cell=e.range.getA1Notation();//debug
  e.source.toast('Entry');//debug
  if(sh.getName()!="Sheet1")return;
  if(e.range.columnStart==2 && e.range.rowStart<15 && e.value=="TRUE") {
    e.source.toast(cell);//debug
    var vA1=e.range.offset(0,-1).getValue();
    e.range.offset(0,-1).setValue((vA1-1<0)?0:vA1-1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart==3 && e.range.rowStart<15 && e.value=="TRUE") {
    e.source.toast(cell);//debug
    var vA1=e.range.offset(0,-2).getValue();
    e.range.offset(0,-2).setValue(vA1+1);
    e.range.setValue("FALSE");
  }
  if(e.range.columnStart===4 && e.range.rowStart<15 && e.value=="TRUE") {
    e.source.toast(cell);//debug
    e.range.offset(0,-3).setValue(0);
    e.range.setValue("FALSE");
  }
}

Please note: there should only be one onEdit(e) simple trigger function. If fact there should only be one of any function in a .gs file.

You may wish to remove my e.source.toast(); lines

Is it possible to use simple triggers to increment a cell whenever another cell is edited in google sheets?

2 Answers