0

I have sheet A and B, data from sheet A which matching my criteria I am duplicating on sheet B by using formulas. Since some data not matching my criteria I am getting an empty row.

**PROBLEM STATEMENT **

I have a script which I am planning to use to delete empty rows on the sheet. But I have been notice that its deleting all formulas same time and all formatting.

I need to delete the rows only between the non empty ones. For example row 1,2 have data, row 5 have data, so, only row 3-4 need to be removed, this process should be automatic.

CODE

    function deleteEmptyRows(){ 
          var sh = SpreadsheetApp.getActiveSheet();
          var data = sh.getDataRange().getValues();
          var targetData = new Array();
          for(n=0;n<data.length;++n){
            if(data[n].join().replace(/,/g,'')!=''){ targetData.push(data[n])};
            Logger.log(data[n].join().replace(/,/g,''))
          }
          sh.getDataRange().clear();
          sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
        }
Dima Bur
  • 11
  • 2

1 Answers1

1

I see no sample of your data and have no idea how your sheet looks like, so here is just another guess:

function deleteEmptyRows() {
  const sh = SpreadsheetApp.getActiveSheet();
  const data = sh.getDataRange().getValues();
  const empty_rows = [];
  for (let i in data) if (data[i].join('') == '') empty_rows.push(+i+1);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));
}

Update

The variant of the function that finds for the last row contains visible data and removes all empty (with no visible data) rows above:


function delete_all_empty_rows_above_the_last_filled_row() {
  const sh = SpreadsheetApp.getActiveSheet();

  const data_all = sh.getDataRange().getValues();
  for (var last_row = data_all.length-1; last_row>0; last_row--) {
    if (data_all[last_row].join('') != '') break;
  }
  
  const data = sh.getRange(1,1,last_row+1,3).getValues();
  const empty_rows = [];
  for (let i in data) if (data[i].join('') == '') empty_rows.push(+i+1);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));
}

Update 2

To make the function to file every time as there were changes on the sheet "IF Copy" within the column "A" you need to add something like this:

function onEdit(e) {
  if (e.range.getSheet().getName() != "IF Copy") return;
  if (e.range.columnStart == 1) 
    delete_all_empty_rows_above_the_last_filled_row();
}

But onEdit() trigger works only when you edit sheet manually. If a sheet changes via some formula or another function the trigger doesn't work.

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • I tried your script, unfortunately its deleting all empty rows in the sheet. I need to delete the rows only between the non empty ones. For example row 1,2 have data, row 5 have data. So, only row 3-4 need to be removed. – Dima Bur Aug 29 '21 at 23:39
  • Sorry, but you're doing something wrong. My script does exactly this: if rows 3 an 4 are empty, it removes them. Believe me, I'm making scripts for quite long time already, and removing empty rows is a pretty common task. Probably you mean something uncommon by 'rows have data' and 'empty rows'. Just show your sheet: 1) what you have and 2) what you want to have. – Yuri Khristich Aug 30 '21 at 06:38
  • Thank you, I tried on another work sheet and it works. Kindly advice how to set the script to run automatically? Since when the new empty row appears it dose not react on changes.. – Dima Bur Aug 30 '21 at 15:32
  • Implementation heavily depends on the workflow. How exactly the empty row appears on your sheet? Does someone insert the new empty rows? Or does someone clear cells in existed non-empty rows? Or there is a some trigger that appends or changes rows? – Yuri Khristich Aug 30 '21 at 16:35
  • Hello. I tried to use on Edit Trigger but it deleting all formula in empty sells. Please advise how to fix (https://docs.google.com/spreadsheets/d/1ffMHVZfqb4XsxznEOH4ASn8pLYo5kxt02wI9MNY5mAs/edit?usp=sharing) – Dima Bur Sep 05 '21 at 00:46
  • In your case, as a simplest solution I'd change your formulas to make them put spaces `" "` instead empty strings `""` into cells: `=IF(OR('Data from FORM+FB'!D4="-");'Data from FORM+FB'!C4;" ")` This way the script will not remove rows with formulas, since it searches empty cells, but on the screen the cells with spaces will *look* empty. But it still eludes me what you're trying to do. To use trigger `onEdit()` for such heavy operations (delete rows) not exactly a best idea. It could work too slow and get you strange errors (with no alerts). – Yuri Khristich Sep 05 '21 at 08:27
  • Hello. I tried to do this, and now the script is not deleting any empty rows, since it counting it as non empty. I want to set it up the way to delete empty rows between the input data , but also keep the formulas active after it. The data on the first sheet its going to be added automatically. So i need to keep the formulas active on the second sheets, to be able select the result which I need. But I don't want to keep empty rows. I have a lot of data with same issue, and need to find the way to fix it. – Dima Bur Sep 05 '21 at 17:45
  • I hope I managed to understand the task: to find the last row with visible data, and remove all the rows above with no visible date inside. I've updated my answer. – Yuri Khristich Sep 05 '21 at 18:20
  • Yes, now it acting the way it was planned. But still i need to make OnEdit function for spreadsheet with name "IF Copy" , since i will have only some specific sheets where i will be using this script, and it is not necessarily to use it for entire spreadsheet. Lets say if column "A" in spreadsheet with name "IF Copy" will get a new value and it should be trigger to activate the script. Is it possible to add? – Dima Bur Sep 06 '21 at 02:45