在GOOGLE SCRIPT中复制和粘贴 [英] Copy and Paste in GOOGLE SCRIPT

问题描述

*我要检查列"D"和"E"表示在"Sheet1"上.* IF:=>列"D"的值等于0.和"E"表示是空的，不是空的=>我想将A:I中的行从Sheet1复制到"Sheet2"中最后一行的下一行；*如果不满足任何条件=>没事

*I want to check the columns "D" and "E" on "Sheet1". *IF: => the values of column "D" and "E" are empty and not empty => I want to copy the row from A:I from Sheet1 to the next row of last row in "Sheet2"; *If there's no condition met => DO NOTHING

您提供的代码很好，但是在不满足任何条件时出错.

The code you provided is good but got an error when no condition met.

我想使用Google Apps脚本实现这一目标.

I want to achieve this using Google Apps Script.

SAMPLE1_UPDATED SAMPLE2_UPDATED

推荐答案

我相信您的目标如下.

• 您要检查列"D"和"E"表示在"Sheet1"上.
• 当列"D"的值等于0时.和"E"表示为空且不为空，您想要复制列"A"到我"该行到"Sheet2"中最后一行的下一行.
• 您想使用Google Apps脚本实现这一目标.

关于如果不满足任何条件=>不用，我不确定您要实现以下哪个目标.

About If there's no condition met => DO NOTHING, I'm not sure which you want to achieve as follows.

1. 当列"D"的值等于0时.和"E"表示不为空或为空，即使其他行的条件已满足，您也不想复制.
2. 当列"D"的值等于0时.和"E"表示不为空或为空，您不想复制行.但是将复制具有填充条件的行.

因此，在这个答案中，我提出了以下两种模式.

So, in this answer, I proposed the following 2 patterns.

在此模式中，当列"D"的值等于和"E"表示不为空或为空，即使填充了其他行的条件，该脚本也不会运行.

In this pattern, when the values of column "D" and "E" are not empty or empty, the script is not run, even when the condition of other rows is filled.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    o[(r[3].toString() == "" && r[4].toString() != "") ? "trueCondition" : "falseCondition"].push(r);
    return o;
  }, {falseCondition: [], trueCondition: []});
  if (obj.falseCondition.length == 0) {
    sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
  }
}

模式2:

在此模式中，当列"D"的值等于和"E"表示不为空或为空，则不复制行.但是具有填充条件的行将被复制.

Pattern 2:

In this pattern, when the values of column "D" and "E" are not empty or empty, the rows are not copied. But the rows with the filled condition are copied.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    o[(r[3].toString() == "" && r[4].toString() != "") ? "trueCondition" : "falseCondition"].push(r);
    return o;
  }, {falseCondition: [], trueCondition: []});
  sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
}

注意:

• 在该示例脚本中，"Sheet1"的值是0.从第一行检索.如果要更改开始行，请根据实际情况修改"A1:I" .
• getValues()
• reduce()
• setValues(values)

关于另外的以下2张示例图片，

About your additional following 2 sample images,

下面的示例脚本怎么样?

how about the following sample script?

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    if ((r[3].toString() != "" && r[4].toString() != "") || (r[3].toString() == "" && r[4].toString() == "") || (r[3].toString() != "" && r[4].toString() == "")) {
      o.falseCondition.push(r);
    } else if ((r[3].toString() == "" && r[4].toString() != "")) {
      o.trueCondition.push(r);
    }
    return o;
  }, {falseCondition: [], trueCondition: []});
  if (obj.trueCondition.length > 0) {
    sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
  }
}

• 在这种情况下，您可以看到未由 console.log(obj.falseCondition)复制的行.

这篇关于在GOOGLE SCRIPT中复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！