5

There isn't a way to share only one sheet of one spreadsheet in Google Docs. So, you have to share an entire spreadsheet. So, I was thinking in writing a script to synchronize two sheets (each one in a different spreadsheet). I thought using a function to get rows as array to do this. Is there a better strategy to do that?

Rubén
  • 34,714
  • 9
  • 70
  • 166
craftApprentice
  • 2,697
  • 17
  • 58
  • 86

2 Answers2

5

One way you could accomplish this is by adding a script to both spreadsheets that copies it's contents to the other spreadsheet on a change trigger. For example if you were to add something like the below to both spreadsheets, swapping the source and destination information around. 

var sourceSpreadsheetID = "ID HERE";
var sourceWorksheetName = "SHEET NAME HERE";
var destinationSpreadsheetID = "ID HERE";
var destinationWorksheetName = "SHEET NAME HERE";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

Just add a change trigger for the importData function and then when any changes are made to either document it will copy the contents to the other spreadsheet, thus keeping the both synced.

Obviously if both spreadsheets are being updated at the same time you will run into trouble.

dev
  • 3,969
  • 3
  • 24
  • 36
  • Agree with last comment in your answer but the OP needed a one direction synchronization so this case should not happen no? – Serge insas Dec 08 '13 at 22:33
  • Hi @Sergeinsas Yes if only one way sync is needed then could just add it to one sheet and forget the other. – dev Dec 08 '13 at 22:40
  • Hi, @vletech, it seems like magic! I'll test if it works. Thanks for your time (and brain!). – craftApprentice Dec 08 '13 at 23:33
  • Hi, @vletech, by "change trigger" you mean one of the 3 available trigers: on edit, on open, on submit form? (since there arent't a specific "on change" trigger? – craftApprentice Dec 08 '13 at 23:53
  • @craftApprentice hmm that's strange I have a fourth which is onChange. I've just tried onEdit and it appears to be acting the same. Is this working for you? – dev Dec 09 '13 at 00:26
  • Hi, @vletech, I tryied onEdit(), but it didn't work. About onChange(), I didn't find it in the documentation page I read (maybe I read an outdated one). I'll ask another question based on your code. I think you give the right direction, now I have to put the gears to work. Thanks again! – craftApprentice Dec 09 '13 at 00:29
  • Ok, now I found onChange(), execuse-me! =) – craftApprentice Dec 09 '13 at 00:38
  • There is a bug in the code above. It only works when you add values. It fails to see removal of values. So it only sync "add", not sync "sync". If I manage to find the fix myself, I'll post it here... – Sebastien Apr 23 '16 at 01:56
  • ok got it: simply add "toWorksheet.clear()" before the toRange statement. Guess it might get long if there is a LOT of data in the test. For my needs, the content isn't too big so it works – Sebastien Apr 23 '16 at 02:04
  • Watch out for the bug in onEdit() where is doesn't fire if you undo a delete: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1119. – Andrew Roberts Jun 28 '16 at 13:55
3

Two-way synchronization is a hard problem, but one that is solved quite effectively by Google Docs. Your concern about need-to-know, however, is a common one when sharing spreadsheets. Your best option in your example is probably to change which sheet is considered the 'master' of the shared data.

Everyone who needs to edit spreadsheet content should do that on the same 'master' sheet, taking advantage of the sharing and revision tracking provided by Google Docs. If you need or wish to combine all or part of that sheet with other sheets, for example to perform review alongside other data that is privileged and unavailable to all editors of the master sheet, then use the ImportRange() spreadsheet function in the secondary sheet to pull from the master sheet, effectively a 1-way sync.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Hi, @Mogsdad, one problem with importrange() is that comments are not syncronized. Do you have any suggestion to solve this? (comments syncronization) – craftApprentice Dec 09 '13 at 13:04
  • 2
    Other than copying the entire spreadsheet, there's nothing you can do to copy comments until [Issue 2566](http://code.google.com/p/google-apps-script-issues/issues/detail?id=2566) is fixed. Funny story - Google marked the issue as "Triaged" the same day they deprecated all Comments methods. See [setComment()](https://developers.google.com/apps-script/reference/spreadsheet/range?hl=en#setComment(String)). If you abandon comments and use notes instead, you'll be able to copy them by script. – Mogsdad Dec 09 '13 at 18:11