Delete blank row with macro

Ask for Help
1

I would like to inquire about the possibility of deleting blank rows in a spreadsheet with a macro (video below) with a trigger from Glide, and how?

Delete Empty Rows (Blank Rows)

Thanks in advance and happy year.

2 Likes
2

Just out of curiosity, why would you want to trigger this from Glide?

Wouldn’t it make more sense to just use a timed trigger to run a script periodically (say once a day) and do a sweep of all sheets to clean out any blank rows?

3

Hi Darren,
Actually what I need is only if at the time someone deletes a row in Glide only. I have a formula that doesn’t work if a blank row occurs in the middle. Besides that, I also publish a spreadsheet, of course, it won’t be beautiful if there are blank gaps between the rows.

4

Okay.

I think I’d still just run something as a timed trigger, but maybe more frequently - maybe once every 5 minutes. If you really want to trigger it from Glide, there are a couple of ways to approach it. But what I’d suggest is rather than using the Glide delete row action, instead write a value to a column (or clear the value in a column) that will act to signal to your script that the row should be deleted.

Here is an example script that I’ve used recently, that you might be able to adapt.

function remove_empty_timesheet_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Timesheet');
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();
  var timesheet_id_index = headers.indexOf('Timesheet ID');
  var user_id_index = headers.indexOf('basic/User ID');
  var data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
  var row = sheet.getLastRow();
  while (row > 2) {
    var rec = data.pop();
    var timesheet_id = rec[timesheet_id_index];
    var user_id = rec[user_id_index];
    if (timesheet_id == "" && user_id == "") {
      console.log("deleting row %s", row);
      sheet.deleteRow(row);
    }
    row--;
  }
}

The above example looks at the values in two specific columns. If both are empty, then it deletes the row.
One very important thing to be aware of when deleting rows using a script like the one above is that you must ALWAYS start from the bottom of the sheet and work up. This is because the script starts by reading the entire sheet into memory, and then works through it row by row. If you started from the top, then as soon as you delete the first row, all of the row indexes will change, and if it finds a second row to delete then the wrong row will be deleted.

6 Likes
5

Looks like it’s fit for purpose but I need time to digest/try this script first. Give me time to report the results.

6

Check out this code…

7

@Darren_Murphy, your script works very nice. One more question, how to run this script for multiple sheets. Looks like I need to learn java in depth. :pensive:
Thank You.

8

I also tried the Code Book method by adding a column in the spreadsheet that contains a unique word, unfortunately, I didn’t succeed. Did I miss something?

9

did you add on change trigger? and unique trigger word is exactly the same? and sheet name changed to your sheet name ?

10

I used the run button to try on copy sheets.

11

run will not do it… it has to be triggered and you need to write delete word to that column … that will trigger the script

12

Ok, I’ll try on the original sheet and use Glide to trigger it.

13

Do you want to run it for all sheets, or just a specific list of sheets?

If for all sheets, you can get a list of all sheets using SpreadsheetApp.getActiveSpreadsheet().getSheets(), and then pass each sheet to the function in a loop. If for a specific list of sheets, you can do the same thing except start by defining an array of sheets.

14

you can use glide or just write directly in gs sheet

15

I only need two sheets out of many, which is fast. As far as I know, if there is a lot of activity in GS it will slow down its sync with Glide.

16

this code will delete row in any sheet:

// (c) StructureArt DIGITAL
  
function deleteanyRow() {
 
  var valueactivecell =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getValue();

  if(valueactivecell == "###delete"){ // this is a special word to trigger this function, you can change it to your own

  var s  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() 
   var row = s.getActiveRange().getRow()
   
   if(row > 1){  // prevent from deleting column title
    
     s.deleteRow(row);
    
    }
  }
}

simply paste that code in the script editor and save it…
create on change trigger for function deleteanyRow … save

paste ###delete to any row in any sheet… see magic happens!

2 Likes
17

I need more time to test your script @Uzo. Meanwhile, trouble occurred during testing. The script duplicated into GS and broke the references from my formula array and affected synchronization in Glide. I need to rearrange the columns in Glide.
But anyway I’m very grateful for your intention to share, and this script should definitely work well.

18

my script duplicated???

19

I mean self-copying into GS, not deleting rows. I don’t know why it happened.

20

i have no idea what you mean by self-copy, there is only one action in the script… delete row