Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

Copying data after deleting empty rows in Google Sheets using Apps Script?

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);
Solution

  • 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: