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.
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.