Use Google Apps Script To Loop Through The Whole Column

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

Solution 1:

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

function condCopy()
{
  vars= SpreadsheetApp.getActiveSpreadsheet();
  varsht= s.getSheetByName('List')
  vardrng= sht.getDataRange();
  varrng= sht.getRange(2,1, drng.getLastRow()-1,drng.getLastColumn());
  varrngA= rng.getValues();//Array of input valuesvarrngB= [];//Array where values that past the condition will govarb=0;//Output iteratorfor(vari=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++;
    }
  }
  varshtout= s.getSheetByName('Output');
  varoutrng= shtout.getRange(2,1,rngB.length,2);//Make the output range the same size as the output array
  outrng.setValues(rngB);
}

Solution 2:

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(){
  varactiveSheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4");
  varlastRow= activeSheet.getLastRow();
  varlastCol= activeSheet.getLastColumn();
  vartargetValues= [];

  varsourceSheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  varlastSourceRow= sourceSheet.getLastRow();
  varlastSourceCol= sourceSheet.getLastColumn();

  varsourceRange= sourceSheet.getRange(1, 1, lastSourceRow, lastSourceCol);
  varsourceData= sourceRange.getValues();

  varactiveRow=0;

  //Loop through every retrieved row from the Sourcefor (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 variablevartempvalue= [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:

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