Apps Script Checkbox True/False to copy between two sheets, is this logic possible?-CodePudding

I'm not sure how to better title this so I'll apologize in advance. I'm trying to determine if something I'm hoping to do is even possible, because so far I don't see how but if it is, I'd like to learn. I have a rather simple script below, but right now as written all it does is looks at sheet2 and if a Checkbox is checked in D10, it checks the Checkbox on sheet3 in D10. This is perfect and works as expected. I also have it set to that if I uncheck the checkbox in D10 on sheet2 it unchecks the checkbox in D10 on sheet3.

The logic I'm hoping to see if I can build into it is that if I now uncheck D10 on sheet3, that it unchecks D10 on sheet2. I've tried that a few different ways by adding the reverse of what I have here, but no matter what I do it just rechecks the box on sheet3 as soon as I uncheck it, and the box on sheet2 never changes.

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet3');
if (sheet2.getRange('D10').isChecked()== true){
  sheet3.getRange('D10').check();
}
else if (sheet2.getRange('D10').isChecked()== false){
  sheet3.getRange('D10').uncheck();
}
{}}

The above was my original code but then I tried something like below and it had the same result. Any thoughts or advice?

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet3');
if (sheet2.getRange('D10').isChecked()== true){
  sheet3.getRange('D10').check();
}
if (sheet3.getRange('D10').isChecked()== false){
  sheet2.getRange('D10').uncheck();
}
else if (sheet2.getRange('D10').isChecked()== false){
  sheet3.getRange('D10').uncheck();
}
{}}

CodePudding user response：

I believe your goal is as follows.

You want to check the checkbox of "D10" of "sheet3", when the checkbox of "D10" is checked, and vice versa.
You want to uncheck the checkbox of "D10" of "sheet3", when the checkbox of "D10" is unchecked, and vice versa.
You want to run the script when the checkbox of "D10" on "sheet2" and "sheet3" is checked and unchecked.

In your script, for example, at the initial stage, when "D10" of "sheet2" has already been checked, "D10" of "sheet3" is checked even when "D10" of "sheet3" is unchecked. I thought that this might be the reason of your issue. In this case, how about the following sample script?

Sample script:

function onEdit(e) {
  const {range, source} = e;
  const sheetName = range.getSheet().getSheetName();
  const obj = {"sheet2": "sheet3", "sheet3": "sheet2"};
  if (range.getA1Notation() == "D10" && obj[sheetName]) {
    source.getSheetByName(obj[sheetName]).getRange("D10")[range.isChecked() ? "check" : "uncheck"]();
  }
}

In this sample script, the event object of onEdit is used.
To switch the sheet name, JSON object is used.

Note:

If you want to use other sheet names, please modify const obj = {"sheet2": "sheet3", "sheet3": "sheet2"};.
In this sample script, getA1Notation() is used for checking the cell coordinate. When you want to achieve this using the event object, you can also modify range.getA1Notation() == "D10" to range.columnStart == 4 && range.rowStart == 10.

Reference:

Event Objects