How to check particular value in google spreadsheet

Question

I picked this code online and I am trying to check if the value in the column B is 'Done' then the value will be be copied otherwise not. Here is the code I am using:

copy sheet function below will copy the datat from source sheet to destination sheet but what I want that it will only pick the row if the col B value contains Done

function copySheet() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination");

  var columns_to_be_copied =['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U'];
  var columns_to_be_pasted =['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U'];

  for (column in columns_to_be_copied) {
    var copy_range_string = columns_to_be_copied[column] + ':' + columns_to_be_copied[column];
    var paste_range_string = columns_to_be_pasted[column] + ':' + columns_to_be_pasted[column];

    var source = sourceSheet.getRange(copy_range_string);
    var destination  = destSheet.getRange(paste_range_string);
    if(findInColumn('A','Done') !== -1) {
      copyTo(source,destination );
    }
  }
}
function copyTo(source,destination) {
  var sourceSheet = source.getSheet();
  var destSheet = destination.getSheet();
  var sourceData = source.getValues();
  var dest = destSheet.getRange(
      destination.getRow(),        // Top row of destination
      destination.getColumn(),     // left col of destination
      sourceData.length,           // # rows in source
      sourceData[0].length);       // # cols in source (elements in first row)
  dest.setValues(sourceData);
  SpreadsheetApp.flush();
}

function findInColumn(column, data) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sprint");
  var column = sheet.getRange(column + ":" + column);  // like A:A

  var values = column.getValues(); 
  var row = 0;

  while (values[row] && values[row][0] !== data) {
    row++;
  }
  if (values[row][0] === data)
    return row+1;
  else 
    return -1;
}

Answer 1

It's about the same function. I just modified it to facilitate my debugging process. It copies the columns from source to destination if Sprint has "Done" in that column.

function copySheet() {
  var srcsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  var dessh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination");
  var from = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U'];
  var to = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U'];
  for(var i=0;i<from.length;i++) {
    var fromrg = from[i] + ':' + from[i];
    var torg = to[i] + ':' + to[i];
    var src = srcsh.getRange(fromrg);
    var des  = dessh.getRange(torg);
    if(findInColumn(from[i],'Done')!== -1){
      src.copyTo(des);
    }
  }
}

function findInColumn(col, data) {
  var col=col || 'A';//This is here for initial testing so I could run the function without parameters.
  var data=data || 'Done';
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sprint");
  var rg = sh.getRange(col + "1:" + col + sh.getMaxRows());//MaxRows is kind of big but I was not sure what else you'd like to do and I dont know what your  Sprint sheet looks like.  
  var vA = rg.getValues(); 
  var rv=-1;
  for(var i=0;i<vA.length;i++){
    if(vA[i][0]==data){
      rv=i+1;
      break;
    }
  }
  return rv;
}

I see that you changed the question a bit. This function looks in your sprint sheet as you show in your answer and copies from source to destination only those columns that have the word "Done" on any row of that column. But it checks every column in your "columns_to_be_copied" which I called "from". Originally, that's what your function was trying to do. So I just wanted to be clear what this function is doing. If it's not what you want then leave a comment and I'll delete it.

Answer 2

As I am a fan of simple and easy to read (even after long time) solutions I would suggest the following script:

function main() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source');
  var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Destination');

  copyFromTo(sourceSheet, destinationSheet);
}

function copyFromTo(sourceSheet, destinationSheet) {
  const ColumnB = 1; //Array indexing starts from 0
  const FilterValue = 'Done';

  var sourceValues = sourceSheet.getSheetValues(1, 1, 100, 28); //startRow, startColumn, numRows, numColumns

  var filteredValues = sourceValues.filter(function(row) {
    return row[ColumnB] === FilterValue;
  });

  destinationSheet.getRange(1, 1, filteredValues.length, filteredValues[0].length).setValues(filteredValues);
}