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?

  • Related: webapps.stackexchange.com/questions/89137/… Commented Mar 28, 2016 at 20:26
  • 2
    One of distinctive features of Google Sheets are the real-time collaboration features. They makes a lot more easier that several users work directly on the master sheet than implementing a custom two way synchronization. One features that could help you is Filter Views. Commented Mar 28, 2016 at 21:49
  • Other than suggesting a change in approach, I suggest that you should consider scripting this with the Google Apps Platform. I have automated transfers between sheets this way. Commented Mar 29, 2016 at 11:07
  • @CoertGrobbelaar that's exactly what I want, something with App Scripts would work perfectly fine, I just don't know what code to use for this to work.
    – RelicCross
    Commented Mar 29, 2016 at 17:17
  • 2
    As Ruben stated, I think filter views would be perfect for this. Instead of doing all the work to hack something together, filter views allow you to see different subsets of the data but edit the same master sheet. No messy sync hacks required. support.google.com/docs/answer/3540681?hl=en Commented Mar 30, 2016 at 18:44

3 Answers 3


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


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


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.

  • 1
    Functions run through a simple trigger can no longer use SpreadsheetApp.openByUrl() or SpreadsheetApp.openById(). An installable trigger is required. Safe sheet sync takes a lot more code than shown here. Use filter views instead. Commented Sep 21, 2021 at 6:05

I would use a workflow integration tool like Unito Unito or Zapier to do that, both are pretty robust when it comes to syncing master sheet use cases.


A really crappy way to do this would be to make all cells on one sheet equal their sister cell in the other sheet, and then add a formula in sheet 2 to do the same thing. It might be recursive, but if someone inputs information it would overwrite the formula and copy it into the other sheet. Then, people wouldn't type over the information in the other sheet because they see that there's something there.

Something like


and then on the other sheet


See what happens

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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