0

I have 4 hard coded cells on 4 different sheets (within a google sheet file) that I'd like to keep synced. If any of those four cells have been changed, I'd like the other three cells to be updated to that changed value. The trick is that all four can be changed manually. Is there an app script that can do that?

1

Here is how this can be done. The function sync should be set to trigger on edit (via Resources > Current project's triggers). The arrays in the first two lines should be filled with sheet names and cell names that you want to sync.

If the edited cell matches one of those, then the rest will be updated to have the same value.

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);
      }
    }
  }
}
  • This does exactly what I want! Thank you so much! – dzarc May 20 '16 at 18:33

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.