google-apps-scriptgoogle-sheetsreferencecell

Trying to bidirectionally reference two cells

I'm basically wondering why the following Google Scripts' script won't work bidirectionally. If I edit NW!P2, then History!R2 correctly updates, while if I edit History!R2, NW!P2 doesn't care.

function onEdit(e) {
  if (!e.source) {
    console.error("Error: e.source is undefined");
    return;
  }

  var nwSheet = e.source.getSheetByName('NW'); // Set the name of the NW sheet
  var historySheet = e.source.getSheetByName('History'); // Set the name of the History sheet

  var range = e.range;
  var value = range.getValue();
  var sheetName = nwSheet.getName();

  // Update History!R2 when NW!P2 is edited
  if (range.getA1Notation() == 'P2' && sheetName == 'NW') {
    historySheet.getRange('R2').setValue(value);
  }

  // Update NW!P2 when History!R2 is edited
  if (range.getA1Notation() == 'R2' && sheetName == 'History') {
    nwSheet.getRange('P2').setValue(value);
  }
}
Solution

  • In your showing script, var sheetName = nwSheet.getName(); is always the sheet name of NW from var nwSheet = e.source.getSheetByName('NW');. I think that this might be the reason for your current issue, if you want to remove this issue, how about the following modification?

    From:

    var sheetName = nwSheet.getName();

    To:

    var sheetName = range.getSheet().getName();

    or

    var sheetName = e.source.getActiveSheet().getName();