Javascript 如何使用谷歌应用程序脚本将一行从一个谷歌电子表格复制到另一个谷歌电子表格？

How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?

提问by Ani

Hello I wanted to copy a particular row from one spreadsheet to another spreadsheet using google apps script.Can anyone please help me to get the answer for this.

您好，我想使用谷歌应用程序脚本将特定行从一个电子表格复制到另一个电子表格。任何人都可以帮我找到答案。

回答by Keith Twombley

NOTE:This solution works for copying rows from one sheet to another sheet in the SAME spreadsheet, and does NOT work for copying a row from a sheet to a DIFFERENT spreadsheet.

注意：此解决方案适用于将行从一张工作表复制到同一电子表格中的另一张工作表，不适用于将一行从工作表复制到不同的电子表格。

Check out the documentation here:

在此处查看文档：

http://code.google.com/googleapps/appsscript/service_spreadsheet.html

http://code.google.com/googleapps/appsscript/service_spreadsheet.html

Let's assume you're working in the spreadsheet where you're copying from.

假设您正在从中复制的电子表格中工作。

You'd have to get a handle to the current spreadsheet and the target spreadsheet. You'll need to get the ID for the target spreadsheet. Details are in the link up there.

您必须获得当前电子表格和目标电子表格的句柄。您需要获取目标电子表格的 ID。详细信息在上面的链接中。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var target = SpreadsheetApp.openById("abc1234567");

Next we need to pick the particular sheets within those spreadsheets. Let's say your row is on the sheet named "New Stuff", and you have a sheet in the target spreadsheet named "Archive".

接下来，我们需要选择这些电子表格中的特定工作表。假设您的行位于名为“New Stuff”的工作表上，并且您在目标电子表格中有一个名为“Archive”的工作表。

var source_sheet = ss.getSheetByName("New Stuff");
var target_sheet = target.getSheetByName("Archive");

Now, the concept that google apps spreadsheets use are ranges. A range is just a chunk of cells. So we need to determine the from-range and the to-range. Let's say your sheet has 7 columns and you want the 10th row.

现在，谷歌应用电子表格使用的概念是范围。范围只是一大块单元格。所以我们需要确定起始范围和结束范围。假设您的工作表有 7 列，而您想要第 10 行。

var source_range = source_sheet.getRange("A10:G10");
var target_range = target_sheet.getRange("A1:G1");

So we're going to take that row and put it on the first row of the target sheet. Now for the actual copy:

所以我们要把那一行放在目标表的第一行。现在为实际副本：

source_range.copyTo(target_range);

And you're done!

你完成了！

Now, this will always clobber the first row of the target sheet. There's plenty you can do to stop that. Instead of always using the first line, you could use the sheet object methods to find the last row, add one after, and then use it as your range.

现在，这将始终破坏目标表的第一行。你可以做很多事情来阻止它。您可以使用工作表对象方法查找最后一行，在其后添加一行，然后将其用作范围，而不是始终使用第一行。

var last_row = target_sheet.getLastRow();
target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));

That way each time you copy a row over, it just gets added to the bottom of the sheet.

这样每次复制一行时，它都会被添加到工作表的底部。

回答by ubique

The copyTomethod is poorly documented, because it does not allow you to copy contents to a sheet in another spreadsheet.

该copyTo方法的文档记录很差，因为它不允许您将内容复制到另一个电子表格中的工作表中。

You may want to use the getValuesans setValueslike below:

您可能想使用如下所示的getValues答案setValues：

function CopyDataToNewFile() {
  var sss = SpreadsheetApp.openById('0AjN7uZG....'); // sss = source spreadsheet
  var ss = sss.getSheetByName('Monthly'); // ss = source sheet
  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('8AjN7u....'); // tss = target spreadsheet
  var ts = tss.getSheetByName('RAWData'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

}  

回答by Stefan van Aalst

I ran into this as well. I managed to figure out a nice workaround that copies everything from one sheet to another (except for images, graphs, scripts and stuff). It copies formulas, values, formating for sure.

我也遇到了这个。我设法找到了一个很好的解决方法，将所有内容从一张纸复制到另一张纸（图像、图表、脚本和其他内容除外）。它肯定会复制公式、值和格式。

Basically the solution is to

基本上解决方法是

• copy the source sheet to the target spreadsheet as a temp source sheet
• copy the data from the temp source sheet to the target sheet
• remove the temp source sheet

• 将源工作表作为临时源工作表复制到目标电子表格
• 将数据从临时源工作表复制到目标工作表
• 删除临时源表

Code:

代码：

var sss = SpreadsheetApp.openById(spreadsheet); // sss = source spreadsheet
var ss = sss.getSheetByName(sheet); // ss = source sheet
var ss_temp = ss.copyTo(targetsheet);
var tss = SpreadsheetApp.openById(spreadsheet); // tss = target spreadsheet
var ts = tss.getSheetByName(sheet); // ts = target sheet
var range2Copy = ss_temp.getRange(range);
range2Copy.copyTo(ts.getRange(range));
bss.setActiveSheet(ss_temp);
bss.deleteActiveSheet()

Based on the above I created a spreadsheet that allows me to update many copies from a master spreadsheet. With about 15 copies, it saves a lot of copy-paste actions.

基于以上内容，我创建了一个电子表格，允许我从主电子表格更新许多副本。大约有 15 个副本，它节省了很多复制粘贴操作。

回答by ellockie

Another way of doing it would be to import the row (or any range) from the source into the target spreadsheet.

另一种方法是将行（或任何范围）从源导入目标电子表格。

Assuming that you want to import row 3 from source spreadsheet's sheet called "source-spreadsheet-sheet-name", put in the first column of the target location the following formula:

假设您要从名为“source-spreadsheet-sheet-name”的源电子表格的工作表中导入第 3 行，请在目标位置的第一列中输入以下公式：

=IMPORTRANGE("source-spreadsheet-key","source-spreadsheet-sheet-name!3:3")

where "source-spreadsheet-key"is the unque ID of the source spreadsheet that you extract from its URL.

"source-spreadsheet-key"您从其 URL 中提取的源电子表格的唯一 ID在哪里。

If you do it first time, the security message will appear:

如果您是第一次这样做，则会出现安全消息：

Just confirm and the whole row will be filled in automatically.

只需确认，整行将自动填写。

Please remember that sometimes it takes couple of minutes to update the imported cells after changes have been made in the source area. However you can speed it up by pressing ctrl-E, or ctrl-shift-E, while beeing in the target spreadsheet, whichever works.

请记住，在源区域中进行更改后，有时需要几分钟来更新导入的单元格。但是，您可以通过按ctrl-E或ctrl-shift-E来加快速度，同时进入目标电子表格，以哪个有效。

回答by Pulpo

Use range.setValues, is the best choice. Works for any range in any sheet, as far as the origin and destination range are similar.

使用range.setValues，是最好的选择。适用于任何工作表中的任何范围，只要起点和目标范围相似。

Here's a sample code:

这是一个示例代码：

function openCSV() {

  //Buscamos el archivo
  var files = DriveApp.getFilesByName("ts.csv");

  //Cada nombre de archivo es unico, por eso next() lo devuelve.
  var file = files.next();

  fileID = file.getId();

  //Abrimos la spreadsheet, seleccionamos la hoja
  var spreadsheet = SpreadsheetApp.openById(fileID);
  var sheet = spreadsheet.getSheets()[0];

  //Seleccionamos el rango  
  var range = sheet.getRange("A1:F1");
  values = range.getValues();

  //Guardamos un log del rango
  //Logger.log(values);

  //Seleccionamos la hoja de destino, que es la activeSheet 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var SSsheet = ss.getSheets()[0];

  //Seleccionamos el mismo rango y le asignamos los valores
  var ssRange = SSsheet.getRange("A1:F1");
  ssRange.setValues(values);

}

回答by Oleg Shaydenko

Maybe this post helps you http://l.rw.rw/gscripts3. It describes functionality of copy function with different conditions (limit data by range, custom filters, place data to target with insert or replace methods). And has working example where you can try it.

也许这篇文章可以帮助你http://l.rw.rw/gscripts3。它描述了具有不同条件的复制功能的功能（按范围限制数据、自定义过滤器、使用插入或替换方法将数据放置到目标）。并有工作示例，您可以在其中尝试。

回答by Sean

This does not work - returns an error "Target range and source range must be on the same spreadsheet." Looks like we need to do something similar to this:

这不起作用 - 返回错误“目标范围和源范围必须在同一个电子表格上。” 看起来我们需要做一些类似的事情：

function copyRow_ (origSheetName, destSheetName, origRowNumber, destRowNumber)
{
  var origSheetObject = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(origSheetName);
  var destSheetObject = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(destSheetName);
  var origLastCol = origSheetObject.getLastColumn();
  var arrOrigData = origSheetObject.getRange(origRowNumber, 1, 1, origLastCol).getValues();
  var destlastRow = destSheetObject.getLastRow() + 1;
  if (destRowNumber)
    destlastRow = destRowNumber;
  var cont;
  for (cont = 0; cont < origLastCol; cont++)
       destSheetObject.getRange(destlastRow, 1+cont).setValue(arrOrigData[0][cont]);
}

(blatantly stolen from here)

（公然从这里偷来的）