如何在两个googlespreadsheet中同时使用两个工作表= importrange（）？ [英] How to sync two sheets with =importrange() in two googlespreadsheet?

问题描述

我使用 = importrange（）函数在两个不同的电子表格中同步（回显同步）两张纸张（如这里）。但是，当我在第一张纸上进行更改时， importrange（）不会同步到第二张纸上。一旦导入，单元格将保持静态，并且不会随着第一个工作表中的更改而更改。有没有办法解决它？

解决方案

我不认为你可以使用 = importrange（）函数，因为只要将函数添加到第二个工作表，它将导入您添加到第一个工作表的函数，并将其自己的ID作为参数。 / p>

您可以使用Google Apps Script，我刚刚在这里回答了一个非常类似的问题。但我会重复我在下面写的内容。

您可以通过在两个电子表格中添加一个脚本来将其内容复制到另一个电子表格上。例如，如果您要将以下内容添加到这两个电子表格中，请交换源和目标信息。

  var sourceSpreadsheetID =ID HERE; 
 var sourceWorksheetName =SHEET NAME HERE; 
 var destinationSpreadsheetID =ID HERE; 
 var destinationWorksheetName =SHEET NAME HERE; 
 
函数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（））; 
 
 $ / code> 

只需为importData函数添加更改触发器，然后进行任何更改要么将文件复制到其他电子表格，从而保持两个同步。



显然，如果两张电子表格都在同一时间更新，您将遇到麻烦。


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?

解决方案

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.

这篇关于如何在两个googlespreadsheet中同时使用两个工作表= importrange（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！