Leave the blank cells in Column A and copy the rest (Google Sheet)

August 7, 2023

I am trying to copy the range from sheet1 to sheet2 using a script.
But I want to copy only the filled Cells in Column A and leave the blank cells. What should I add to the script?

This is the script I used to copy from sheet1 to sheet2

function Test() {
   const ss = SpreadsheetApp.getActive()
   const Source = ss.getRange('sheet1!A2:E33').getValues()
   const DestS = ss.getSheetByName('sheet2')
   const lastrow = DestS.getLastRow()
   const DestR = DestS.getRange(lastrow + 1,1,Source.length,Source[0].length)

DestR.setValues(Source);
}

>Solution :

you can do as following:

function Test() {
    const ss = SpreadsheetApp.getActive();
    const sourceValues = ss.getRange('sheet1!A2:A33').getValues(); // Get only values in Column A
    const filledCells = sourceValues.filter(row => row[0] !== ""); // Filter out blank cells

    if (filledCells.length > 0) {
    const destS = ss.getSheetByName('sheet2');
    const lastRow = destS.getLastRow();
    const destR = destS.getRange(lastRow + 1, 1, filledCells.length, filledCells[0].length);

    destR.setValues(filledCells);
    }
}