从一张纸复制一行到另一张 [英] copy one row from one sheet to another

问题描述

我知道这个问题之前已经被问过了，因为我已经花了最近5个小时在这里尝试了他们，甚至是封闭的谷歌产品论坛。我无法得到任何循环停止。条件正在满足，但循环继续运行和运行。我一直在线上复制其他例子并插入我自己的变量，但最后，我会有5行到新表单几次，直到表单变得累了或什么。

我尝试了getDataRange ...我试过getRange ...我已经在循环和其他循环中尝试过var，我已经尝试过所有的，所以请看一看，让我知道这是怎么回事代码会导致行多次添加到新表中：

  function runReportAllMemCos1（）{
 var sheet1 = sskey.getSheetByName（'Businesses'）; 
 var sheet2 = sskey.getSheetByName（'tempsheet'）; 
 
 var data = sheet1.getRange（1,1，sheet1.getLastRow（），sheet1.getLastColumn（））。getValues（）; 
 
 var dest = []; （data [i] [12] ==Associate）{
 dest.push（data（$ data）; 
 for（var i = 0; i< data.length; i ++） [一世]）; 
} Logger.log（data）
 if（dest.length> 0）{
 sheet2.getRange（sheet2.getLastRow（）+ 1,1，dest.length，dest [0 ]。长度）.setValues（目标）; 
} 
} 
} 
  

感谢您的帮助！ / p>

解决方案

我并不完全同意user1795832的答案，因为它不使用批量写入，而只是将数据写入表2每次条件为真...那么为什么使用数组来做到这一点？

尝试在循环完成后批量写入表2，它应该工作如预期的那样。

function runReportAllMemCos1（）{
var sheet1 = sskey.getSheetByName '业务'）;
var sheet2 = sskey.getSheetByName（'tempsheet'）;

var data = sheet1.getRange（1,1，sheet1.getLastRow（），sheet1.getLastColumn（））。getValues（）;

var dest = [];
for（var i = 0; i< data.length; i ++）{
Logger.log（data [i] [12]）; //仅用于检查条件是否为真; - ）
if（data [i] [12] ==Associate）{
dest.push（data [i]）;
}
} //这里是for循环的结尾

Logger.log（dest）; //记录dest数组，而不是

if（dest.length> 0）{//如果有数据写入到您的数组中，则批量将其写入dest。 sheet
sheet2.getRange（sheet2.getLastRow（）+ 1,1，dest.length，dest [0] .length）.setValues（dest）;


$ / code $ / pre

$ hr

编辑：在您的评论之后选择您复制的列的其他可能性 $ b

  function runReportAllMemCos1（）{
 var sheet1 = sskey.getSheetByName（'Businesses'）; 
 var sheet2 = sskey.getSheetByName（'tempsheet'）; 
 
 var data = sheet1.getRange（1,1，sheet1.getLastRow（），sheet1.getLastColumn（））。getValues（）; 
 
 var dest = []; 
 for（var i = 0; i< data.length; i ++）{
 Logger.log（data [i] [12]）; //仅用于检查条件是否为真; - ）
 if（data [i] [12] ==Associate）{
 var destRow = []; //初始化中间数组
 destRow.push（data [i] [1]，data [i] [2]，data [i] [12]）; //在此选择要添加的列（此处col2,3& 13）
 dest.push（destRow）; 
} 
} //这里是for循环的结尾
 Logger.log（dest）; //记录dest数组而不是
 if（dest.length> 0）{//如果数组中已写入某些内容，则批量将其写入dest。 sheet 
 sheet2.getRange（sheet2.getLastRow（）+ 1,1，dest.length，dest [0] .length）.setValues（dest）; 
} 
} 
  

I know this has been asked before because I have spent the last 5 hours trying them all from here and even the closed google product forum. I can't get any of the loops to stop. The conditions are getting met, but the loop keep running and running. I have been copying from other examples online and inserting my own variables, but in the end, I'll have 5 rows go to the new sheet several times until the sheet gets tired or something.

I've tried getDataRange...I've tried getRange...I've tried var in loops and other loops, I've tried them all, so please take a look and let me know how this code keeps resulting in rows getting added to the new sheet multiple times over:

function runReportAllMemCos1() {
  var sheet1 = sskey.getSheetByName('Businesses');
  var sheet2 = sskey.getSheetByName('tempsheet');

  var data = sheet1.getRange(1,1, sheet1.getLastRow(), sheet1.getLastColumn()).getValues();

  var dest = [];
  for (var i = 0; i < data.length; i++ ) {
    if (data[i][12] == "Associate") { 
      dest.push(data[i]); 
    } Logger.log(data)
    if (dest.length > 0 ) {
    sheet2.getRange(sheet2.getLastRow()+1,1,dest.length,dest[0].length).setValues(dest);
    }
  }
}

Thanks for any help!

解决方案

I don't really agree with user1795832's answer as it doesn't make use of batch writing but simply writes data to sheet 2 each time the condition is true... so why using an array to do that ?

Try simply to batch write to sheet 2 after the loop is completed and it should work as expected.

function runReportAllMemCos1() {
  var sheet1 = sskey.getSheetByName('Businesses');
  var sheet2 = sskey.getSheetByName('tempsheet');

  var data = sheet1.getRange(1,1, sheet1.getLastRow(), sheet1.getLastColumn()).getValues();

  var dest = [];
  for (var i = 0; i < data.length; i++ ) {
    Logger.log(data[i][12]);// just to check if the condition is true sometimes ;-)
    if (data[i][12] == "Associate") { 
      dest.push(data[i]); 
    }
  } // here is the end of the for loop

  Logger.log(dest) ; // log the dest array instead

  if (dest.length > 0 ) { // if something has been written in your array then batch write it to the dest. sheet
    sheet2.getRange(sheet2.getLastRow()+1,1,dest.length,dest[0].length).setValues(dest);
  }
}

---

EDIT : other possibility following your comment to choose the columns you copy

function runReportAllMemCos1() {
  var sheet1 = sskey.getSheetByName('Businesses');
  var sheet2 = sskey.getSheetByName('tempsheet');

  var data = sheet1.getRange(1,1, sheet1.getLastRow(), sheet1.getLastColumn()).getValues();

  var dest = [];
  for (var i = 0; i < data.length; i++ ) {
    Logger.log(data[i][12]);// just to check if the condition is true sometimes ;-)
    if (data[i][12] == "Associate") { 
      var destRow = []; // initialise intermediate array
      destRow.push(data[i][1],data[i][2],data[i][12]);// choose here the columns you want to add (here col2, 3 & 13)
      dest.push(destRow); 
    }
  } // here is the end of the for loop
  Logger.log(dest) ; // log the dest array instead
  if (dest.length > 0 ) { // if something has been written in your array then batch write it to the dest. sheet
    sheet2.getRange(sheet2.getLastRow()+1,1,dest.length,dest[0].length).setValues(dest);
  }
}

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