Bi-directional sync of two separate sheets on a Master Sheet

Question

I am new to GAS and I am working on synchronising 3 Google Sheets, based on

Is there a way to keep two sheets synchronized?

I am currently struggling with the problem that I cannot synchronise both sheets at the same time on a Master Sheet, that I got only one of datasets from either sheets. (E.g.: Sheet 1 data of A1:C5 is copied twice on Master A1:C5 and D1:F5)

The codes for Sheet 1 are as shown below.

var sourceSpreadsheetID = "Sheet ID 1";
var sourceWorksheetName = "Sheet1";
var destinationSpreadsheetID = "Master ID";
var destinationWorksheetName = "Master";

function syncData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getRange("A1:C5");
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange("A1:C5");
  toRange.setValues(thisData.getValues());
}

For Sheet 2, nothing much above changes(only "Sheet 1 ID" to "Sheet 2 ID") but its location on Master will be as shown below.

function syncData1() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getRange("A1:C5");
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange("D1:F5");
  toRange.setValues(thisData.getValues());
}

You are referring to the same sheet twice, that's why you're getting the same results twice. — a-burge, Jun 30 '20 at 11:09

score 0 · Accepted Answer · answered Jun 30 '20 at 16:08

Your use of global variables is not a good solution. Let say you read the comment above and so you make a correction like this:

var sourceSpreadsheetID = "Sheet ID 1";
var sourceWorksheetName = "Sheet1";
var destinationSpreadsheetID = "Master ID";
var destinationWorksheetName = "Master";

function syncData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getRange("A1:C5");
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange("A1:C5");
  toRange.setValues(thisData.getValues());
} 

var sourceSpreadsheetID = "Sheet ID 2";
var sourceWorksheetName = "Sheet2";

function syncData1() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getRange("A1:C5");
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange("D1:F5");
  toRange.setValues(thisData.getValues());
}

You might think that you have solved the problem but in fact you have not your still reading from the same sheet it's just that now it's Sheet2 rather than Sheet1.

And no there really isn't a good way to keep both sheets in sync. Probably the best way is to just have one sheet. You could try with onEdit but if you really have a lot of editors then most likely it won't be able to keep up with them.

Thanks I initially thought putting 4 scripts into all 3 files will help to synchronize the sheets. I simply put two sets of codes in Master sheet, and other two with the destination and source in reverse in Sheet 1 and 2. What I do is when I edit something on Master Sheet, I press the button with script assigned for synchronization, as I do the same on Sheet 1 or 2. Great if you can provide me with further details on onEdit functions, since many may work on the same sheet at the same time. — Harris Morrison, Jul 02 '20 at 02:12

Bi-directional sync of two separate sheets on a Master Sheet

1 Answers1