Copy content from one spreadsheet to another without IMPORTRANGE

Question

I'm trying to create bidirectional synchronisation of some content between two Google Sheets. My script is working when:

the source and destination ID of the sheets are the same AND have the ID of the active spreadsheet

SO, THIS WORKS:

function myFunction() {

 var sourceSpreadsheet = SpreadsheetApp.openById("ID-OF-ACTIVE-SPREADSHEET");
 var destinationSpreadsheet = SpreadsheetApp.openById("ID-OF-ACTIVE-SPREADSHEET");

 var sourceSheet = sourceSpreadsheet.getSheetByName("sheet1");
 var destinationSheet = destinationSpreadsheet.getSheetByName("sheet2");

 var sourceRange = sourceSheet.getRange("A1:A4");

 // destination, columnStart, columnEnd, rowStart, rowEnd
 sourceRange.copyValuesToRange(destinationSheet, 2, 2, 3, 7);

}

But when I replace the destination ID with the ID of another spreadsheet, it doesn't work.

Could it have something to do with permissions?

I have tried to publish both spreadsheets.

THIS DOESN'T:

...

 var sourceSpreadsheet = SpreadsheetApp.openById("ID-OF-ACTIVE-SPREADSHEET");
 var destinationSpreadsheet = SpreadsheetApp.openById("ID-OF-ANOTHER-SPREADSHEET");

...

Since I'm trying to create bidirectional sync I don't think I can use IMPORTRANGE.

Source SpreadSheet

Destination SpreadSheet

Can you share sample sheets with the script attached? Without being able to debug the specifics, it's hard to say exactly what is the problem. — MickATX
Source: docs.google.com/spreadsheets/d/… Destination: docs.google.com/spreadsheets/d/… — Jonas Barsten

Jonas Barsten Jonas Barsten · Accepted Answer · 2014-05-18T14:24:07

It seems like copyValuesToRange doesn't communicate outside the spreadsheet, here is a working solution:

function onChange() {

  var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sourceData = sourceSpreadsheet.getRange("A1:A8").getValues();
  var targetSpreadsheet = SpreadsheetApp.openById("ID-OF-ANOTHER-SPREADSHEET").getSheetByName("Sheet1");

  targetSpreadsheet.getRange("A1:A8").setValues(sourceData);
}

PS: this also enables bidirectional sync between spreadsheets, just add a copy of this code on both spreadsheet A and B and replace "ID-OF-ANOTHER-SPREADSHEET" with the ID of the other spreadsheet. And then define the from- and to range in place of the "A1:A8".

Copy content from one spreadsheet to another without IMPORTRANGE

1 Answers