将多行复制并粘贴到另一张纸上的下一个空行中 [英] Copying and Paste multiple rows into next empty Row on another sheet

问题描述

我有以下脚本:

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 5 or E
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "IN" && r.getColumn() == 7 && r.getValue() == "Y") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("ORDERS");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);

  }
}

这是我找到脚本的原始帖子:https://support.google.com/docs/forum/AAAABuH1jm0hR40qh02UWE/?hl=en&gpf=%23!topic%2Fdocs%2FhR40qh02UWE

This is the original post where i found the script: https://support.google.com/docs/forum/AAAABuH1jm0hR40qh02UWE/?hl=en&gpf=%23!topic%2Fdocs%2FhR40qh02UWE

我想对代码做一些小的调整，但不知道从哪里开始.

I want to make a few small adjustments to the code, but don't know where to start.

目前，当在 G 列中输入Y"时，它会复制整行并将行内容放在 ORDERS 的最后一行！

Presently, it copies the whole row when a "Y" is entered into column G and places the row contents on lastrow of ORDERS!.

我想要它做的是:

1) 只复制订单最后一行的 B、C 和 E 列！2) 删除 IN 上 E 和 F 中的值！在为该特定行运行代码后(不希望它删除我没有放置Y"的行)3) 有没有办法用一个按钮来代替，当单击该按钮时，它会一次复制所有带有Y"的行?

1) only copy columns B,C and E on lastrow of ORDERS! 2) delete values in E and F on IN! after code has run for that specific row (don't want it deleting rows that I haven't put a "Y" against) 3) Is there a way to have a button instead, that when the button is clicked it copies all the rows with "Y" at once?

如果你想玩，这是我的工作表的链接:https://docs.google.com/spreadsheets/d/1RvjVyL7j5bmgtMs9BL16cvsGhhOSuRV_TsAo/edit?usp=sharing编辑?usp=共享

Here's a link to my sheet if you want to have a play: https://docs.google.com/spreadsheets/d/1Peo5_5QmkxVyL7j5bmgtMs9BL16cvsGhhOSuRV_TsAo/edit?usp=sharing

最好的问候曼克

推荐答案

我相信你的目标如下.

• 当G"列为Y时，要将B"、C"和E"列的值复制到工作表的最后一行ORDERS.
  • 您想在脚本运行时复制列G"为 Y 的所有行.
  • When the column "G" is Y, you want to copy the values of the columns "B", "C" and "E" to the last row of the sheet ORDERS.
    • You want to copy all rows that the column "G" is Y when the script is run.

    对于这个，这个答案怎么样?

    For this, how about this answer?

    • 在您的脚本中，
      • 当通过单击工作表上的按钮运行脚本时，无法使用事件对象.
      • 您的脚本复制活动范围的一行.
      • 复制行的E"和F"列不会被删除.

      以上修改点需要修改.当以上几点反映到脚本中时，就变成了这样.

      It is required to modify above modification points. When above points are reflected to the script, it becomes as follows.

      请将以下脚本复制并粘贴到脚本编辑器中.并请准备一个绘制和/或图像的按钮，并将功能run分配给按钮.这样，当单击按钮时，脚本就会运行.并将列G"为Y的所有行的B"、C"和E"列从工作表IN"复制到工作表ORDERS"的最后一行.

      Please copy and paste the following script to the script editor. And please prepare a button which is drawing and/or image, and assign the function run to the button. By this, when the button is clicked, the script is run. And the columns "B", "C" and "E" of all rows that the column "G" is Y are copied from the sheet "IN" to the last row of the sheet "ORDERS".

      function run() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var srcSheet = ss.getSheetByName("IN");
        var dstSheet = ss.getSheetByName("ORDERS");
      
        // 1. Retrieve the values from "A2:G" of sheet "IN".
        var srcValues = srcSheet.getRange("A2:G" + srcSheet.getLastRow()).getValues();
      
        // 2. Create an object for putting values and deleting the contents of the columns "E" and "F".
        var obj = srcValues.reduce((o, [,b,c,,e,,g], i) => {
          if (g == "Y") {
            o.values.push([b, c, e]);
            o.ranges.push(`E${i + 2}:F${i + 2}`);
          }
          return o;
        }, {values: [], ranges: []});
      
        // 3. Copy the values to the sheet "ORDERS".
        dstSheet.getRange(dstSheet.getLastRow() + 1, 1, obj.values.length, obj.values[0].length).setValues(obj.values);
      
        // 4. Delete the contents of the columns "E" and "F" of sheet "IN".
        srcSheet.getRangeList(obj.ranges).clearContent();
      }
      

      注意:

      • 关于运行 Google Apps 脚本的按钮，我认为这个网站很有用.Ref 在您的情况下，请在工作表上创建一个按钮"IN"并将功能 run 分配给按钮.这样，当按钮被点击时，脚本就会工作.
      • 请在 V8 中使用此脚本.

      Note:

      • About the button for running the Google Apps Script, I think that this site is useful. Ref In your case, please create a button on the sheet "IN" and assign the function run to the button. By this, when the button is clicked, the script works.
      • Please use this script with V8.
      • setValues(values)立>
      • clearContent()
      • reduce()

      这篇关于将多行复制并粘贴到另一张纸上的下一个空行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！