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();
}
{}}
I believe your goal is as follows.
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?
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"]();
}
}
onEdit
is used.const obj = {"sheet2": "sheet3", "sheet3": "sheet2"};
.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
.