0

In Google Sheets, I need to check column F if certain values are present. If the values I have specified are in column F, it will copy the entire row and paste it on a new sheet.

The code works when the specified values are in column F, but it breaks when the values do not exist. Not quite sure how to fix the error if the specified values do not exist in column F.

EDIT: I think it has to do with the last line //copy data array to destination sheet being set to always true. Can't figure out how to make it conditional.

Code below:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data'); //source sheet
  var testrange = sheet.getRange('F:F'); //range to check
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('To_Remove'); //destination sheet
  var data = [];
  var j =[];

  //Condition check in H:H; If true copy the same row to data array
for (i=0; i<testvalue.length;i++) {
  if (testvalue[i] == '254'  || 
      testvalue[i] == '560'  || 
      testvalue[i] == '164'  || 
      testvalue[i] == '260'  ||
      testvalue[i] == '146'  ||
      testvalue[i] == '91'   ||
      testvalue[i] == '847'  ||
      testvalue[i] == '99'   ||
      testvalue[i] == '167'  ||
      testvalue[i] == '844'  ||
      testvalue[i] == '182'  ||
      testvalue[i] == '1180' ||
      testvalue[i] == '1242' ||
      testvalue[i] == '806'  ||
      testvalue[i] == '489'  ||
      testvalue[i] == '490'  ||
      testvalue[i] == '807' 
     )
  { //NOTE HERE: || is the OR equivalent for the conditional statement. AND is &&
  data.push.apply(data,sheet.getRange(i+1,1,1,25).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }
//Copy data array to destination sheet
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

}
0

From the question:

EDIT: I think it has to do with the last line //copy data array to destination sheet being set to always true. Can't figure out how to make it conditional.

Put that code line inside an if statement. Example:

if(data.length > 0){
//Copy data array to destination sheet
  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

setValues(values) requires that it's argument be a bidimensional array

data.lenght > 0 will return false if data is empty. This should be enough but for certain scenarios you should use something more robust.

NOTE: The Google Apps Script best practices discourages the use of methods that write/read to an spreadsheet inside loops. For further details see https://developers.google.com/apps-script/guides/support/best-practices

| improve this answer | |
  • Thank you so much! Appreciate your help! – Brian Jan 24 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.