Use Google Apps Script to loop through the whole column

Question

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!

score 8 · Accepted Answer · answered Mar 06 '17 at 19:25

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

Karl_S · Answer 2 · 2017-03-06T21:53:25.930

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) {

This is a good code. but can you please tell me what can we do if we want to remove the row after add to the second sheet. — xitas, Sep 16 '20 at 04:51
@xitas take a look at this thread: https://stackoverflow.com/questions/37924842/deleting-rows-in-google-sheets-using-google-apps-script You should save the rows to be deleted in an array and then delete them from the bottom up. Also, if you have consecutive rows, delete them as a group. Each call to delete a row slows the code so minimizing the calls is important. Note that when you delete a row it renumbers the rows. This is why you start at the bottom and work your way up. — Karl_S, Sep 22 '20 at 15:08

Use Google Apps Script to loop through the whole column

2 Answers2

Linked