Google Sheets – Sync Multiple Cells to Change Together

google sheetsgoogle-apps-script

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?

Best Answer

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);
      }
    }
  }
}

Related Solutions

Google-sheets – Auto Sort Google Sheet with onEdit function

If you want to sort only the active sheet, it's simpler:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
  range.sort({column: 1, ascending: false});
}

You had sheet.getLastRow() - 1 for the number of rows in the range, which doesn't seem correct if you start the range from the 3rd row. I put -2.

Google-sheets – Auto create sheets within a spreadsheet with formatted columns and cells

Here is the script that does what you described. The formulas are prepared in a double array formulas ahead of time. Then, each new sheet gets one frozen row, columns headings, and formulas.

function setup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formulas = [];
  for (var i = 2; i <= 30; i++) {
    formulas.push(['=D' + i + '*100']);
  }
  for (i = 1; i <= 5; i++) { 
    var sheet = ss.insertSheet('New Sheet '+ i);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:F1").setValues([['Heading 1', 'Heading 2', 'Heading 3', 'Heading 4', '', 'Heading 5']]);
    sheet.getRange("E2:E30").setFormulas(formulas); 
  }
}

However it's easier to use arrayformula command that deals with all multiplications at once: =arrayformula(D2:D30*100). Then the script is shorter:

function setup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for (i = 1; i <= 5; i++) { 
    var sheet = ss.insertSheet('New Sheet '+ i);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:F1").setValues([['Heading 1', 'Heading 2', 'Heading 3', 'Heading 4', '', 'Heading 5']]);
    sheet.getRange("E2").setFormula("=arrayformula(D2:D30*100)");
  }
}

Best Answer

Related Solutions

Google-sheets – Auto Sort Google Sheet with onEdit function

Google-sheets – Auto create sheets within a spreadsheet with formatted columns and cells

Related Topic