Google-sheets – Keep multiple sheets synchronised (both ways)

google sheetsgoogle-apps-scriptsynchronization

I need to have multiple Google Sheets be synced both ways.

For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only contains a subset of the master content (those rows with "Jane Doe" in the name column).

If I edit the master sheet, the filtered sheets should update. But also, if the filtered sheet is edited, the master sheet should be updated.

It seems that =importrange gets me one way (master changes, filtered updates) but it doesn't allow me to edit the content in the filtered list at all.

Is there anyway to do this?

Best Answer

This function can be used in each of the destination sheets to copy back to the main sheet. 

function onEdit(event){
  var range = event.range;
  if(range.getSheet().getName() == "Sheet1" && range.getColumn() > 1 && range.getRow() > 1 ){//example conditionals to limit the action to exclude headers or other static data could be changed to work specific cells or ranges
    var mainsheet = SpreadsheetApp.openByUrl("main spreadsheet URL").getSheetByName("destination sheet");
    var dataA1 = range.getA1Notation();
    range.copyTo(mainsheet.getRange(dataA1));

  }
}

this is a simple trigger and requires your users to have edit access to your main sheet. If you do not want to give them that access you could use an installable trigger instead.

I a similar script could be used to push the data out from the main sheet to the secondaries. I would recommend setting it as a menu so that you have better control and because triggers have a 30 second execution time.

For a single sheet per spreadsheet that would look like this:

function copyOut() {
  var source = SpreadsheetApp.getActiveSheet();
  var sheets = [firstsheeturl, secondsheeturl,thirdsheeturl];
   for(var i = 0; i < sheets.length; i++){
     var ss = SpreadsheetApp.openByUrl(sheets[i]);
     source.getRange("A1:Z26").copyTo(ss.getRange("A1:Z26"));
   }

}

if you want to send each secondary sheet different data sub sets not using a loop would be simpler. It is important to keep the same data in the same cells in both sheets or the onEdit as written won't know where to put things. Writing in symetrical offsets into the onEdit and the copyOut would be a way around that.

Related Topic