无需导入即可将内容从一个电子表格复制到另一个电子表格 [英] Copy content from one spreadsheet to another without IMPORTRANGE

问题描述

我正在尝试在两个Google表格之间创建某些内容的双向同步.我的脚本在以下情况下有效:

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

• 工作表的源ID和目标ID相同，并且具有活动电子表格的ID

所以，这工作:

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);

}

但是当我将目标ID替换为另一个电子表格的ID时，它将无法正常工作.

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

它与权限有关吗?

我试图发布两个电子表格.

I have tried to publish both spreadsheets.

没有:

...

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

...

由于我试图创建双向同步，所以我认为我不能使用IMPORTRANGE.

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

源SpreadSheet

目标SpreadSheet

推荐答案

似乎copyValuesToRange不在电子表格外部进行通信，这是一个可行的解决方案:

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:这还可以在电子表格之间进行双向同步，只需在电子表格A和B上添加此代码的副本，然后用另一电子表格的ID替换"ID-OF-ANOTHER-SPREADSHEET".然后定义"A1:A8"的起始和终止范围.

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

这篇关于无需导入即可将内容从一个电子表格复制到另一个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！