Apps脚本-将E列复制到另一个工作表的K列，但将常量值添加到其他列(B列和D列) [英] Apps Scripts - Copy Column E To Column K of Another Sheet but add constant values into other columns (Column B and D)

问题描述

我编写了以下脚本，以将工作表1的E列的常量值复制到工作表2的K列，到目前为止效果很好.但是，我想在分别复制到工作表2的每一行的旁边，在B列和D列中添加两个值，分别是Name和Color.我不确定如何为此编写for循环.目前，它仅将常量值复制到第一行复制.虚拟工作表链接: https://docs.google.com/电子表格/d/1FGiGGiEoaYjq0KicC10PXx3AiwdvpCap9k4KUvQdQ-g/edit?usp = sharing

I wrote the following script to copy over the constant values of column E of Sheet 1 to column K of the sheet 2 which is good so far. But, I want to add two values which are Name and Color to column B and D right next to each row that has been copied over to the sheet 2, respectively. I am unsure how to write for loop for this. At the moment it only copies the constant values to the first copied row. Dummy sheet link: https://docs.google.com/spreadsheets/d/1FGiGGiEoaYjq0KicC10PXx3AiwdvpCap9k4KUvQdQ-g/edit?usp=sharing

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=="sheet 1" && e.range.columnStart==1 && e.value=="TRUE") {
    const tsh=e.source.getSheetByName('Sheet 2');
    const nr=tsh.getLastRow()+1;
    sh.getRange(e.range.rowStart,2,1,1).copyTo(tsh.getRange(nr,2,1,1));
    sh.getRange(e.range.rowStart,4,1,1).copyTo(tsh.getRange(nr,4,1,1));
    sh.getRange('E3:E').copyTo(tsh.getRange(nr,11,1,1));

 
  }
}

推荐答案

解决方案:

我将一些常用的代码放入变量中.由于您要粘贴在不连续的范围内，因此将 setValues 分为三个不同的行.当然，有更好的方法可以做到这一点，例如，使用范围列表或在它们之间放置空列，但我决定保持简单.

Solution:

I put some often used code into variables. Since you want to paste in a non-contiguous range, I split setValues to three different lines. Of course there are better ways to do that, for example, use list of ranges or put empty columns in between but I decided to keep it simple.

function onEdit(e) {
  const rng = e.range;
  const row = rng.getRow();
  const col = rng.getColumn();
  const sh = rng.getSheet();
  if(sh.getName()=="Sheet 1" && col == 1 && e.value=="TRUE") {
    const tsh=e.source.getSheetByName("Sheet 2");
    const nr=tsh.getLastRow()+1;
    const size = sh.getRange("E2:E").getValues().filter(String);
    const len = size.length;
    const product = new Array(len).fill([sh.getRange(row,2).getValue()]);
    const color = new Array(len).fill([sh.getRange(row,4).getValue()]);
    tsh.getRange(nr,2,product.length,1).setValues(product);
    tsh.getRange(nr,4,color.length,1).setValues(color);
    tsh.getRange(nr,11,size.length,1).setValues(size);
  }
}

请谨慎使用工作表名称.在您共享的模拟数据中，您具有 Sheet 1 和 Sheet 2 ，但是在您的代码中，您具有 sheet 1 和 Sheet 2 .确保名称完全匹配，即没有额外的空格，并且没有不同的大小写字母.必须完全匹配.

Be careful with the sheet names. In the mock data you shared, you have Sheet 1 and Sheet 2 but in your code you have sheet 1 and Sheet 2. Make sure the names matches exactly, namely no additional spaces and no different upper or lower case letters. There has to be an exact match.

这篇关于Apps脚本-将E列复制到另一个工作表的K列，但将常量值添加到其他列(B列和D列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！