将每个新值复制并粘贴到两个不同工作表上的下一个空行-Google表格 [英] Copying And Pasting Each New Value To The Next Empty Row On Two Different Sheets - Google Sheets

问题描述

我有一个Google表格，其中包含多个不同的表格.每次我向第一张工作表的单元格(例如，G2:I1000中的任何单元格)添加文本时，我都希望其他一些单元格的值出现在第二张工作表的下一个空行中.例如，如果我将文本添加到第一张纸上的I5，那么我想自动将单元格A5:B5的值粘贴到第二张纸上的下一个空行(也在A和B列中).

I have one Google Sheet with multiple different sheets in it. Every time I add text to a cell (let's say any cell from G2:I1000) of the first sheet I want the value of a few other cells to appear in the next empty row on the second sheet. For example, if I add text to I5 on the first sheet, I then want to automatically paste the values of the cells A5:B5 to the next empty row on the second sheet (also in column A and B).

推荐答案

据我了解，您需要执行以下操作:

As far as I understand, you want to do the following:

• 每次编辑某个工作表中G2:I1000范围内的值时，您都希望复制已编辑行中的前两个单元格(A，B).
• 您要将这两个值粘贴到另一张纸的第一行.

您可以使用 onEdit触发器触发器来完成此操作.每当绑定到电子表格的电子表格被编辑时，该函数就会运行.但是，为了避免始终复制值，在将值从一张纸复制到另一张纸之前，必须满足几个条件:

You can use an onEdit trigger trigger to accomplish this. This function runs every time the spreadsheet it is bound to is edited. But to avoid values getting copied all the time, several conditions have to be met before copying the values from one sheet to another:

• 已编辑的行索引大于1且小于1001.
• 已修改的列是G，H或I.
• 编辑后的工作表是您要从中复制值的工作表.

满足这些条件后，该函数应获取值A，B，并将其复制到另一张纸的第一行.使用 appendRow 即可轻松实现.

After meeting those conditions, the function should get values A, B and copy them to the first empty row in another sheet. This can easily be achieved using appendRow.

以下代码完成了您想做的所有这些事情:

The following code does all these things you want to do:

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet(); // Gets sheet that was edited
  var destSheet = e.source.getSheetByName("Destination"); // Change this according to your preferences
  var range = e.range; // Gets range that was edited
  var editedRow = range.getRow();
  var editedCol = range.getColumn();
  var editedSheetName = editedSheet.getName();
  var originName = "Origin"; // Change this according to your preferences
  if (editedRow > 1 && editedRow < 1001 && editedCol > 6 && editedCol < 10 && editedSheetName == originName) {
    var firstCol = 1;
    var numCols = 2;
    var numRows = 1;
    var copyValues = editedSheet.getRange(editedRow, firstCol, numRows, numCols).getValues()[0];
    destSheet.appendRow(copyValues);
  }
}

请注意，我已通过以下方式命名工作表.请根据您的喜好进行更改:

Take into account that I've named sheet the following way. Please change this according to your preferences:

• Origin是要从中复制值的工作表.
• Destination是要复制值的工作表.

• Origin is the sheet to copy values from.
• Destination is the sheet to copy values to.

• 如果仅在L列中的对应值等于"YES"的情况下才希望复制数据，则应更改以下代码行:

if (editedRow > 1 && editedRow < 1001 && editedCol > 6 && editedCol < 10 && editedSheetName == originName) {

对此:

if (editedCol == 12 && range.getValue() == "YES" && editedSheetName == originName && editedRow > 1 && editedRow < 1001) {

editedCol应等于L列的索引(12)，并且已编辑范围的值应为"YES".

Where editedCol should be equal to the index of column L (12), and the value of the edited range should be "YES".

• 如果您希望在两种情况下都复制数据(即，如果G，H列被编辑，并且L列被编辑为是")，则该行应更改为此: /li>

• In case you want data to get copied in both situations (that is, if columns G, H, I are edited AND also if column L is edited to "YES"), then the line should be changed to this:

if (((editedCol == 12 && range.getValue() == "YES") || (editedCol > 6 && editedCol < 10)) && (editedSheetName == originName && editedRow > 1 && editedRow < 1001)) {

将两个条件表达式合并为一个，如果其中至少一个为真，则复制数据.

Which merges both condition expressions into one, and copies data if at least one of them is true.

我希望这会有所帮助.

这篇关于将每个新值复制并粘贴到两个不同工作表上的下一个空行-Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！