使用Google Apps脚本遍历整个列 [英] Use Google Apps Script to loop through the whole column

问题描述

我试图在我的谷歌表格中遍历整行，并将一些数据从一张表复制到另一张。该列表会随着时间的推移而变长。更具体地说：如果列B中的输入等于蓝色，则将列A和C中的值复制到另一个表中。
在列结束前为所有列执行此操作。

链接到我的电子表格： https://docs.google.com/spreadsheets/d/1xnLygpuJnpDfnF6LdR41gN74gWy8mxhVnQJ7i3hv1NA/edit?usp=sharing

• 当颜色不等于蓝色时，循环停止。为什么？


• 正如你所看到的，我使用了一个for循环。这是甚至要走的路吗？


• 我可以做任何有关代码执行的速度吗？

$ b $ / div>

你有名为List的输入表，并将输出表命名为Output。
$ b

 函数condCopy（）
 {
 var s = SpreadsheetApp.getActiveSpreadsheet（） ; 
 var sht = s.getSheetByName（'List'）
 var drng = sht.getDataRange（）; 
 var rng = sht.getRange（2,1，drng.getLastRow（） -  1，drng.getLastColumn（））; 
 var rngA = rng.getValues（）; //输入值数组
 var rngB = []; //超过条件值的数组
 var b = 0; / /输出迭代器
 for（var i = 0; i< rngA.length; i ++）
 {
 if（rngA [i] [1] =='blue'）
 {
ngB [b] = []; //初始新数组
ngB [b] .push（rngA [i] [0]，rngA [i] [2]）; 
 b ++; 
} 
} 
 var shtout = s.getSheetByName（'Output'）; 
 var outrng = shtout.getRange（2,1，rngB.length，2）; //使输出范围与输出数组大小相同
 outrng.setValues（rngB）; 
} 
  

I am trying to loop through the whole row in my google sheet and copy some of the data from one sheet to another. The list will get longer over time.

More specifically: If input in column B equals "blue", than copy the values from column A and C into another sheet. Do this for all columns till the end of the column.

Link to my spreadsheet: https://docs.google.com/spreadsheets/d/1xnLygpuJnpDfnF6LdR41gN74gWy8mxhVnQJ7i3hv1NA/edit?usp=sharing

• The loop stops when the colour does not equal blue. Why?
• As you can see I used a for loop. Is that even the way to go?
• Can I do anything about the speed of the code execution?

Any comments, hints or help are highly appreciated.

Regards!

解决方案

You had the input sheet named "List" and I named the output sheet "Output". And here's the code.

function condCopy()
{
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sht = s.getSheetByName('List')
  var drng = sht.getDataRange();
  var rng = sht.getRange(2,1, drng.getLastRow()-1,drng.getLastColumn());
  var rngA = rng.getValues();//Array of input values
  var rngB = [];//Array where values that past the condition will go
  var b = 0;//Output iterator
  for(var i = 0; i < rngA.length; i++)
  {
    if(rngA[i][1] == 'blue')
    {
      rngB[b]=[];//Initial new array
      rngB[b].push(rngA[i][0],rngA[i][2]);
      b++;
    }
  }
  var shtout = s.getSheetByName('Output');
  var outrng = shtout.getRange(2,1,rngB.length,2);//Make the output range the same size as the output array
  outrng.setValues(rngB);
}

这篇关于使用Google Apps脚本遍历整个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！