0

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();
}
{}}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Chaz
  • 19
  • 1
  • 6

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Much appreciated @Tanaike. I will need to read up more on the use of JSON and the getA1Notation() function, but this worked perfectly for my situation. I don't suppose there is an easy way to also add to this if the ranges are different in some cases? For example in most cases the cell ranges will be the same but in some cases it's D12 to D14 and then when D14 is unchecked it'd need to uncheck D12. I think with my initial script I thought I'd have more control over that but if that's not possible, I may just edit my document so that the cell source/targets always match. – Chaz Sep 21 '21 at 13:11