How to delete the empty cells in a specific column using apps script?

Question

The goal is to delete empty cells in Column N alone, without disturbing the other columns and shift the rest of the cells upwards to obtain a compact column with just non empty cells. There can and will be empty cells after the result of course. Please suggest a method

function Defaulters() {
    var spreadsheet = SpreadsheetApp.getActive();
    var as = spreadsheet.getActiveSheet();
 

    //to get the last row of the Column
      
    var lastRow = 100;
    var range = as.getRange("N" + lastRow);

    if (range.getValue() !== "") 
    
    {Logger.log(lastRow);}
    
    {lastRow = range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();}

    Logger.log(lastRow);
  
    //to delete the empty cells and give a compact output of just names and emails in the Column

    for (var l=lastRow; l>=3; l--)
    {if(as.getRange(l,14).getValue() == "")

     Logger.log(true); **//what to put here to delete this cell?**

     else

     Logger.log(false);**// what to put here to retain this cell?**
  }
}

Did you mean to delete all rows that have empty cell in 'N' column or remove empty cells from the column (and shift the rest cells upward, I suppose) and keep cells in other columns intact? — Yuri Khristich
– Yuri Khristich, Commented Jul 27, 2021 at 14:26
hey @YuriKhristich, added a bit more detailing to the question now, it's the latter. — Opaf
– Opaf, Commented Jul 27, 2021 at 17:29
Does my solution (third sample) work for you? It should do exactly the thing you describe: remove empty cells in column 'N' and shift the rest cells (in column 'N') upward. — Yuri Khristich
– Yuri Khristich, Commented Jul 27, 2021 at 20:12
@YuriKhristich Thanks you mate, your solution worked for me. Just a beginner, so another small doubt, say I need to accomplish the same for column N and O, what do I do? As of now, I'm just individually using the formula twice. — Opaf
– Opaf, Commented Jul 28, 2021 at 8:32
Actually I'd do about the same: use the function two times. Only thing I'd change is add argument to the function function clean_column(col). Adn call this function to clean any column: clean_column('A'), clean_column('B'), etc. See my updated answer. — Yuri Khristich
– Yuri Khristich, Commented Jul 28, 2021 at 9:10

Yuri Khristich · Accepted Answer · 2021-07-28 09:09:31Z

I'd try something like this:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues(); // get all data
  const data_new = data.filter(row => row[13] != ''); // filter the data by column 'N'
  sheet.clearContents(); // clean the sheet
  sheet.getRange(1,1,data_new.length,data_new[0].length)
    .setValues(data_new); // put the new data back on the sheet
}

Or even like this:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues().filter(row => row[13] != '');
  sheet.clearContents().getRange(1,1,data.length,data[0].length).setValues(data);
}

If you need to keep all the table intact and remove empty cells only from column 'N' it can be done this way:

function clean_column_N() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('N3:N'+sheet.getLastRow())       // get a range start from row 3
  const data = range.getValues().filter(String);                // get a data and remove empty elements
  range.clearContent().offset(0,0,data.length).setValues(data); // put the data back on the sheeet
}

All data in column 'N' will be moved upward.

Update

Modified last variant to clean any column:

function main() {
  clean_column('N');
  clean_column('O');
}

function clean_column(col) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(col + '3:' + col + sheet.getLastRow());
  const data = range.getValues().filter(String);
  range.clearContent().offset(0,0,data.length).setValues(data);
}

Collectives™ on Stack Overflow

How to delete the empty cells in a specific column using apps script?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related