修改代码-将范围复制到另一个电子表格Google表格 [英] Modify Code - Copy Range to another Spreadsheet Google Sheets
问题描述
下面的代码将复制并移动到同一工作簿中的下一个电子表格,称为需要充值".
The code below copies range and moves to next Spreadsheet in same workbook called "Topup Required".
我希望将数据发送到另一个工作簿中的另一个电子表格.
I would like it to send this data to another spreadsheet inside another workbook instead.
function onEdit(event) {
// assumes source data in sheet named PrepSheet
// target sheet of move to named TOP UP NEEDED
// getColumn with check-boxes is currently set to column I
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "PrepSheet" && r.getColumn() == 9 && r.getValue() == true) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("TOP UP NEEDED");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
}
}
我尝试更换
var targetSheet = ss.getSheetByName("TOP UP NEEDED");
使用
var targetSheet = SpreadsheetApp.openById("sheet ID").getSheetByName("TOP UP NEEDED");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
但是没用
谁能告诉我我哪里出问题了
Could anyone tell me where i went wrong
谢谢
推荐答案
- 在编辑单元格后,您要将值和格式从
s.getRange(row, 1, 1, numColumns)
复制到sheet ID
的电子表格中TOP UP NEEDED
的最后一行. - You want to copy the values and format from
s.getRange(row, 1, 1, numColumns)
to the last row on the sheet ofTOP UP NEEDED
in the Spreadsheet ofsheet ID
, when the cell is edited.
如果我的理解是正确的,那么这个答案如何?请认为这只是几个可能的答案之一.
If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.
不幸的是,在当前阶段,Class范围的copyTo()
不能用于其他电子表格.
Unfortunately, in the current stage, copyTo()
of Class range cannot be used for other Spreadsheet.
在此模式下,使用copyTo()
.因此,值和单元格格式都将被复制.
In this pattern, copyTo()
is used. So both the values and the cell format are copied.
修改脚本后,请进行以下修改.
When your script is modified, please modify as follows.
var targetSheet = ss.getSheetByName("TOP UP NEEDED");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
到:
var targetSS = SpreadsheetApp.openById("sheet ID");
s = SpreadsheetApp.getActiveSheet();
var tempSheet = s.copyTo(targetSS);
var targetSheet = targetSS.getSheetByName("TOP UP NEEDED");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
tempSheet.getRange(row, 1, 1, numColumns).copyTo(target);
targetSS.deleteSheet(tempSheet);
模式2:
在此模式中,使用getValues()
和appendRow()
.因此,仅复制值.
Pattern 2:
In this pattern, getValues()
and appendRow()
are used. So only the values are copied.
修改脚本后,请进行以下修改.
When your script is modified, please modify as follows.
var targetSheet = ss.getSheetByName("TOP UP NEEDED");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
到:
var targetSheet = SpreadsheetApp.openById("sheet ID").getSheetByName("TOP UP NEEDED");
var sourceValues = s.getRange(row, 1, 1, numColumns).getValues()[0];
targetSheet.appendRow(sourceValues);
注意:重要
-
在此修改中,不能使用
onEdit()
的简单触发器.openById()
发生错误.因此,请使用OnEdit事件触发器的可安装触发器.在这种情况下,请按如下所示修改函数名称.Note: IMPORTANT
In this modification, the simple trigger of
onEdit()
cannot be used. An error occurs atopenById()
. So please use the installable trigger of OnEdit event trigger. In this case, please modify the function name as follows.-
来自
From
function onEdit(event) {
-
-
收件人
To
function installedOnEdit(event) {
-
此修改后,请安装OnEdit事件触发器到
installedOnEdit
作为可安装的触发器.这样,在编辑单元格时,将运行installedOnEdit
而不执行两次.请注意这一点. After this modification, please install the OnEdit event trigger to
installedOnEdit
as the installable trigger. By this, when the cell is edited,installedOnEdit
is run without the double executions. Please be careful this.- copyTo(destination) of Class Range
- copyTo(spreadsheet) of Class Sheet
- getValues()
- appendRow(rowContents)
- Simple Triggers
- Installable Triggers
如果我误解了你的问题,而这不是你想要的方向,我深表歉意.
If I misunderstood your question and this was not the direction you want, I apologize.
这篇关于修改代码-将范围复制到另一个电子表格Google表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!