Google-sheets – Synchronizing Cell Changes Across Sheets

google sheetsgoogle-apps-script

I'm trying to connect a pair of cells on different sheets within the same Google Spreadsheet, so that after a new value is entered into one cell, the other cell will update to match that value, going in both directions. I found this old question and answer that seems to offer a solution in this script:

function sync(e) {
  var sheetName = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4'];  // name of sheets
  var cell = ['A1', 'B2', 'B1', 'A1'];    // corresponding cells to sync
  var r = e.range;
  var ss = e.source;
  var value = (e.value === undefined ? '' : e.value);
  var i = sheetName.indexOf(r.getSheet().getSheetName()); 
  if (i > -1 && r.getA1Notation() == cell[i]) {
    for (var k = 0; k < sheetName.length; k++) {
      if (k != i) {
        ss.getSheetByName(sheetName[k]).getRange(cell[k]).setValue(value);
      }
    }
  }
}

However, I can't get it to work in my Spreadsheet. I've put the names of the sheets and cells that I want it to reference into the arrays, but when I try to run it, it gives me the following error: 

TypeError: Cannot read property "range" from undefined. (line 4)

That seems like a pretty clear indication that I'm supposed to define the range property somehow, but my level of scripting expertise is best described as "cargo cult", and the original asker seemed satisfied with the script as originally given, so I'm at a loss.

Best Answer

With some advice from a commenter and a little additional tweaking, I've gotten the script to work exactly how I wanted. I had a little extra difficulty with the fact that I was trying to synchronize two different pairs of cells, and at first I could only get one pair or the other to sync, not both, but I managed to solve that issue with this script, which is working perfectly (so far):

function onEdit(e) {
  var sheetName = ['Sheet 1', 'Sheet 2',];  // name of sheets
  var firstCell = ['A', 'B'];    // First pair of cells to sync
  var secondCell = ['C', 'D'];    // Second pair of cells to sync
  var r = e.range;
  var ss = e.source;
  var value = (e.value === undefined ? "" : e.value);
  var i = sheetName.indexOf(r.getSheet().getSheetName()); 
  if (i > -1 && r.getA1Notation() == firstCell[i]) {
    for (var k = 0; k < sheetName.length; k++) {
      if (k != i) {
        ss.getSheetByName(sheetName[k]).getRange(firstCell[k]).setValue(value);
      }
    }
  }
  if (i > -1 && r.getA1Notation() == secondCell[i]) {
    for (var k = 0; k < sheetName.length; k++) {
      if (k != i) {
        ss.getSheetByName(sheetName[k]).getRange(secondCell[k]).setValue(value);
      }
    }
  }
}
Related Topic