修改代码-将范围复制到另一个电子表格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 of TOP UP NEEDED in the Spreadsheet of sheet 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 at openById(). 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.

    • 类别范围的复制到(目标)
    • 课程表的复制到(spreadsheet)
    • getValues()
    • appendRow(rowContents)
    • 简单触发器
    • 可安装触发器
    • 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屋！