最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

javascript - Use Google Apps Script to loop through the whole column - Stack Overflow

programmeradmin9浏览0评论

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:

  • 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 ments, hints or help are highly appreciated.

Regards!

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./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 ments, hints or help are highly appreciated.

Regards!

Share Improve this question asked Mar 6, 2017 at 18:09 user2375263user2375263 1952 gold badges3 silver badges14 bronze badges
Add a ment  | 

2 Answers 2

Reset to default 10

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);
}

You have 2 options. The first is to use the standard query() function from Google Sheets to get the values. The downside here is that it is only a reference of the values. So you cannot reorder them, etc. To use this, place this in cell A1 and it will pull the Headers and retrieve the values from column A and C:

=QUERY(A:C, "select A, C where B = 'blue'", 1)

For a Google Apps Script answer: This will loop through your List sheet and for every row where column B is blue it will save the values in column A and C to column A and B of the new sheet:

function doIt(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4");
  var lastRow = activeSheet.getLastRow();
  var lastCol = activeSheet.getLastColumn();
  var targetValues = [];

  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  var lastSourceRow = sourceSheet.getLastRow();
  var lastSourceCol = sourceSheet.getLastColumn();

  var sourceRange = sourceSheet.getRange(1, 1, lastSourceRow, lastSourceCol);
  var sourceData = sourceRange.getValues();

  var activeRow = 0;

  //Loop through every retrieved row from the Source
  for (row in sourceData) {
    //IF Column B in this row has 'blue', then work on it.
    if (sourceData[row][1] === 'blue') {
      //Save it ta a temporary variable
      var tempvalue = [sourceData[row][0], sourceData[row][2]];
      //then push that into the variables which holds all the new values to be returned
      targetValues.push(tempvalue);
    }
  }

  //Save the new range to the appropriate sheet starting at the last empty row
  activeSheet.getRange(lastRow + 1, 1 , targetValues.length, 2).setValues(targetValues);
}

Of course, you could pass the value to test to the function by replacing 2 lines. The first, defining the function:

function doIt(testingvar){

to pass a variable called testingvar, and the test line to replace the hard coded test with the passed variable:

if (sourceData[row][1] === testingvar) {
发布评论

评论列表(0)

  1. 暂无评论