Is there a way to keep two sheets synchronized?

Question

There isn't a way to share only one sheet of one spreadsheet in Google Sheets. 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?

Answer 1

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.