How to sync two sheets with =IMPORTRANGE() in two Google Spreadsheets?

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

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.