3

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/… – Rubén Mar 28 '16 at 20:26
  • 1
    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. – Rubén Mar 28 '16 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. – Coert Grobbelaar Mar 29 '16 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 Mar 29 '16 at 17:17
  • 1
    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 – LoganGoesPlaces Mar 30 '16 at 18:44
1

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.

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.