如何使用谷歌应用程序脚本将一行从一个谷歌电子表格复制到另一个谷歌电子表格? [英] How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?

问题描述

我想使用 Google Apps 脚本将一个电子表格中的特定行复制到另一个电子表格中.

I wanted to copy a particular row from one spreadsheet to another spreadsheet using google apps script.

谁能帮我找到答案.

推荐答案

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

在此处查看文档:

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

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

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

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

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.

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

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

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

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

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

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.

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

大功告成！

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

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.

这篇关于如何使用谷歌应用程序脚本将一行从一个谷歌电子表格复制到另一个谷歌电子表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！