Search code examples
google-sheetsconcatenationtransposearray-formulasgoogle-sheets-query

Removing only blank cells (not rows) in Google Sheets

Could you please assist in a simple Google Sheets script to allow me to take a sheet that looks like this:

Before picture

to automatically convert it with 1 click to this (ie - not to delete ROWS, but to delete and move up on empty cells only):

After Picture

Solution

  • This sample as you requested:

    function adder()
 {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');
   var lastRow = sheet.getLastRow(); 
   for (var i = 1; i < lastRow+1; i++){
     if((sheet.getRange('A'+(lastRow-i+1)).getValue()) + ""=="")
     {
       sheet.getRange('A'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
     }

     //var valueB = sheet.getRange('B'+(lastRow-i+1)).getValue() + "";
     if((sheet.getRange('B'+(lastRow-i+1)).getValue())=="")
     {
       sheet.getRange('B'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
     }

     //var valueC = sheet.getRange('C'+(lastRow-i+1)).getValue() + "";
     if((sheet.getRange('C'+(lastRow-i+1)).getValue())=="")
     {
       sheet.getRange('C'+(lastRow-i+1)).deleteCells(SpreadsheetApp.Dimension.ROWS);
     }
   }
 }