2

The following code is working fine. It is sorting, removing duplicates and deleting empty rows. Then I just want to copy the rows of data to after the last data row. But the code is copying the rows of data with empty rows. Need to avoid empty rows while copying

var column = 3;   
range.sort({column: column, ascending:true});   
range.removeDuplicates([column]);   

//now delete empty rows if any   
for (var i = range.getHeight(); i >= 1; i--){
    if(range.getCell(i, 1).isBlank()){
            sheet.deleteRow(range.getCell(i, 1).getRow());
    }   
}
//For Double periods in a class    
var dataToCopy = range.getValues(); //Gets the values of the source range in a 2 dimensional array    
var copyData = sheet.getRange(range.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy);
marko-36
  • 1,309
  • 3
  • 23
  • 38
Aktaruzzaman Liton
  • 55
  • 5

1 Answers1

2

I believe your goal as follows.

  • After the for loop in your script was finished, you want to retrieve the values without the empty rows.

For this, how about this answer?

Modification points:

  • In this answer, the number of delete rows is counted and the number is reduced from range of range.getValues();. By this, dataToCopy doesn't include the empty rows.

When above points are reflected to your script, it becomes as follows.

Modified script:

var column = 3;
range.sort({column: column, ascending:true});
range.removeDuplicates([column]);

//now delete empty rows if any

var deleteRows = 0;  // <--- Added

for (var i = range.getHeight(); i >= 1; i--){
    if(range.getCell(i, 1).isBlank()){
      sheet.deleteRow(range.getCell(i, 1).getRow());
      deleteRows++; // <--- Added
    }
}
//For Double periods in a class
var dataToCopy = range.offset(0, 0, range.getNumRows() - deleteRows).getValues();  // <--- Modified
var copyData = sheet.getRange(sheet.getLastRow()+1,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy); // <--- Modified
  • If you want to put the values to the last row of range, please modify the last line to the last line of your script.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    I added inside if statement deleteRows++ and it worked fine for me. Thanks – Aktaruzzaman Liton Jun 07 '20 at 03:41
  • @Aktaruzzaman Liton Thank you for replying. I have to apologize. From your replying, I could notice that I had copied and pasted mistake of the modified script. I modified the script. Thank you so much. – Tanaike Jun 07 '20 at 04:06