loop over a column and copy data to another sheet if cell = 0

Question

I am trying to loop over a specific column and look for values. When a cell value = 0, I want to copy other columns in that row to another sheet.

function selectCopy() {

var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Intake');
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Service Pending');
var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Service Complete');
var sheet4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('To Reissue');
var sheet5 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Discovery');

var lastRow1 = sheet1.getLastRow();
var lastRow2 = sheet2.getLastRow();
var lastRow3 = sheet3.getLastRow();
var lastRow4 = sheet4.getLastRow();
var lastRow5 = sheet5.getLastRow();

var startRow = 2;

var range = sheet2.getRange(2,1, lastRow1-startRow+1,1);

var numRows2 = range.getNumRows();
var case_number_values = range.getValues();

range = sheet2.getRange(2,2, lastRow1-startRow+1,1);
var name_values = range.getValues();

var range = sheet2.getRange(2,6, lastRow2-startRow+1,1);

var days_left_values = range.getValues();
var selection = sheet2.getDataRange();

range = sheet2.getRange(2,7, lastRow2-startRow+1,1);
var sent_to_server_values = range.getValues();

range = sheet2.getRange(2, 4, lastRow2-startRow+1,1);
var issue_date_values = range.getValues();

range = sheet2.getRange(2, 5, lastRow2-startRow+1,1);
var service_deadline_values = range.getValues();

range = sheet2.getRange(2,8, lastRow2-startRow+1,1);
var service_date_values = range.getValues();


for (var i=0; i<= numRows2-1; i++){
  var days_left = days_left_values[i][0];
  if (days_left === 0){
    var case_number = case_number_values[i][0];
    var name = plaintiff_name_values[i][0];
    var service_date = service_date_values[i][0];
    var issue_date = issue_date_values[i][0]
    var service_deadline = service_deadline_values[i][0];
    var sent_to_server = sent_to_server_values[i][0];

    sheet4.getRange(lastRow4+1,1).setValue(case_number);
    sheet4.getRange(lastRow4+1,2).setValue(name);
    sheet4.getRange(lastRow4+1,4).setValue(service_deadline);
}
}
}

It is sort of working but it is only copying the last row that has a cell = 0. So If I have three rows in column 6, whos value = 0, I want to copy all three rows, this code is only copying the last row.

You have specified target row as lastRow4+1 independently on i. So all copies fall into the same range. — Александр Ермолин

J. G. J. G. · Accepted Answer · 2019-06-25T17:55:55

does this run insanely slow? you could gain a lot of time if you rewrote this a little bit. you really only need to read the data once and then write once per row.

function selectCopy() {


var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Service Pending');
var sheet4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('To Reissue');

}
var data = sheet2.getDataRange().getValues();
for (var i=1; i< data.length; i++){
  var days_left = data[i][5];//column 6 is days left
  if (days_left === 0){
    var case_number = data[i][0];//column 1 is case number
    var name = data[i][1];//column 2 is plaintiff names
    var service_date = data[i][7];// column 8
    var issue_date = data[i][3]//column 4
    var service_deadline = data[i][4];//column 5 is deadlines
    var sent_to_server = data[i][6];//column 7

    sheet4.appendRow([case_number,name,"",service_deadline]);
}


}

loop over a column and copy data to another sheet if cell = 0

1 Answers