CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Jonas Barsten
Jonas Barsten

Reputation: 37

Copy content from one spreadsheet to another without IMPORTRANGE

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

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

Upvotes: 0

Views: 1936

Answers (1)

Jonas Barsten
Jonas Barsten

Reputation: 37

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".

Upvotes: 1

Related Questions