1

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.

  • 2
    Welcome. You need to read up on Triggers and Event Objects. What is assumed (and not mentioned in that topic) about that script is that it is executed as an installable onEdit trigger. The documentation explains how to do this manually. So you never "run" the script per se; the script executes when a cell is edited. Depending on your requirements, you may need to check that the edit is on the appropriate sheet/ row/column, etc. But that can be left for another day PS: props for trying out the script. – Tedinoz Sep 16 at 3:36
  • That helped, thank you! – James Bisbee Sep 16 at 4:11
  • How is the code running now? – Tedinoz Sep 16 at 6:32
  • It's running well! – James Bisbee Sep 18 at 22:23
  • 1
    @JamesBisbee That's a good idea. Post an answer to your own question. It makes it easier for others to find and understand your solution. – Tedinoz Sep 18 at 23:21
1

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);
      }
    }
  }
}
  • This is a seriously good answer. Some very advanced code that one would expect of a skilled programmer - unlike me ;-) – Tedinoz Sep 23 at 0:29
  • It's not that advanced, all I did was define the other pair of cells as its own variable and copy-paste the bottom four lines to deal with it – James Bisbee Sep 24 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.