How to sync two sheets with =importrange() in two

Question

I'm using =importrange() function to sync (echo sync) two sheets in two different spreadsheets (as described here). But, the importrange() it is not syncing to the second sheet when I make a change in the first sheet. Once imported, the cells stay static and do not alter as more changes are made in the first worksheet. Is there a way to fix it?

Answer 1

That was pretty helpfull script . Have edited some changes , in your script so that even multiple sheets can be synchronized for a give column and row .the code is a bit slow but works good.

I am now thinking if there was a way to merge multiple sheets using the same method , if it does it should be awesome .

   // sync multiple sheets to a source sheet ( “sheet 1”)
// change active sheet name to the designated sheet names. 
function importData(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = ss.getActiveSheet().getSheetName();
// set the sheet to copy from sheet 1 to sheet 2. sheet 1 active sheet.
  if( activeSheetName == "Daily report Counselling" )
  {
  var thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var thisWorksheet = thisSpreadsheet.getSheetByName("Sheet1");
  var thisData  = thisWorksheet.getRange("A5:H");
  var toSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var toWorksheet = toSpreadsheet.getSheetByName("Sheet2");
    var toRange = toWorksheet.getRange("A7:H");
  toRange.setValues(thisData.getValues());
      }
// if sheet 1 has not the active sheet choose from sheet 2.
 if( activeSheetName == "Follow Up Needed Editable" )
  {
  var thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var thisWorksheet = thisSpreadsheet.getSheetByName("Sheet2");
    var thisData  = thisWorksheet.getRange("A7:H");
  var toSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var toWorksheet = toSpreadsheet.getSheetByName("Sheet1");
    var toRange = toWorksheet.getRange("A5:H");
  toRange.setValues(thisData.getValues());

  }
}

Please let me know if something interesting comes in the way .

Answer 2

I don't think you'll be able to use the =importrange() function on two sheets because as soon as you add the function to the second sheet it will be importing the function you added to the first sheet with it's own ID as an argument.

You could use Google Apps Script, I have just answered a very similar question here. But I'll repeat what I wrote below.

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.